I am in the process of creating a MS Access 2000 database for customer billing. The customer info table is easy, but I am trying to layout the design for the fees and invoice tables.

I wanted to to allow the end user to edit and add fees for each bill. These fees will include a minimum charge, overage charge, various flat rate charges, and percentage (late fee).

The minimum and overage charges are based on usage. For instance, if a customer used 50 units, the min. charge is $25.00 for 40 units. Then an overage will be applied for the remaining 10 units at $3.00 for every 5 units for a total $37.00...hmmm

Flat rate would be like a general fee, $1.00 applied to every count, but it is not usuage based.

Then a percentage based fee (surcharge I guess) would be like a late fee... 10% or something like that.

My plan would be to then build an SQL statement for all fees for a given invoice date, then compute the total using the resulting records.

My problem is that I cannot easily get an sql statement that will work with a master fee table that contains usage based charges with flat rate and percenatge based fees.

Is there some information available that could be used to guide an individual on designing a Access database that incorporates "minimal usage and overage" type fees and flat rate fees.