Query similar data across two tables

[Jason]

Hi all,
I have two tables I'd like to compare a field from each even though the data
in the fields are not a 100% match.

Table 1:
Zip5

This would contain something like
25865
22365
etc

Table 2:
ZipPlus4

This table would be like this:
25865-5563
22365-8574
(Basically the same 5 digit zip as above plus the zip+4. May or may not have
a dash -)

If I do a simple Join I can view which rows are identical, but I can't
figure out how to compare what I've tried to show above.

Any help would be much appreciated.

 

[Jeff Boyce]

Jason

In your query, add a new field in that "calculates" the zip-only version.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Oops, hit <send> too soon...

After you create a new query that "calculates" the zip-only value, create
(yet) another query that uses the first query and joins to the other table
that has zip-only values. (you can use a query to join a table and a query)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jason]

Hi Jeff,
I'm not sure what you meant by "calculate" the zip-only value. What I ended
up doing was creating a query that pulls the first 5 digits of the zip code.
Then created another query where I joined the extracted zip table and the
old 5 digit table:

Query1:
SELECT PatDataLink.pat_number, Left(([patdatalink].[zip]),5) AS Expr1
FROM PatDataLink;

Query2:
SELECT patZipcodes.pat_number
FROM MSA_all INNER JOIN patZipcodes ON MSA_all.Zip = patZipcodes.Expr1;

A little longer than I wanted, but it works.

Thanks.

 

[Jeff Boyce]

Yes, that's what I was trying to describe.

Regards

Jeff Boyce
Microsoft Office/Access MVP