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