none
Comparing Data Across Two Tables RRS feed

  • 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

    Monday, February 13, 2017 6:47 PM

All replies

  • 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...

    Monday, February 13, 2017 7:41 PM
  • 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)

    Tuesday, February 14, 2017 2:45 PM
  • 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

    Tuesday, February 14, 2017 5:44 PM