Prev: Updated fields query
Next: Splitting a table into smaller tables
From: Dorian on 27 Jan 2010 13:02
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".
From: KARL DEWEY on 27 Jan 2010 13:31
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;

--
Build a little, test a little.


"Dorian" wrote:

> 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".
From: John Spencer on 27 Jan 2010 15:10
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

Dorian wrote:
> 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".
 | 
Pages: 1
Prev: Updated fields query
Next: Splitting a table into smaller tables