Link Question

[Rose]

I have a list of social security numbers within a table. I want to be able
to link them to another table that has a social security range and the state
in which they are from.

First Table (1 Field)
Social Security Number
011-123-4567

Second Table (2 Fields)
First three Digits State
001-003 New Hampshire
004-007 Maine

How can I create a link that will tell me of the 1000 plus social security
numbers I have who is from what state? Any ideas?

 

[Ken Snell \(MVP\)]

Your second table obviously is not a normalized data set, so my first
inclination is to try a non-equi-join between the two tables:

SELECT [First Table].[Social Security Number],
[Second Table].[State]
FROM [First Table] INNER JOIN
[Second Table] ON
Left([First Table].[Social Security Number], 3) >=
Left([Second Table].[First three digits], 3) AND
Left([First Table].[Social Security Number], 3) <=
Right([Second Table].[First three digits], 3);