Query Design for Identical Values in Separate Tables

[JeffreyE]

My laboratory uses an Access database to record specimens as they are received. We have a patient table with a unique identifier (medical record number) that ties into several specimen types (cytology, surgical path specimens, etc.) in a one-to-many relationship. The specimen tables are related to the patient table thru the Medical record number. The specimen tables have a variety of fields including accession date. I am trying to design a query that would tell me this. If a certain group of patients have a cytology specimen in a certain quarter of this year (for example April thru June), I want to know at the end of the following quarter (September) which of those patients have also had a concurrent or follow-up surgical pathology specimen (separate table) during the 6 month period of April thru September. Thanks for your help in advance.

 

[alvechurchdata]

Sounds like you need a sub-query. The idea is that you write a query to select a list of ID codes from one table then pick the records matching that list from another table. Something like:
[TT]
SELECT * FROM Patients
WHERE PatientID IN
(SELECT PatientID FROM Specimen
WHERE TestDate<#30/09/2005#)
[/TT]

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[JeffreyE]

Dear alvechurchdata,

Your help was just what I needed. I have to work around my lack of SQL knowledge but what I have now are two queries, one to select the medical record number of surgical path reports for the last six months and then a second to find the matching medical record number of cytology cases in the appropriate study quarter. Thanks very much for your assistance. In fact much of the help was on your web site under the heading of subqueries.