Want date paid to return value based on range of dates in another table.

[Accessnewbie55]

I have two tables in Access 2016. The first table (DUES)lists the ID number, Amount paid and the date paid. The records in this table are constantly updated as new payments are received. Each record in this table is a separate payment - usually one payment per person, but occasionally someone will make two partial payments.

The second table (AMOUNT) lists the starting date, ending date, and the minimum amount due. This table is usually static, but the time frame between the start and end dates for each payment range is not a regular period of time. Each record is a new range of dates with new minimum amount due.

What I need is to have Access compare the most recent date paid in the DUES table with the range of dates in the AMOUNT table and then return the minimum amount due for that date range.

I don't care if this is done as a query, or if a new table/field is created. I would think that this should be an easy thing for Access to do, but I am having no luck in getting it to work.

[jforbes]

I think they way your data is structured is preventing this from being a simple solution. It makes logical sense to humans to split up your data in this manner, but it would basically take a human to reconcile the two tables.

I think you would be better off if you were to make one Transaction table where you post Debits and Credits against your users Account, then you can more easily create a Query that calculates the Balance for any given Date.

ACCOUNT TRANSDATE TRANSAMOUNT DETAIL
Bob      1/1/2016    -200     January Dues
Bill     1/1/2016    -200     January Dues
Frank    1/1/2016    -200     January Dues
Bill     1/1/2016      50     Payment
Bob      1/5/2016     200     Payment
Bill     1/8/2016      50     Payment
Bill     1/15/2016     50     Payment
Bill     1/21/2016     50     Payment
Frank    1/20/2016    400     Payment
Bob      2/1/2016    -200     February Dues
Bill     2/1/2016    -200     February Dues
Frank    2/1/2016    -200     February Dues
 

Doing something like this would give you the Balance for January 31st:
?DSUM("TRANSAMOUNT ", "Transations", "TRANSDATE<=#1/31/2016#")

This would give you the Balance for Bob on January 31st:
?DSUM("TRANSAMOUNT ", "Transations", "ACCOUNT='Bob' AND TRANSDATE<=#1/31/2016#")

This would give Bob's current Balance:
?DSUM("TRANSAMOUNT ", "Transations", "ACCOUNT='Bob'")

You could also create a Query to perform your calculations. You could include a GROUP BY to group the totals by Month. You could even create a crosstab query to give you fancy totals.