I’m 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.
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???...
Hi sinndho and thanks for your response, also sorry for the delay in responding back!
Unfortunately the query wasn’t 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 q’s (so similar to your example for finding the complete diff).
Hope this all makes sense – but I think it’ll 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!