Windows Forum

Ask something
Discussion Locked

Comparing Records In Two Tables

0 helpful
8 replies

by gcheels

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have two tables that contain similar data in MS Access. For this example let say they both relate to customer info. Both tables have a field called CustID that I can link the two with. Both tables have missing or different data in the address field and some records have the data missing on both fields in each table. I would like to write a query that will look at both tables and display the records that the address field do not match as well as the instances where there is no data in the address field in both tables. Thanks.

8 replies

by bobho

You can either do this in the Access query builder or write the query by hand. Here is an example using the Access query designer. Assume I have Table1 and Table2 with my customer info in them.

Link the 2 tables on the customerid field.

Selectthe fields you want to include.

In the criteria for Table1 address field enter:

"<> [Table2].[address field]" without quotes and substituting your table name and address field name.

In the next criteria line for the address field criteriaenter "NULL" again without the quotes.

This will select all of the records in the 2 tables where the specific address fields do not match or where the address field in Table1 is blank.

Regards,

DrBob:>)

by shmaltz

Use a union query. To create a union query, create to select statements, one for each table. Do the critira on both of the select statements. Write one select statement then make sure there is new line and write the second select statement, but makesure that the second statement starts with UNION SELECT. For more information on union querys look up Access help.

This discussion is closed to new replies.