The Old Joel on Software Forum: Part 4 (of 5) - Data Comparison at two tables via MS Access

Data Comparison at two tables via MS Access

Hi! Everyone, I want to compare my data's (value) at two different tables via MS Access. My objective is to segregate the matched and unmatched data.

Please help.

Thanks,

alvin

alvin benito
Wednesday, May 12, 2004

I am not sure if the Jet Engine recognizes these three standard SQL commands: UNION, INTERSECT and MINUS. If it does then you have a clear case.

<MATCHED_RECORDS>
Your ResultSet = (A Intersection B)
</MATCHED RECORDS>

<UNMATCHED_RECORDS>
Your ResultSet = (A U B) - (A Intersection B)
</UNMATCHED RECORDS>

Sathyaish Chakravarthy
Wednesday, May 12, 2004

Ok, I created two dummy tables in MS Access with roughly the same structure.

[Location]
=========
[Location ID] numeric
[Location Name] Text

[Branch]
========
[Branch ID] numeric
[Branch Name] Text

Here goes your unmatched records resultset:

<SQL for (AUB) - (A Intersection B)>
select * from Location
UNION
select * from Branch
where [Branch Name] not in
(select [Location Name] from Location, Branch where [Location ID] = [Branch ID]);
</SQL for (AUB) - (A Intersection B)>

Sathyaish Chakravarthy
Wednesday, May 12, 2004

Hold on guys, doesn't Access have all this built in?

I seem to remember it has an unmatched-data query wizard, when you create a new query. There's a duplicate-data wizard too.

Okay, so I know these are trivial to solve in SQL, but why bother with the typing if Access will do it for you.

Steve Jones (UK)
Thursday, May 13, 2004

I typed SQL because I had no memory of myself using an unmatched-query wizard. Well, that's the way to go then.

Sathyaish Chakravarthy
Thursday, May 13, 2004

Unmatched Query wizard only looks for null values.

Angel DuPont
Friday, June 4, 2004