Previous received date

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

As payments come in they log the 'date received'. A
query needs to display this date received as well as the
previous payment's date received.

So if John Smith sends payments on 04/10/2003,
05/12/2003, and 06/15/2003

The query should display
----------
Last Payment Date Current Payment Date
04/10/2003
04/10/2003 05/12/2003
05/12/2003 06/15/2003
----------
Maybe something along the lines of calculating the
greatest date for that customer less than a
given 'current payment date'.

I keep thinking that this isn't a hard one, but I've been
scratching my head all morning trying to figure it out.


Thanks for suggestions,
RT
 
Assuming your table is called tblPayments, and you have an ID field for each
different customer.
They this:
SELECT tblPayments.ID, (select max([Date Received]) From tblPayments P where
P.ID=tblPayments.ID and P.[Date Received]<tblPayments.[Date Received]) AS
[Last Payment Date], tblPayments.[Date Received] AS [Current Payment Date]
FROM tblPayments;
 
As payments come in they log the 'date received'. A
query needs to display this date received as well as the
previous payment's date received.

So if John Smith sends payments on 04/10/2003,
05/12/2003, and 06/15/2003

The query should display
----------
Last Payment Date Current Payment Date
04/10/2003
04/10/2003 05/12/2003
05/12/2003 06/15/2003
----------
Click to expand...

Set Last Payment Date to

=DMax("[Date Received]", "[yourtablename]", "[CustomerID] = " &
[CustomerID] & " AND [Date Received] < #" & [Date Received] & "#")

This will look up the latest date for this customer prior to the date
in the current record.
 
Back
Top