Comparing Data Across Two Tables

Question

I have two tables for tracking patients, each table contains data specific to a medical issue. The issues are only slightly related and do not have too many fields in common. Putting them in a single table would be impractical. I have a qry for each table that generates a unique patient identifier. This would be the same for each table when the patient appears in each one.

Example

Qry 1

Date             PTID

1/1/1980      32567SD

1/2/1980      12345FE

1/1/1980      32567SD

Qry 2

Date             PTID

1/1/1981      32567TR

1/2/1980      12345FE

1/1/1980      32567SD

I am trying to build a Qry that will do the following.

How many times the PatientIdentifier appears in each qry, # of times in Qry 1 and # of times in Qry2

How many times the patient identifier from Qry 1 appears in Qry 2 and From 2 in 1

How many times the patient identifier shows up in table 1 after the date it appears in table 2

I was thinking that creating a Union Qry might be the best approach but thought I would post it here first to see if someone might have a better solution.

Thank you in advance.

Jason

Answer 1

Hi Jason,

I'm not sure a UNION query will do it. I think maybe you'll need a query of DISTINCT patient identifier for each query. Then, you can use the list of unique identifiers to do your counts.

Just my 2 cents...

Answer 2

Each of your tasks is a separate query unto itself.  Attempting to perform all tasks in a single query, union or otherwise, is not feasible.

You can display all the information together either in a form or a report via a couple different methods:

* sub forms/reports

* DLook up fields

* joining the task queries together in a single presentation query (where the IDs all exist in each task query)

Answer 3

1.  'How many times the PatientIdentifier appears in each qry, # of times in Qry 1 and # of times in Qry2'

SELECT "Qry1" AS SourceQuery, PTID,
COUNT(*) AS PatientCount
FROM Qry1
GROUP BY PTID
UNION ALL
SELECT "Qry2", PTID,
COUNT(*)
FROM Qry2
GROUP BY PTID;

2.  'How many times the patient identifier from Qry 1 appears in Qry 2 and From 2 in 1'

SELECT "One in Two" AS CountType, PTID,
COUNT(*) AS PatientCount
FROM Qry2
WHERE EXISTS
       (SELECT *
        FROM Qry1
        WHERE Qry1.PTID = Qry2.PTID)
GROUP BY PTID
UNION ALL
SELECT "Two in One", PTID,
COUNT(*)
FROM Qry1
WHERE EXISTS
       (SELECT *
        FROM Qry2
        WHERE Qry2.PTID = Qry1.PTID)
GROUP BY PTID;

3.  'How many times the patient identifier shows up in table 1 after the date it appears in table 2'

SELECT PTID,
COUNT(*) AS PatientCount
FROM Qry1
WHERE [Date] >=
      (SELECT MIN([Date])
       FROM Qry2
       WHERE Qry2.PTID = Qry1.PTID)
GROUP BY PTID;

I hope I've understood your requirements.  The wording of 3 is a little ambiguous, but I'm a assuming it means 'after the earliest date it appears in table 2'

I would recommend that you change the name of the Date column to something specific like TreatmentDate or similar.  Date is the name of a built in function, so as a 'reserved word' should not be used as an object name.

---

Ken Sheridan, Stafford, England