Cannot join on Social Security Num

[Flick Olmsford]

I have a table that I am trying to add an ID number into from another. The
two tables have a Social Security Number that is the only way to link the
tables. I am trying to change that

I am first doing a select query before the update query

When I add both tables to a query and tie them together by the Social
Security Num, (regular join, not outer or inner) I get zero records
retrieved. There are matching Social Security Nums in both tables though

The two SocSecNum fields have input picture clauses 000\-00\-0000;0;_
Both fields in both tables are the same length and input pictures

 

[Amy Blankenship]

I have a table that I am trying to add an ID number into from another.
The
two tables have a Social Security Number that is the only way to link the
tables. I am trying to change that

I am first doing a select query before the update query

When I add both tables to a query and tie them together by the Social
Security Num, (regular join, not outer or inner) I get zero records
retrieved. There are matching Social Security Nums in both tables though

The two SocSecNum fields have input picture clauses 000\-00\-0000;0;_
Both fields in both tables are the same length and input pictures

What do you mean regular, not outer or inner? Please post your query.

 

[John Spencer]

It sounds like you should get matches.

Are both tables Access tables?

For example, IF one table is an SQL table and the len is longer than the
number of stored characters and the field is defined as Char instead of as
VarChar then you could have trailing characters. Char pads out the field to
the full specified length.

Open your query in SQL view and try change the Join clause to something like

Table1 INNER JOIN Table2
ON TRIM(Table1.SSN) = TRIM (Table2.SSN)

Once you do that you cannot view the query in the query design view (the
grid) any longer. If you still don't get matches, then I would suspect that
there is some other difference in the way the SSN are stored.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KARL DEWEY]

Maybe check the actual stored lenght of the data ---
Table1_Lenght: Len(TRIM(Table1.SSN))
Table2_Lenght: Len(TRIM(Table2.SSN))

 

[Flick Olmsford]

Thanks for all your help. As I am working with a new copy of the database,
I was able to delete all the records and start again. I think that the
problem might have been related to having the input mask in the table in one
of them, and trying to add the dashes later.

Anyway, I readded the records / tables to the new db and all worked well
Thank again