Stop duplicate controls in subquery

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.
 

Users who are viewing this thread

Back
Top Bottom