Find unmatched query based on multiple fields?

[PATSYS]

Hi all,

I am an absolute MS Access newbie. Excel has been able to provide me with all my needs so far but I was told that many of the things I do in Excel can be done easier and smarter in Access. So that is why my sudden interest.

Anyway about my question..

I have 2 tables, CODE1 and CODE2. Both tables have the same number of fields and same field names..Field1, Field2, Field3, Field4 and Field5.

I would like to run a query to show me all records in CODE2 that is not existing in CODE1. The basis of matching is all the fields i.e. Fields 1-5.

What I tried so far...

I tried to create a query using the FIND UNMATCHED QUERY WIZARD but I could not get it to work.

1. I clicked the INSERT menu, chose QUERY
2. Chose FIND UNMATCHED QUERY WIZARD
3. Under the "WHICH TABLE OR QUERY CONTAINS RECORDS YOU WANT...", I chose CODE2
4. Under the "WHICH TABLE OR QUERY CONTAINS THE RELATED RECORDS", I chose CODE1
5. Under which "WHAT PIECE OF INFORMATION IS IN BOTH TABLES, this is where I am confused because it seems I can only select 1 matching fields. So what I did I clicked each fields (1-5) in both cides and pressed the <=>

6. Under the "WHAT FIELDS DO YOU WANT TO SEE...", I chose all the fields.

When I viewed the results, it shows nothing although I know there are 2 records in CODE2 that are not in CODE1.

Pls help.

Thanks.

 

[james_lankford]

from the database manager (I don't know what its really called) click Queries / New / Design View / OK
then a list of tables will come up
FIRST click on CODE2 and Add, then click on CODE1 and Add, then Close
now left click on field1 in CODE2 and drag it to field1 in CODE1
the fields will become connected by a black line
left click on the black line and make it bold (this may be difficult to click exactly on the line) once its bold, right click on it and select Properties
click the circle that says "Include ALL records from CODE2 and only those records from CODE1 where the joined fields are equal";
click OK; now the connecting line will have an arrow on the end of it pointing to CODE1

do that for all 5 fields

now drag the 5 fields from CODE2 down into the query builder grid
then drag the 5 fields from CODE1 down into the query builder grid (so it will be the 5 fields from CODE2 and then the 5 fields from CODE1)

now ONLY under the CODE1 fields put
is null
in the criteria row
so you're putting
is null
for ALL 5 fields of CODE1 and make sure all the is nulls are on the same criteria row

now press the red exclamation mark

 

[PATSYS]

Thanks James, it works. Though honestly I think it is more cumbersome (especially if I have more than just 5 fields) than in excel. In excel, I could just do an advanced filtering with CODE1 being the criteria range.

Thanks again.