Problems with linking tables

[chubby]

I have two tables that contain SSN as primary keys.

note: (Table 1 has 645 records, table 2 has 606 records. Both tables contain the same information, they just come from different sources. My end-users want to see what personnel are missing from table 2).

I create a relationship using SSN and I tried to
do a unmatch query, but it only returned 45 records.

I printed both list and reviewed the SSN numbers, greater than 95% of them matched. so why only 45 matched? I tried changing Joins, field properties, I don't know why I can't get more names to matchup.

 

[DougP]

I would double check the number itself

It may be 999-99-9999 in one table and
999 99 9999 in the other
or some other variation

A quick wat to see problem is
open the table, maximise Access and the table, and sort the SSN column A-Z. The blanks will show at the top
Then sort it Z-A

if you do this on both table and scan up and down the records it should show bad data. DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[chubby]

I have double checked the numbers in both tables the SSN are in the same format 000-00-0000.

Table 1 uses an input mask, table 2 does not. Both tables are in the same format. (11 character spaces used with dashes)

there is no blanks columns or rows in either table.

I also tried querying both tables and linking the queries together. same thing 45 records turned only...

When I print out both tables SSN match right-up. The only thing that not the same is one table has 645 records, the other has 606 records. I'm still lost. any more ideas???

 

[JeffH]

When you are creating the queries, are you using the unmatched record wizard? I guess where I'm going with this, is that maybe the 45 records that are getting returned are the ones that don't match.

But if you just create a NEW query (without wizard) and you create the join and it only returns 45 records, then this is not the problem.

WHat I would do in this case, is select both SSN fields to view in the query, then on your join type, select "Select All Records form Table 1" (or something like that) then compare the 2 fields together. This might give some insight as to whay they don't match up. I hope this is clear...

Another thing to check to see if the database is actually storeing the dashes, perhaps one table is storing just the number but the other is storing just the dashes. I think with the input mask you have the option to store the charaters, I really think this might have something to do with it.

Have fun with it...

Jeff

 

[chubby]

Thanks Jeff and DougP
Sunday, I'm going into work to see if your theory holds up (Jeff). I just can't understand for the life of me why I can't the two to matchup.
I have one more question... How can I make sure the table without the input mask dashes are saved? I see them in a query and they show up when used in a report... What gives???