Find the next record by date for a person and compare it to their initial record

jameslagan

Registered User.
Local time
Today, 17:23
Joined
Jan 13, 2012
Messages
10
I'd be very grateful for any help on this Access scenario.

I have a massive table (tbl) with:
id, person_id, date, resultA, resultB, resultC, resultD
with about 20000 results.

e.g.
id 1 person_id 2 (Joe Bloggs (made up name)) result on date (01/01/2012) resultA (P), resultB (N), resultC (N), resultD (N)I need to perform a query where it selects the records where one of the tests was positive and the remainder negative. EASY!
SELECT id, person_id, date, resultA, resultB, resultC, resultD FROM tbl WHERE resultA = "P" AND resultB = "N" AND resultC = "N" AND resultD = "N"
The tricky bit is that I want to find out what the NEXT result for that person, by date, is for each of the records that meet the criteria above. I then want to compare the two results and see if the results have changed. i.e. resultB might change from "N" to "P" in the say, 1 month, between Joe Bloggs (made up name) tests and I need to capture that.

I am sure I need to use subqueries in access 2010 but I can't quite work out how to do it.

Any help will be grately appreciated!

Many thanks,

James
 
Is this not already normalised? I don't see any repetition of data.

Do you propose I have a table for resultA where "N" or "P" correspond to person_id and date? This doesn't seem to make a great deal of sense to me.

In additional I have no idea how I would move this mass of data into a more normalised form!

James
 
Having a field named 'date' is a bad idea (its a reserved word in Access and can cause issues in code). For your problem, I'd make a sub-query to determine the next date for each record:

SELECT id, person_id, date, resultA, resultB, resultC, resultD, DMax("[date]", "tbl", "[date]>=#" & [date] & "#") AS NextDate FROM tbl;

Now for the main query you bring in that query and link it tbl by joining person_id to person_id and NextDate to date. From there you can make calculated fields that determine if each result field has changed from the current record to the next.
 
I've tried that - it doesn't seem to work. The NextDate column is filled with the same date for all of them.
 
Sorry, I screwed that up all kinds of ways. The DMAX should be a DMIN, it shouldn't be greater than or equal and you need to include the person_id in the where portion. This is what it should be:

SELECT id, person_id, date, resultA, resultB, resultC, resultD, DMin("[date]", "tbl", "[person_id]=" & [person_id] & " AND [date]>#" & [date] & "#") AS NextDate FROM tbl;
 
Is this not already normalised? I don't see any repetition of data.

Do you propose I have a table for resultA where "N" or "P" correspond to person_id and date? This doesn't seem to make a great deal of sense to me.

In additional I have no idea how I would move this mass of data into a more normalised form!

James

Please describe what ResultA means, and ResultB, ResultC....
All results are for a specific Date for a specific Person.
I'm not understanding Results and I don't see how Results relate to(tests) in your statement
it selects the records where one of the tests was positive and the remainder negative

Perhaps an example would help sort out ResultA, ResultB...
 

Users who are viewing this thread

Back
Top Bottom