Compare two tables using last four of SSN

[Just Me]

We use the last 4 of a customer's SSN as part of their sign in.
*
This is entered into a table (tblSign_In) that consists of these
fields: Last_Name, First_Name, and Last_Four. Last_Four is obviously
the last four digits of the customer's SSN.
*
We also have a table (tblCustomer_Complete) that lists (among other
things) the person's Last_Name, First_Name, and SSN (the complete
SSN).
*
I would like to be able to find (and display) all of the records in
tblCustomer_Complete that match the records in tblSign_In. The way we
would like to do this will mean somehow comparing the Last_Name and
Last_Four in the tblSign_In to the Last_Name and the last four digits
of the SSN in the table tblCustomer_Complete.
*
Everything I have tried so far has failed miserably and I haven't been
able to find a similar question in the archives. If anyone has any
serious suggestions that might help do this, I would be forever in
your debt.
*
Thank you so much in advance.

 

[Rick B]

I know you could do it in two queries.

Your first query would contain your tblCustomer_Complete and the fields
you'd like to pull. In a new column, create a field...

Last4 : Right([SSN],4)

Save that query.

Now creat a new query. For your data sources, pull the query we just
created and also your tblSign_In. Create a relationship between the two on
the Last_Four field in the table and the Last4 field from the query. Make
the relationship only pull records that match.

That should do it.

Rick B


We use the last 4 of a customer's SSN as part of their sign in.
*
This is entered into a table (tblSign_In) that consists of these
fields: Last_Name, First_Name, and Last_Four. Last_Four is obviously
the last four digits of the customer's SSN.
*
We also have a table (tblCustomer_Complete) that lists (among other
things) the person's Last_Name, First_Name, and SSN (the complete
SSN).
*
I would like to be able to find (and display) all of the records in
tblCustomer_Complete that match the records in tblSign_In. The way we
would like to do this will mean somehow comparing the Last_Name and
Last_Four in the tblSign_In to the Last_Name and the last four digits
of the SSN in the table tblCustomer_Complete.
*
Everything I have tried so far has failed miserably and I haven't been
able to find a similar question in the archives. If anyone has any
serious suggestions that might help do this, I would be forever in
your debt.
*
Thank you so much in advance.

 

[John Spencer (MVP)]

You would need a query that looks something like the following. You can't build
this query in the query grid, but you have to do this in the SQL window.

SELECT TblCustomer_Complete.*
FROM TblCustomer_Complete INNER JOIN tblSign_In
ON TblCustomer_Complete.Last_Name = tblSign_In.Last_Name
AND TblCustomer_Complete.SSN LIKE "*" & tblSign_In.Last_Four

You can also do it this way.

Add both tables to the query grid.
Link the two tables on Last_Name.

Add criteria to the field
Field: SSN
Table: tblCustomer_Complete
Criteria: LIKE "*" & [tblSign_In].[Last_Four]