The background information:

I have an access 2007 table that has >300,000 records in it. These records are test results. The tests are run on the same person multiple times, so there are several records for each person (each person has a unique identifier). The test results include a column for the date and a column for the time each test was taken along with a column for the "result". These records are for the same person over several days, so it is possible that there are 10-50 records for each person in the table. Each of these tests is stored in the table as a separate record.

What I need to do:

I need to query the table to get all the results with a value greater than a certain number (this is easy, and I have this part down). For the results of that query, I need to pull out the next test result to see what it is, but it has to have been done within a certain time-frame (i.e. < 3 hours from the first result).

I can write the query to get all of the results with the value greater than the aribtrary cutt-off number, but I cannot figure out how to link that back to the source table to get the next, subsequent test result. Any assistance would be GREATLY appreicated!!

Thanks so much!

RPetrella

If that data were sorted in order on the person, then descending order on the score/result, you'd have a way to find the "next-highest" result for each person. That is, first do your query, finding the highest result per person. Then do a second query, in which you want the ones NOT in the first query (an "unmatched", query, help available via wizard).

Then you'd need to get only the TOP 1 for each person ... , then compare that back to the first query ... is the difference (DateDiff() function) less than 3 hours?

Is that what you're looking to do?

Along the same lines that Jeff B suggests using the TOP predicate in your query, you can nest a query within your existing query. Something like this (not tested):


SELECT personID, yourdate, yourresult, (SELECT TOP 1 Q1.yourresult FROM yourtable as Q1 WHERE Q1.personID=yourtable.personID AND Q1.yourdate>yourtable.yourdate and datediff("n",yourtable.yourdate,Q1.yourdate )<180 ORDER BY personID, yourdate ASC) as NextResult
FROM yourtable

Thanks so much. I apologize for not getting back here sooner (very busy @ my new job and a steep learning curve). This information is extremely helpful!

Thanks again,
Rebecca

You're welcome and good luck with your new job!