There are 2 tables - Contracts and Payment Schedule.

Contracts stores contract level information per leasing contract.

Payment Schedule stores payment dates and amounts for leasing contracts. Some contracts have multiple payment amounts and payment dates - but usually a maximum of around 3.

What I need to do is extract out Contract Level information and then add in ONLY the next payment date and amount from the Payment Schedule table. I was thinking that somehow maybe I could write an Expression in my query that would give me MAX payment date IF payment date was less than say TODAY() or a given date.

Does anyone know how that can be written in Access as an Expression?

Example:-
Contract No. 333.

If I extract ALL data from Payment Schedule table, I get:-

Contract No. Payment Date....... Payment Amount

333..............01/01/04............£350
333..............02/05/05............£500
333..............28/02/07............£800

So, at the moment, I'm getting duplicate contract numbers and ALL 3 payments. I just want the payment date and amount for the oldest payment date prior to todays date. Does that make sense?

Any help appreciated ....... as always :-)

Hi,
I think your table structures might be incorrect so you might want to post them so we can verify that you are on the right track. BUT...have a look at the dmax function! Examples
here !
HTH
Good luck

Do you want the oldest payment date as you state, or the latest payment date as you imply?

Thanks.

Syntax for DMAX is:-

DMax(expr, domain, [criteria])

So ....... do I understand from this that:-

expr = Payment_Date ........... data field
domain= PAYMENT_SCHEDULE ........ table
criteria = < todays date?

IF yes, would my expression be:-

Expression:DMAX(Payment_Date,PAYMENT_SCHEDULE,<TODAY())

Unfortunately I've just tried that and it did not work and its prompting me for a value for the PAYMENT_SCHEDULE parameter.

Hi Frank,

I'd like the oldest payment date and the corresponding payment value IF payment date is less than todays date.

Out of curiosity, by definition, "Today" is the oldest date. Why test for that particular condition? It seems redundant.

Actually, I'm thinking that oldest date means earliest date, latest date means today's date.

In the example below, there are 3 payment dates in the Payments Scheduled Table.

333..............01/01/04............£350
333..............02/05/05............£500
333..............28/02/07............£800

Let me explain this further. This is a leasing contract. On 1st Jan 04, customer pays £350 per month. Customer continues to pay this amount until 02/05/05 when the payment amount that he needs to pay changes to £500. Customer continues to pay £500 until the 28th February 2007 when the payment amount changes to £800.

I want to extract out the next payment amount that the customer will pay on the contract. So.... I thought if I could ask Access to look at all the payments in the Payments Scheduled Table and give me the maximum date and value BUT only if the date is less than todays date. So using this criteria, it would return me the following from the above example:-

333..............02/05/05............£500

Reason is that this is the maximum date that is less than 09/03/06 and this is the value that relates to that date.

Does that make sense? Alternatively can you think of another way?

I would probably do it like this:

QueryA would fetch all the payment dates less than today's date.

QueryB would be a Group By query that would use QueryA as its "table"

I would group by on customerID, Max on date.

If you needed more fields from the table, join this query back to the table on the customerID to CustomerID and MaxDate to Date fields.
I have attached an example. See Query12, Query13, Query14.
Edited by: fkegley on Thu Mar 9 14:33:56 EST 2006.

Edited by: fkegley on Thu Mar 9 15:07:13 EST 2006.

Edited by: fkegley on Thu Mar 9 15:07:49 EST 2006.

Excellent ....... thanks again Frank. I'll try to replicate what you've achieved. :-)

You're welcome. I am glad I could help.