Access Queries Look Up

[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

 

[John W. Vinson]

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

If the Zip field in Table2 in fact contains both zipcodes and a hyphen as a
text string, this will require some hassle: fields should be atomic, having
only one value (say two fields ZipFrom and ZipTo).

This will be really inefficient, but you could use a query like

SELECT TableII.[State Designation]
FROM TableI, TableII
WHERE TableI.[Zip Code] >= Left([TableII].[Zip Code]
AND TableI.[ZipCode] <= Right([TableII].[Zip Code];

Since you'll be using functions to decompose the composite field, and since
you won't be able to use any indexes, this query won't be very quick - but for
a 50-row table it won't be *too* bad.

Having TableII structured as

StateCode <e.g. CA; primary key, text 2>
ZipLow <e.g. 90000, text 5, indexed unique>
ZipHigh <e.g. 94999, text 5, indexed unique> <or is 95000 a CA zip?>

you could use a much more efficient

SELECT TableII.Zip
FROM TableI INNER JOIN TableII
ON Table1.Zip >= Table2.ZipLow AND Table1.Zip <= Table2.ZipHigh;


John W. Vinson [MVP]

 

[John Spencer]

Could you redesign your table so that the start code is in one field and
the end code in a second? That would make life much simpler.


If for some reason you cannot change your field structure, you can try
something like:

SELECT T.[Zip Code], S.State
FROM [Table 1] as T INNER JOIN [Table 2] as S
ON T.[Zip Code] >= Left(S.[Zip],5) AND
T.[Zip Code] <= Right(S.[Zip],5)

That query cannot be built in the query grid, but must be built in the
SQL window.

You can start in the query grid by building a query with the two tables
and joining table one ZIP Code to Table two's zip code. Then switch to
SQL view (View: SQL) and editing the join line so it looks similar to
the above.

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

 

[Guest]

Thanks for your help. Actually the zip ranges are in sperate fields. The beg.
zip, end zip and state code each are in a separate field. Does this change
your suggestion?

Could you redesign your table so that the start code is in one field and
the end code in a second? That would make life much simpler.


If for some reason you cannot change your field structure, you can try
something like:

SELECT T.[Zip Code], S.State
FROM [Table 1] as T INNER JOIN [Table 2] as S
ON T.[Zip Code] >= Left(S.[Zip],5) AND
T.[Zip Code] <= Right(S.[Zip],5)

That query cannot be built in the query grid, but must be built in the
SQL window.

You can start in the query grid by building a query with the two tables
and joining table one ZIP Code to Table two's zip code. Then switch to
SQL view (View: SQL) and editing the join line so it looks similar to
the above.

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

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

 

[Guest]

Thanks for your help.

Actually the zip ranges are in separate fields. The beg. zip, end zip and
state code each are in a separate field. Does this change your suggestion?

Could Excel be used to perform this task?

Could you redesign your table so that the start code is in one field and
the end code in a second? That would make life much simpler.


If for some reason you cannot change your field structure, you can try
something like:

SELECT T.[Zip Code], S.State
FROM [Table 1] as T INNER JOIN [Table 2] as S
ON T.[Zip Code] >= Left(S.[Zip],5) AND
T.[Zip Code] <= Right(S.[Zip],5)

That query cannot be built in the query grid, but must be built in the
SQL window.

You can start in the query grid by building a query with the two tables
and joining table one ZIP Code to Table two's zip code. Then switch to
SQL view (View: SQL) and editing the join line so it looks similar to
the above.

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

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

 

[John Spencer]

That makes life simpler. You can then drop the need to parse out the
beginning and ending values.
The SQL becomes something like the following.

SELECT T.[Zip Code], S.State
FROM [Table 1] as T INNER JOIN [Table 2] as S
ON T.[Zip Code] >= S.[Zip] AND
T.[Zip Code] <= S.[Zip]


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

Thanks for your help.

Actually the zip ranges are in separate fields. The beg. zip, end zip and
state code each are in a separate field. Does this change your suggestion?

Could Excel be used to perform this task?

Could you redesign your table so that the start code is in one field and
the end code in a second? That would make life much simpler.


If for some reason you cannot change your field structure, you can try
something like:

SELECT T.[Zip Code], S.State
FROM [Table 1] as T INNER JOIN [Table 2] as S
ON T.[Zip Code] >= Left(S.[Zip],5) AND
T.[Zip Code] <= Right(S.[Zip],5)

That query cannot be built in the query grid, but must be built in the
SQL window.

You can start in the query grid by building a query with the two tables
and joining table one ZIP Code to Table two's zip code. Then switch to
SQL view (View: SQL) and editing the join line so it looks similar to
the above.

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

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