Query to pull earliest and latest results for every individual

G

Guest

There must be a better way than what I've finally come up with (after many
frustrating hours!). It probably involves writing a function, but I don't
know how to begin to write it! Maybe someone out there has an elegant
solution:

ClientTable:
fields: UID, .....lots of information on each individual in the database

TestResults:
fields: UID, TestDate, TestResult

any individual (UID) can have an unlimited number of tests

i want to pull the first and last results for each individual (and graph
them over time).

i have succeeded in getting a query that pulls the min and max dates for
each individual correctly:
Query: First_Last_Dates:
SELECT UID, Min(TestDate) AS MinOfTestDate, Max(TestDate) AS MaxOfTestDate
FROM TestResults
GROUP BY UID;

this works fine, and generates a table with one record for each individual,
no matter how many test results are entered in the TestResultTable for that
individual.

I then created two more queries:
Query: FirstResults
SELECT TestResults.UID, TestResults.TestDate AS FirstDate,
TestResults.TestResult AS FirstResult
FROM [First_Last_Dates] LEFT JOIN TestResults
ON [First_Last _Dates].UID = TestResults.UID
WHERE (((TestResults.TestDate)=[First_Last_Dates].[MinOfTestDate]));

Query: LastResults
SELECT TestResults.UID, TestResults.TestDate AS LastDate,
TestResults.TestResult AS LastResult
FROM [First_Last_Dates] LEFT JOIN TestResults
ON [First_Last _Dates].UID = TestResults.UID
WHERE (((TestResults.TestDate)=[First_Last_Dates].[MaxOfTestDate]));

finally, to get the first and last results into one table:
Query: FirstLastResults
SELECT ClientTable.UID, FirstResults.FirstDate, FirstResults.FirstResult,
LastResults.LastDate, LastResults.LastResult
FROM (FirstResults INNER JOIN ClientTable ON FirstResults.UID =
ClientTable.UID) INNER JOIN LastResults ON ClientTable.UID = LastResults.UID;

and the final result of that is a table which contains:

UID --- First Date --- First Result --- Last Date --- Last Result

which is what i want, but it seems like this is too many steps to go through!!

tia, for any thoughts
 
G

Guest

Another option can be, 3 queries

1. To return Max per UID
SELECT M1.*
FROM TestResults AS M1
WHERE M1.TestDate In (SELECT Top 1 M2.TestDate
FROM TestResults as M2
WHERE M2.UID =M1.UID
ORDER BY M2.TestDate Desc)

1. To return Min per UID
SELECT M1.*
FROM TestResults AS M1
WHERE M1.TestDate In (SELECT Top 1 M2.TestDate
FROM TestResults as M2
WHERE M2.UID =M1.UID
ORDER BY M2.TestDate)

3. Union quey that join both query from above

Select * From Query1 Union
Select * From Query2
=======================================
You save one query, and if you decide to get two last and first records, all
you have to do is change to Top 2, etc
--
I hope that helped
Good luck


janaki said:
There must be a better way than what I've finally come up with (after many
frustrating hours!). It probably involves writing a function, but I don't
know how to begin to write it! Maybe someone out there has an elegant
solution:

ClientTable:
fields: UID, .....lots of information on each individual in the database

TestResults:
fields: UID, TestDate, TestResult

any individual (UID) can have an unlimited number of tests

i want to pull the first and last results for each individual (and graph
them over time).

i have succeeded in getting a query that pulls the min and max dates for
each individual correctly:
Query: First_Last_Dates:
SELECT UID, Min(TestDate) AS MinOfTestDate, Max(TestDate) AS MaxOfTestDate
FROM TestResults
GROUP BY UID;

this works fine, and generates a table with one record for each individual,
no matter how many test results are entered in the TestResultTable for that
individual.

I then created two more queries:
Query: FirstResults
SELECT TestResults.UID, TestResults.TestDate AS FirstDate,
TestResults.TestResult AS FirstResult
FROM [First_Last_Dates] LEFT JOIN TestResults
ON [First_Last _Dates].UID = TestResults.UID
WHERE (((TestResults.TestDate)=[First_Last_Dates].[MinOfTestDate]));

Query: LastResults
SELECT TestResults.UID, TestResults.TestDate AS LastDate,
TestResults.TestResult AS LastResult
FROM [First_Last_Dates] LEFT JOIN TestResults
ON [First_Last _Dates].UID = TestResults.UID
WHERE (((TestResults.TestDate)=[First_Last_Dates].[MaxOfTestDate]));

finally, to get the first and last results into one table:
Query: FirstLastResults
SELECT ClientTable.UID, FirstResults.FirstDate, FirstResults.FirstResult,
LastResults.LastDate, LastResults.LastResult
FROM (FirstResults INNER JOIN ClientTable ON FirstResults.UID =
ClientTable.UID) INNER JOIN LastResults ON ClientTable.UID = LastResults.UID;

and the final result of that is a table which contains:

UID --- First Date --- First Result --- Last Date --- Last Result

which is what i want, but it seems like this is too many steps to go through!!

tia, for any thoughts
Click to expand...
 
Top