jameslagan
Registered User.
- Local time
- Today, 06:42
- Joined
- Jan 13, 2012
- Messages
- 10
Note - all example data information below is completely made up!
I am using Microsoft Access 2010.
I have a table with patients who have a disease (disease)
I have a table with patients who do not have a disease (controls)
Whether disease or control they have gender (male/female) and a location field (first two letters of postcode) and date of birth.
My aim is for each patient with a disease to get 2 controls matched for gender and location. These 2 controls I want to have a DOB nearest as possible to that of the patient with disease.
For example:
Patient 324 Male AZ 01/01/2001
Control 987 Male AZ 03/02/2001 Control 104 Male AZ 20/09/2000
I already have a query with a subquery that can do this which is great.
However,
I am noticing that different patients with disease are sharing the same controls.
For example:
Patient 999 Male AZ 21/09/2000
Control 104 Male AZ 20/09/2000 Control 56 Male AZ 22/08/2000
You can clearly see that control 104 is duplicated with Patient 999 and 987.
Does anyone know a way that if the control has already been 'used' then it is missed and the NEXT nearest control is used instead.
Ideally I always want each disease to have 2 unique matched controls.
This is my current query, simplified for ease:
It takes data from a table, [Disease|Control_tbl], which has each Disease.ID with all matched Controls.ID ordered by there absolute DateDiff (between their dates of birth)
SELECT ID, [Disease.ID], [Control.ID], [DateDiff] FROM [Disease|Control_tbl] WHERE (((ID In (SELECT TOP 2 ID FROM [Disease|Control_tbl] as Dummy WHERE Dummy.[Disease.ID] = [Disease|Control_tbl].[Disease.ID] ORDER BY Dummy.[DateDiff] ASC, Dummy.ID ASC)));
If anyone knows a way to stop duplicate controls on the subquery I would be extremely grateful.
I am using Microsoft Access 2010.
I have a table with patients who have a disease (disease)
I have a table with patients who do not have a disease (controls)
Whether disease or control they have gender (male/female) and a location field (first two letters of postcode) and date of birth.
My aim is for each patient with a disease to get 2 controls matched for gender and location. These 2 controls I want to have a DOB nearest as possible to that of the patient with disease.
For example:
Patient 324 Male AZ 01/01/2001
Control 987 Male AZ 03/02/2001 Control 104 Male AZ 20/09/2000
I already have a query with a subquery that can do this which is great.
However,
I am noticing that different patients with disease are sharing the same controls.
For example:
Patient 999 Male AZ 21/09/2000
Control 104 Male AZ 20/09/2000 Control 56 Male AZ 22/08/2000
You can clearly see that control 104 is duplicated with Patient 999 and 987.
Does anyone know a way that if the control has already been 'used' then it is missed and the NEXT nearest control is used instead.
Ideally I always want each disease to have 2 unique matched controls.
This is my current query, simplified for ease:
It takes data from a table, [Disease|Control_tbl], which has each Disease.ID with all matched Controls.ID ordered by there absolute DateDiff (between their dates of birth)
SELECT ID, [Disease.ID], [Control.ID], [DateDiff] FROM [Disease|Control_tbl] WHERE (((ID In (SELECT TOP 2 ID FROM [Disease|Control_tbl] as Dummy WHERE Dummy.[Disease.ID] = [Disease|Control_tbl].[Disease.ID] ORDER BY Dummy.[DateDiff] ASC, Dummy.ID ASC)));
If anyone knows a way to stop duplicate controls on the subquery I would be extremely grateful.