Previous received date

[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

 

[John Verhagen]

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;

 

[John Vinson]

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
----------

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.