Compare tables for unmatched data

[scout]

Hello,



I have a data base used as part of research, I am trying to improve for more accurate date.

First someone will go through the source data and input this via a form which will translate to 'Table 1' then a second user will independently go through the same source data but this will input to 'Table 2' so Table 1 and Table 2 should have all the information for each study participant.

I want a query that looks for unmatched data that a 'Monitor' can then manually verify.

I have tried the basic instructions for Unmatched query via the wizard but it doesn't returns any results, I have complete the same form for both users inputting different information in each field to test. The only field been the same is the ID field as this is what I watch to compare, is this why I am not getting result because the ID is the same?

I am complete beginner, so would appreciate dumbed down answers

[Bob Fitz]

Can you post a copy of the db

[scout]

No, it is full of sensitive data. If I can't figure it out, I will make an empty copy when I get a chance

[scout]

Sorry, I do have a copy with just has dummy data in

[Micron]

You built those tables like spreadsheets - that's always going to put up roadblocks. One record in each table is not much data to go on there. Regardless, trying to find dissimilar fields in records when there are about 65 fields is probably not feasible with a query. The concept of why different users should put the same data into separate tables requires some explaining IMO and might lead to suggestions on how to better achieve whatever that goal is.

You probably should read up on db normalization to see if you can come up with a schema of properly normalized tables for whatever purpose the db supports.

[Bob Fitz]

Sorry, I do have a copy with just has dummy data in

I would agree with everything that Micron has already said.
If it is possible to do what you want to do with a query, you would need to create a relationship between each field in table1 and its counterpart in table2 edited to return All records from table1 and only those records from table2 where the joined fields are equal.
I don't know of a way to automate the creation and editing o the relationships.
To get you started, in the following SQL statement, the first three field relationships have been edited and could be copied to a new query. You would need to do the other 60 or so field relationships but I don't know that it would work.

SELECT tbl1stdeBASE.StudyID, tbl1stdeBASE.Date_consent_signed, tbl1stdeBASE.Visit_date, tbl1stdeBASE.Aged_between_18_and_75, tbl1stdeBASE.Chronic_low_back_pain, tbl1stdeBASE.First_consultation, tbl1stdeBASE.NRS, tbl1stdeBASE.ODI, tbl1stdeBASE.Lumbar_multifidus_muscle_dysfunction, tbl1stdeBASE.Informed_consent, tbl1stdeBASE.Able_to_comply, tbl1stdeBASE.Indicated_for_reactiv8, tbl1stdeBASE.BMI, tbl1stdeBASE.Leg_pain, tbl1stdeBASE.Sacroiliac_joint_pain, tbl1stdeBASE.Scoliois, tbl1stdeBASE.Neurological_deficit, tbl1stdeBASE.Opioids, tbl1stdeBASE.Medications, tbl1stdeBASE.Rhizotomy, tbl1stdeBASE.Block, tbl1stdeBASE.Previous_back_surgery, tbl1stdeBASE.Previous_thoracic, tbl1stdeBASE.Litigation, tbl1stdeBASE.Claim, tbl1stdeBASE.Depression, tbl1stdeBASE.Diagnosed, tbl1stdeBASE.Planned_surgery, tbl1stdeBASE.Eligible, tbl1stdeBASE.Consent_obtained, tbl1stdeBASE.Age, tbl1stdeBASE.Gender, tbl1stdeBASE.Height, tbl1stdeBASE.Weight, tbl1stdeBASE.Pain_frequency, tbl1stdeBASE.First_doctor_visit, tbl1stdeBASE.Current_episode, tbl1stdeBASE.Previous_rhizotomy, tbl1stdeBASE.Spine_surgery, tbl1stdeBASE.Receiving_compensation, tbl1stdeBASE.Received_compensation, tbl1stdeBASE.Work_status, tbl1stdeBASE.Ability_to_work, tbl1stdeBASE.BaselineNRS, tbl1stdeBASE.DASS_depression, tbl1stdeBASE.DASS_anxiety, tbl1stdeBASE.DASS_stress, tbl1stdeBASE.DASS_total, tbl1stdeBASE.BaselineODI, tbl1stdeBASE.EQ5D_mobility, tbl1stdeBASE.EQ5D_selfcare, tbl1stdeBASE.EQ5D_usual_activities, tbl1stdeBASE.EQ5D_pain_discomfort, tbl1stdeBASE.EQ5D_anxiety_depression, tbl1stdeBASE.PROMIS29_physical_function, tbl1stdeBASE.PROMIS29_anxiety, tbl1stdeBASE.PROMIS29_depression, tbl1stdeBASE.PROMIS29_fatigue, tbl1stdeBASE.PROMIS29_sleep_disturbance, tbl1stdeBASE.PROMIS29_participate, tbl1stdeBASE.PROMIS29_pain_interference, tbl1stdeBASE.PROMIS29_pain_intensity, tbl1stdeBASE.Current_medications, tbl1stdeBASE.[5min_walk_test], tbl1stdeBASE.Sit_stand_test, tbl1stdeBASE.Adverse_eventsFROM tbl1stdeBASE LEFT JOIN tbl2nddeBASE ON (tbl1stdeBASE.Visit_date = tbl2nddeBASE.Visit_date) AND (tbl1stdeBASE.Date_consent_signed = tbl2nddeBASE.Date_consent_signed) AND (tbl1stdeBASE.[StudyID] = tbl2nddeBASE.[StudyID])
WHERE (((tbl2nddeBASE.StudyID) Is Null));

BTW I find it irritating when sample DBs are posted with loin forms, but without login details, and without some guidance of what we should be looking at.
Good luck with your project.

[Micron]

BTW I find it irritating when sample DBs are posted with loin forms, but without login details

In this case I don't think it would have helped at all. AFAICS the tables required for logging in were not included.

[June7]

Multiple users input same data as part of a quality control procedure?

Would need two queries.

1. returns records from tbl1st that don't have match in tbl2nd

2. returns records from tbl2nd that don't have match in tbl1st

Could use SELECT * instead of listing all fields.

If we start with an assumption that there will always be one record for each StudyID in each table, could build a form or report that shows the data side-by-side and visually review the data. Could even use textbox Conditional Formatting for red background when data does not match.

[CJ_London]

@Bob

you would need to create a relationship between each field in table1 and its counterpart in table2

why do you need to create a relationship? it will just be a (left) join? You are also limited to 16 joins so if more that 16 fields then that method won't work anyway.

A technique I use is to calculate a hash of all the relevant fields and left join on that - plus do the other way as well as you advise

[June7]

I tested this Find Unmatched. I stopped setting up field links at 20. Query works.

I have query with 22 tables joined to a common parent - that means 22 JOINS.

There is a limit of 32 tables in query and I have hit it - so there were 31 JOINS. I redesigned report to use subreports.

[isladogs]

My article Finding Unmatched Records compares three methods of identifying unmatched records.
At least one method should work for you (though with the caveat that I haven't looked at the file you uploaded)

[CJ_London]

I was quoting from here
https://support.microsoft.com/en-us/...8-98c1025bb47c

[June7]

I know. My experience contradicts that doc.

[isladogs]

I did several tests a few years ago in response to various posts by user @jaryszek at AWF including Join with more than 15 fields | Access World Forums (access-programmers.co.uk)

Both the OP & I found that the limit of 16 joins from/to each table was a hard limit. i tested in both A2010 & A365.
16 joins between 2 tables in an unmatched query worked. 17 joins failed.

So I'm surprised to hear than @June7 is stating that the limit doesn't apply in her tests on A2010. But reading the examples given in post #10 is I think describing a different scenario. The limit of 16 joins applies to the number of joins between 2 specific tables ... not the total number of joins in a query with multiple tables

[June7]

My post #10 described two different scenarios.

I had 20 field links between the two tables in posted db.

Then in my db, I have query with 22 tables joined to a 23rd table.