Date ranges based on related table

[Dorian]

Any tips on best way to do this.
I have two tables, related one to many Case--Payments
I need a query that finds the last payment for each case and outputs an X in
one of three columns based on DateDiff('d',LastPayment,Date()) being 0-30
days, 31-90 days or over 90 days.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KARL DEWEY]

UNTESTED UNTESTED UNTESTED
SELECT Case.ID, (SELECT Max([PayDate]) FROM Payments WHERE Case.ID =
Payments.ID) AS LastPayment, DateDiff('d', (SELECT Max([PayDate]) FROM
Payments WHERE Case.ID = Payments.ID), Date()) Between 0 AND 30, "X", "") AS
[0 - 30], DateDiff('d', (SELECT Max([PayDate]) FROM Payments WHERE Case.ID =
Payments.ID), Date()) Between 31 AND 90, "X", "") AS [31 - 90],
DateDiff('d', (SELECT Max([PayDate]) FROM Payments WHERE Case.ID =
Payments.ID), Date()) > 90, "X", "") AS [Over 90]
FROM Case LEFT JOIN Payments ON Case.ID = Payments.ID
ORDER BY Case.ID;

 

[John Spencer]

If that is ALL you need then the following should work.

SELECT Payments.CaseID
, IIF(DateDiff("d",Max(PaymentDate), Date()) <30),"X",Null) as 30Day
, IIF(DateDiff("d",Max(PaymentDate), Date()) Between 31 and 90),"X",Null) as 90Day
, IIF(DateDiff("d",Max(PaymentDate), Date()) >90),"X",Null) as Over90
FROM Payments
Group By Payments.CaseID

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County