Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    16

    Unanswered: Table comparison

    Hello all

    Im trying to do something as simply as possible and was just wondering how I would go about comparing information from the different tables in my database using just access sql and no vba.

    Example scenario:-

    I have four tables within my database.

    The first table records all info about patients. In this table, many to many relationships are not broken up, so there is duplicated information. An example of what the table may conatin is PatientID, name, surname, date of birth, and symptoms. A patient can have more than one symptom and a symptom which is from a defined list can relate to more than one patient....All this information has been recorded by person A. Hope this is making sense so far!

    I then have three other tables in my database whereby one table records patient info, a second for patient_symptoms and a third recording the list of symptoms, the information has been recorded by person B.

    The information about the patients is exactly the same apart from the symptoms. I would like to compare the symptoms recorded by person a and b - a list of where there is exact matches, where there may be some similar, and where it is completely different.

    I have simplified this as much as possible using the above example....what is the best way to do the comparison???...


    Thanks in advance t.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For finding non-matching records:
    Code:
    SELECT Table_2.*
    FROM Table_2 
    LEFT JOIN Table_1 ON Table_2.Field_1 = Table_1.Field_1
    WHERE Table_1.Field_1 Is Null
    And of course, for finding matching records:
    Code:
    SELECT Table_2.*
    FROM Table_2 
    LEFT JOIN Table_1 ON Table_2.Field_1 = Table_1.Field_1
    WHERE Table_1.Field_1 Is Not Null
    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    16
    Hi sinndho and thanks for your response, also sorry for the delay in responding back!

    Unfortunately the query wasnt as simple as that. Using the same example as above I did the following:-

    For exact matches (where the same no of symptoms were selected and the exact symptoms were chosen by person and b) i did the following:-

    -Count of no of symptoms recorded by a and b for the diff patients (separate queries)
    -Another query with the count of where person a and b selected the same symptoms
    -In my actual exact match query, i added a field for the difference (so the no of symptoms recorded by a minus the no of matches).
    - In the query i then joined all the relevant tables/queries together and did a match on where the symptoms recorded by a and b were the same and where the matches was equal to zero and to be extra cautious where the count of the no of symptoms recorded by person a + b were also equal.

    For similar: Pretty much the same query as above but where the matches was not equal to zero

    For difference: I left joined a list of all the patients in the db on the exact_matches and similar queries and in the where clause retrieved all the patients where the patient number was not in exact_match or similar qs (so similar to your example for finding the complete diff).

    Hope this all makes sense but I think itll only make sense to me!!! Would post the queries but I tried to make the example in these posts as simple as possible to what I was trying to achieve...

    So yes can be done just using sql and is pretty easy to do!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •