Selecting top 2 rows of a sorted table for EACH Person ID

[jameslagan]

I am working on a project with MS Access 2010.

A person with an illness needs to matched against two people without the illness, or controls. These two controls need to be the nearest in terms of age as possible.

I have matched suitable controls but I am struggling to make a query to select just the two controls with nearest dates of birth (Date difference) to the people with the disease.

(The date_difference field is an absolute number so even if they are younger or older the number is always positive - obviously, a higher number indicates they are more years apart).

Can anyone help?

For example... (obviously all names are completely made up!)

LINK(i.imgur.com/dHVxR.jpg)

I would like the outcome of a query to generate the following:

LINK(i.imgur.com/LdInP.jpg)

Any help will be MUCH appreciated!

James

 

[recyan]

just chk if fiddling around with below, gives some guidelines :

SELECT 
	tblDiseases.Disease_ID, 
	tblDiseases.disease_surname, 
	tblDiseases.control_id, 
	tblDiseases.control_surname, 
	tblDiseases.date_diff
FROM 
	tblDiseases
WHERE 
	((
	(tblDiseases.ID) 
	In 
	(
		SELECT TOP 2 
			ID 
		FROM 
			[tblDiseases] AS Dummy 
		WHERE 
			Dummy.Disease_ID = [tblDiseases].Disease_ID 
		ORDER BY 
			Dummy.date_diff ASC, 
			Dummy.ID DESC
	)
	))
ORDER BY 
	tblDiseases.Disease_ID, 
	tblDiseases.date_diff, 
	tblDiseases.ID;

Thanks

 

[jameslagan]

Thank you so much recyan,

I really appreciate it. Solution works very well.

 

[recyan]

Glad you found it helpful.

Thanks