Unmatched Query (hot to see unmatched data)

[J Haden]

In an unmatched query, is there a way to see the exact data that is
unmatched? For example, if SSN in one table does not match SSN in the
2nd table, I would like to be able to see both SSN's in the result
(both the correct SSN and the mismatched SSN). I modified the
unmatched query and selected "Show" to see the unmatched field, but
that column returns empty. Am I missing something obvious?

Thanks so much!
Judy

 

[George Nicholson]

Judy:

An "unmatched" query shows you ALL the records in one table that do not have
a corresponding value in table 2 (often represented by an empty field).

If the query was designed to show you SSNs in table1 that don't exist in
table2, what is it that you expect/want the query to show you since the
value doesn't exist? For every Table1 value that can't be found in table2,
*every* record in table2 is "mismatched".

**************************
However, *IF* you are trying to see if the SSNs are the same for each
matching name in 2 tables, that is an entirely different thing. In that
case you would join the 2 tables on the names and then include a field in
the query like: Match?:[Table1].[SSN] = [Table2].[SSN] (Criteria: False)

If this is what you are doing, you can include any fields you want from
table1 or table 2, including the SSN fields themselves.

.....And once you get the SSNs straightened out, you delete the SSN field
from one of the tables so you are only storing the value ONCE per person and
don't have to do this again.
**************************

Hope this helps,

 

[John Vinson]

In an unmatched query, is there a way to see the exact data that is
unmatched? For example, if SSN in one table does not match SSN in the
2nd table, I would like to be able to see both SSN's in the result
(both the correct SSN and the mismatched SSN). I modified the
unmatched query and selected "Show" to see the unmatched field, but
that column returns empty. Am I missing something obvious?

Think about this:

Suppose you have a SSN 999-99-9999 in Table1, and five thousand
records with other SSN's in Table2, none of them equal to 999-99-9999.

All five thousand are mismatched SSN's.

Do you want to see all five thousand? If not, which one do you want to
see?

 

[J Haden]

Sorry - I didn't do a very good job explaining. Here's what I'm workin
with. I am trying to use Access 2002 to compare data from one in-hous
system to a future new in-house system. The data copied from the ol
system is Table 1 and the data from the new system is Table 2. Th
field names are identical between the two tables. By the go live dat
for the new system, we want to have identified and resolved all th
differences. We want to make sure that Judy Blue's SSN in the ol
system (Table 1) is the same in the new system (Table 2). If it i
not, then we need to see (in ONE query or new table) the field content
for the SSN fields from BOTH Tables. The info is then passed along t
other dept's for resolution. They want to see what the discrepant SS
looks like in the old system (Table 1) and what it looks like in th
new system (Table 2).

FYI: SSN is only one of the many fields that I have to compare to fin
any differencies.

Thanks for your help!
Jud

 

[John Vinson]

We want to make sure that Judy Blue's SSN in the old
system (Table 1) is the same in the new system (Table 2). If it is
not, then we need to see (in ONE query or new table) the field contents
for the SSN fields from BOTH Tables.

Ok... this isn't too hard, but with a Big Gotcha.

Create a Query joining the two tables, joining by firstname and by
lastname. Select all the fields that you want to compare (SSN,
birthdate, anything else).

On *separate* lines of the query grid, so it uses OR logic, under each
field in Table2 put

<> [Table1].[SSN]

or whatever field you're trying to compare.

This will find all records where any one of these fields is discrepant
between the two tables for the same name.

The GOTCHA is: if you have "Judy Blue" in Table1, and "Judith Blue" or
"Judy Robinson" (maiden name) in Table2, they won't match.

You may want to run a similar query linking the two tables by SSN
(after fixing any SSN errors discovered in this query) to find name
discrepancies.