comparing two fields in two tables.

[nateobot]

i accidentally posted this in the "Other Topics" forum. Sorry for the double posting.



I have two fields in table1 called CompID and ElementID.

I have the same to fields in another table (table2).

How can I find out which records in table1 are not in table2. Both the compID and ElementID are the Primary Keys in each table.

Sounds simple enough but for some stupid reason I cannot get it to work.


Thanks
-Nate

 

[fredk]

Nate - Create a new query and in the pop up box select, "Find Duplicates Query Wizard" Then follow the instructions.

That should work for you.

Fred

 

[fredk]

Actually, first create a query that contains both tables you want to compare and all of the fields you want to compare....then create another query using the find duplcates query wizard on the first query you just created.

That should work.

Fred

 

[fredk]

That probably won't work for you either - Do you want to know only if both fields in both tables match or either?

Sorry, having a bad day here - Use the "Find Unmatched query wizard"!!!!

That should work for you

Fred

 

[nateobot]

Thanks for your efforts fred.
The unmatched query was the first thing i tried. However it only allows you to select one matching field in the two tables. I need to find the records where the two fields are not in the tables.

EX:

table1
CompID ElementID
26035 01454
26035 01467
26035 01455
26071 01454
26071 01455


table2
CompID ElementID
26035 01454
26035 01467
26071 01454
26071 01455



I need to know that record #3 in table1 is not included in table2.

Thanks

 

[fredk]

What about creating a query using the two tables and joining the two fields. For example:

from table 1:

TestField1: [CompID] & [ElementID]

from table 2:

TestField2: [CompID] & [ElementID]

Then run the find unmatched query based off this query. Would that work for you?

Fred

 

[nateobot]

That seems to give me the results i needed.

Thanks for the extra brainpower fred

 

[fredk]

No problem, glad to help.

Fred