How to I use the payment date to determine the amount due?

[newbie1955]

If members of the club pay their dues late, we assess a penalty that varies depending on when the payment is received. I have set up two tables, "Payment Due Dates and Amounts" and "Dues"

The Payment Due Dates table has three fields, "Start Date", "End Date", and "Amount", that specifies the total amount due with the start and stop dates for each amount. This table has multiple rows, each with a new amount.

The Dues table contains the membership number, the date they paid, and the amount they paid. If the member made partial payments, then each partial payment is listed.

What I want to do is compare the date that the member paid (stored in the dues table) with the range of dates (stored in the Payment table) and then return with the appropriate amount. [If the member made multiple payments then will need to use the first date listed..]

I realize that this is probably a simple problem, but I'm a novice to Access, and am basically trying to teach myself as I go along. Many Thanks for your help.

[PhilOfWalton]

This is not too difficult, but I wonder if you are doing this the easiest way.

In a Club I have been involved in there is a fee for each class of membership, and a percentage discount applied to that fee, depending in the month paid.

Each member has a ClassID associated with him/her that points to the membership fee for that class.

So your PaymentDueDate then becomes very simple with a StartDate, and 12 discounts, one for each month, that apply to the appropriate fees.

The fee due is found by finding the gross fee, seeing which month the first payment was made and finding the corresponding discount to apply to that gross fee.

Let us know if this looks a better option.

Phil