J
janaki
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
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