Help creating query in Access or should I be using a formula in Ex

[Guest]

I have two tables, table I contain addresses complete with five (5) digit zip
codes. Table II contain a range of five (5) digit zip codes with a state
designation. I’m tying to create a query in Access that will look up the zip
codes from table I and assign the corresponding state designation from table
II.

Here is an example of the tables


Table I Table I I

Zip Code Zip
Code state designation
45455 40000 – 46000 TN
93725 90000 – 95000 CA
22618 20000 – 25000 NY


The query results that I would like should appear as the following:

45455 TN
93725 CA
22618 NY

 

[Steve]

You could make things a lot easier by modifying Table II and having
ZipcodeStartOfRange and ZipcodeEndOfRange fields. So for TN,
ZipcodeStartOfRange would be 40000 and ZipcodeEndOfRange would be 46000. You
could then use the DLookup function in your query to find the state:

State = DLookup("[state designation]","[Table II]","[Zipcode] Between
ZipcodeStartOfRange And ZipcodeEndOfRange")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

I have two tables, table I contain addresses complete with five (5) digit zip
codes.

Patience, please. You received two answers within half an hour after posting.
A few hours to a day is not unreasonable (for a free, volunteer-staffed peer
support forum). Reposting after twenty minutes borders on rudeness.


John W. Vinson [MVP]

 

[Guest]

Sorry, I didn't know the decorum. I'll be more patient.