Related fields with identical data appears in "unmatched" query

[Ruth]

I have a database that has had a number of tables tacked onto it as
time has gone by. I have an employee table with about 9,000 records
that uses SSN as the KeyID (TblEmpl). I received an Excel file this
week with records for about 6,000 of those employees who were written
checks (some received multiple checks, so I will have a one-to-many
relationship there). SSN was not used as an ID field in that file.
After importing, I added an SSN field to the Check list (TblCkList) and
by linking the name and payee fields, I updated the SSN field for as
many records as I could. There were around 100 where there were
discrepancies in spelling (i.e., Johnson instead of Johnston) but there
were some where it appeared that nothing was different in the name
field at all. I should have known something was up then, but I pressed
on.

I manually looked up the SSN (in TblEmpl) and copied and pasted to the
SSN field in TblCkList. After I completed this, I wanted to make sure
that all records were matching. I ran a Find Unmatched query, and darn
if that same group of employees didn't show up! (TblCkList Without
Matching TblEmpl created using the wizard with the match on the SSN
field.) I have visually inspected the fields, checked the field
properties in each table (they are identical), copied and pasted the
field contents into notepad to see if i could spot any discrepancies.

Any ideas?
Thanks,
Ruth

 

[John Vinson]

Any ideas?

Look for: differences in spacing (John Trent vs. John Trent);
nonprinting characters; trailing blanks ("John Trent " vs. "John
Trent")...

John W. Vinson[MVP]

 

[Ruth]

Well, that might explain name fields not matching, but this is the SSN
that isn't matching. The SSN field in both tables is formatted with an
input mask that stores the hyphen in the field, so there's no
difference between SSN with or w/o the hyphen character as hyphens are
automatically added and stored. The field length is limited to 11
characters on both tables as well, so there are no trailing spaces. Any
missing numbers would be evident.

Any other ideas?

Anyone else?

Thanks for your thoughts!

 

[John Vinson]

Well, that might explain name fields not matching, but this is the SSN
that isn't matching. The SSN field in both tables is formatted with an
input mask that stores the hyphen in the field, so there's no
difference between SSN with or w/o the hyphen character as hyphens are
automatically added and stored. The field length is limited to 11
characters on both tables as well, so there are no trailing spaces. Any
missing numbers would be evident.

Any other ideas?

Try running a search for

NOT LIKE "###-##-####"

to see if there are any records which might have been entered with a
different input mask, or with = as a typo for -, or other oddities.

John W. Vinson[MVP]

 

[Ruth]

That helped! The series of SSNs did not have the "-" character. While
the field was set up to store the hyphen as a character in the field,
retyping or copying and pasting did not seem to store the character. I
had to take the input mask off the field, add the hyphens manually, and
voila! We have a match!

Thanks for your suggestion.

Ruth