Calculating amount due using changing rates

[Craig]

This one is making my head hurt. Any help will be much appreciated.

I have an online database that field reps use to enter new clients and
record client payments. I have a batch file that downloads the data in an
..mdb file, and queries in another .mdb file that is linked to the tables in
the downloaded file. The queries generate the invoices that we mail to the
clients.

The tables are Clients (ClientID, StartDate, EndDate), Payments (ClientID,
PmntDate, PmntAmount), and Rate (ClientID, RateDate, RateAmount). If there
is no record in the Rate table for the client, they are charged our standard
$12/day fee for every service day. If there is a RateAmount in the Rate
table with a date after the StartDate, they are charged the standard $12/day
until the 1st RateDate. They can have multiple records in the Rate table, so
they may pay $12/day from 7/1/2008 until 7/31/2008, $10/day from 8/1/2008
until 8/31/2008, and $8/day from 9/1/2008 until their EndDate of 9/30/2008.

To calculate their amount due, I have to be able to aggregate their payments
(which is easy enough) and their total accumulated daily fees at the time the
invoice is generated. How do I create a query that adds up fees at one rate
from the StartDate until the first RateDate, at the first RateAmount until
the second RateDate, etc., and then tallies them up?

Thanks in advance!

 

[Lord Kelvan]

because there is not really a finiate amount of rates per client you
would need to do it in a loop since as far as i knwo you canot do
loops in a query you need to make a form to doo the loopthen save it
to a table which you can then report on and make your invoices make a
simple query to select the rates the client and the dates then make a
loop to go through the records to get the amount to multiply by the
rates just use cint(date1-date2) and it will give you a number to
multiply by 12 or 10 or whatever.

i cannot think of another way of doing it without a loop but i may be
wrong.