Compare and return 2 most recent dates

  • Thread starter Thread starter lookoutlisa
  • Start date Start date
L

lookoutlisa

Hi,

I have a query that brings in multiple assessment dates (many different
assessments) for each child, including nulls if they haven't had a
certain assessment yet. Each child is 1 row. I need to return the most
recent date, and the one before that one for a new report. Any ideas? I
thought the Top Values property would work, but that only returns the
top 2 childIDs.

Thanks!
Lisa
 
You will probably need to use TOP 2 in a subquery in the where clause. Hard to
give you a specific solution without fields and tables. Something like

SELECT *
FROM Children as C Inner Join Assessments as A
ON C.ChildID = A.ChildID
WHERE A.AssessmentDate in
(SELECT TOP 2 Temp.AssessmentDate
FROM Assessments as Temp
WHERE Temp.ChildID = A.ChildID
ORDER BY Temp.AssessmentDate Desc)
 
John said:
You will probably need to use TOP 2 in a subquery in the where clause. Hard to
give you a specific solution without fields and tables. Something like

SELECT *
FROM Children as C Inner Join Assessments as A
ON C.ChildID = A.ChildID
WHERE A.AssessmentDate in
(SELECT TOP 2 Temp.AssessmentDate
Click to expand...

Hi John,

That sounds logical... but I'm a little lost. Here is my current query:

SELECT [Child data query -IT].IDNO, [Child data query -IT].CNTRNAME,
Ounce4mo.Date AS 4m, Ounce8mo.Date AS 8m, Ounce12mo.Date AS 12m,
Ounce18mo.Date AS 18m, Ounce24mo.Date AS 24m, Ounce30mo.Date AS 30m,
Ounce36mo.Date AS 36m, Ounce42mo.Date AS 42m
FROM ((((((([Child data query -IT] LEFT JOIN Ounce4mo ON [Child data
query -IT].IDNO = Ounce4mo.IDNO) LEFT JOIN Ounce8mo ON [Child data
query -IT].IDNO = Ounce8mo.IDNO) LEFT JOIN Ounce12mo ON [Child data
query -IT].IDNO = Ounce12mo.IDNO) LEFT JOIN Ounce18mo ON [Child data
query -IT].IDNO = Ounce18mo.IDNO) LEFT JOIN Ounce24mo ON [Child data
query -IT].IDNO = Ounce24mo.IDNO) LEFT JOIN Ounce30mo ON [Child data
query -IT].IDNO = Ounce30mo.IDNO) LEFT JOIN Ounce36mo ON [Child data
query -IT].IDNO = Ounce36mo.IDNO) LEFT JOIN Ounce42mo ON [Child data
query -IT].IDNO = Ounce42mo.IDNO
WHERE ((([Child data query -IT].CNTRNAME)="W Boone"));

So I am comparing 4m, 8m, and so on...
Thanks!
Lisa
 
Ouch! Bad design.

That data is totally non-normalized. Your bad structure will make this difficult.

You should have one table Ounce with a field that tells you the type of
"assessment" and then has the other elements of information in it.

With this structure, I'm not sure what can be done.

Try normalizing the "assessment" data with a UNION query.

SELECT "Ounce4Mo" as TestType,IDNo, [Date]
FROM Ounce4Mo
UNION
SELECT "Ounce8Mo", IDNo, [Date]
FROM Ounce8Mo
UNION
SELECT "Ounce12Mo", IDNo, [Date]
FROM Ounce12Mo
UNION
....
UNION
SELECT "Ounce42Mo", IDNo, [Date]
FROM Ounce42Mo

Save the query as Assessments and use it in another query

Then use the saved query to get the results.

SELECT C.IDNO, C.CNTRNAME, A.TestType, A.Date
FROM [Child data query -IT] as C Inner JOIN Assessments as A
ON C.IDNo = A.IDNo
WHERE C.CNTRNAME="W Boone"
AND A.Date in
(SELECT Top 2 Temp.Date
FROM Assessments as Temp
WHERE Temp.IDNo = A.IDNo
ORDER BY Temp.Date Desc)



John said:
You will probably need to use TOP 2 in a subquery in the where clause. Hard to
give you a specific solution without fields and tables. Something like

SELECT *
FROM Children as C Inner Join Assessments as A
ON C.ChildID = A.ChildID
WHERE A.AssessmentDate in
(SELECT TOP 2 Temp.AssessmentDate
Click to expand...

Hi John,

That sounds logical... but I'm a little lost. Here is my current query:

SELECT [Child data query -IT].IDNO, [Child data query -IT].CNTRNAME,
Ounce4mo.Date AS 4m, Ounce8mo.Date AS 8m, Ounce12mo.Date AS 12m,
Ounce18mo.Date AS 18m, Ounce24mo.Date AS 24m, Ounce30mo.Date AS 30m,
Ounce36mo.Date AS 36m, Ounce42mo.Date AS 42m
FROM ((((((([Child data query -IT] LEFT JOIN Ounce4mo ON [Child data
query -IT].IDNO = Ounce4mo.IDNO) LEFT JOIN Ounce8mo ON [Child data
query -IT].IDNO = Ounce8mo.IDNO) LEFT JOIN Ounce12mo ON [Child data
query -IT].IDNO = Ounce12mo.IDNO) LEFT JOIN Ounce18mo ON [Child data
query -IT].IDNO = Ounce18mo.IDNO) LEFT JOIN Ounce24mo ON [Child data
query -IT].IDNO = Ounce24mo.IDNO) LEFT JOIN Ounce30mo ON [Child data
query -IT].IDNO = Ounce30mo.IDNO) LEFT JOIN Ounce36mo ON [Child data
query -IT].IDNO = Ounce36mo.IDNO) LEFT JOIN Ounce42mo ON [Child data
query -IT].IDNO = Ounce42mo.IDNO
WHERE ((([Child data query -IT].CNTRNAME)="W Boone"));

So I am comparing 4m, 8m, and so on...
Thanks!
Lisa
Click to expand...
 
Back
Top