Most recent record

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
I have a table where I enter payments made, the date on which they were
made, and the next payment due date. I now want to select the records where
the due date has passed without a payment haveing been made.I need to select
out the records where the "next payment due date" is the most recent entry
for each client, and where that date has passed todays date. Any ideas
folks?? Tia, John
 
Hi John,
Are you looking for "next payment due date" that has passed already and
no payment was made?

Ex. Client A has no payment made and next payment due date was 7/22/05

If this is what you are looking for, try a totals query, group by client id,
max on next payment due date where payment date is null and next payment due
date < date().

Hope this helps
 
Hi Chris,
that certainly gives me some further ideas to try.....I was
trying "next payment due" <Now(), but the next payment due date is entered
in the payment table and is entered with each successive payment as it is
made. This presents a problem when you enter "payment date" Is Null. Each
"Next payment due date" entry has a corresponding current "payment date" by
default. I think I can sort it now though, thanks for the
angle..................On a different note, where can I find a listing of
the "Is Null", Now(), etc statments, I found them before on Access Help
online somewhere but lost them. Thanks for yer help, John
 
HI John,

Sounds like you want a list of available functions? I'm using Access
2003. If you select online help, and type Functions (by category) in the
searchbox, you'll get a listing of functions. Good luck.
 
Back
Top