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 queryNext: Splitting a table into smaller tables