Problems with zip codes . . .

  • Thread starter Thread starter MrDangerous
  • Start date Start date
M

MrDangerous

The source of my problem I'm having is that several small towns in our area
have the same zip and two cities in my area (Savannah and Charleston) have
several zips, so neither the zip or the city name can be the primary index.
So, I gave them an autonumber primary key instead.

I have a table for work orders, and a table of job site addresses that
include a combo box that references another table named CityTimeMiledge with
the following fields: ID(autonumber), City(text), State(text), Zip(text),
Miledge(text), and Travel Time(text), with City as the bound column.
Everything works fine until I try to build a query using the table. The
query which searches out work orders by specific locations (which has a
combo box with a row source of SELECT[Site Locations].[Site Name], [Site
Locations].Address, [Site Locations].City, [Site Locations].[Phone Number]
FROM [Site Locations]; with [Site Name] as the bound column) will have
multiple entries for every site in Savannah, one for every zip code in
Savannah. If I change the bound column for City to the ID, the site
locations combo box shows the ID number in the address, not the city, and
the queries come up blank.

The quick and dirty fix is changing the city name for each zip code into
something unique, like Savannah West side, Savannah Midtown, etc., but this
screws up mailing addresses based on the Site Locations table. What's the
best way to fix this?
 
In the combo box, set the Row Source to a query that will contain the ID
field and the City Name field, probably sorted on the city name field
(personal preferance). The field corrospond left-to-right to columns in the
combo box. If the query has the ID first, set the Bound Column to 1, this
will cause the ID to be stored in the table when you make a selection. In
the Column Widths (Format Tab) set the widths to 0";1" adjusting the 2nd
width as needed to see the citys' names. The first visible column is the one
that will show in the combo box. Since we set the first column (the ID
column) to zero width then the city name column will be the one to show.
Doing this should automatically set Limit To List to Yes if you haven't
already done so.
 
MrDangerous said:
The source of my problem I'm having is that several small towns in our area
have the same zip and two cities in my area (Savannah and Charleston) have
several zips, so neither the zip or the city name can be the primary index.
So, I gave them an autonumber primary key instead.

I have a table for work orders, and a table of job site addresses that
include a combo box that references another table named CityTimeMiledge with
the following fields: ID(autonumber), City(text), State(text), Zip(text),
Miledge(text), and Travel Time(text), with City as the bound column.
Everything works fine until I try to build a query using the table. The
query which searches out work orders by specific locations (which has a
combo box with a row source of SELECT[Site Locations].[Site Name], [Site
Locations].Address, [Site Locations].City, [Site Locations].[Phone Number]
FROM [Site Locations]; with [Site Name] as the bound column) will have
multiple entries for every site in Savannah, one for every zip code in
Savannah. If I change the bound column for City to the ID, the site
locations combo box shows the ID number in the address, not the city, and
the queries come up blank.

The quick and dirty fix is changing the city name for each zip code into
something unique, like Savannah West side, Savannah Midtown, etc., but this
screws up mailing addresses based on the Site Locations table. What's the
best way to fix this?
Click to expand...

This is one of the reasons that ZIP+4 was introduced. If your job site has
it's full zip code (or event the neighbors if it's new) you should be able
to get what you want.
 
Worked like a charm, thanks.


Wayne Morgan said:
In the combo box, set the Row Source to a query that will contain the ID
field and the City Name field, probably sorted on the city name field
(personal preferance). The field corrospond left-to-right to columns in the
combo box. If the query has the ID first, set the Bound Column to 1, this
will cause the ID to be stored in the table when you make a selection. In
the Column Widths (Format Tab) set the widths to 0";1" adjusting the 2nd
width as needed to see the citys' names. The first visible column is the one
that will show in the combo box. Since we set the first column (the ID
column) to zero width then the city name column will be the one to show.
Doing this should automatically set Limit To List to Yes if you haven't
already done so.

--
Wayne Morgan
MS Access MVP


MrDangerous said:
The source of my problem I'm having is that several small towns in our area
have the same zip and two cities in my area (Savannah and Charleston) have
several zips, so neither the zip or the city name can be the primary index.
So, I gave them an autonumber primary key instead.

I have a table for work orders, and a table of job site addresses that
include a combo box that references another table named CityTimeMiledge with
the following fields: ID(autonumber), City(text), State(text), Zip(text),
Miledge(text), and Travel Time(text), with City as the bound column.
Everything works fine until I try to build a query using the table. The
query which searches out work orders by specific locations (which has a
combo box with a row source of SELECT[Site Locations].[Site Name], [Site
Locations].Address, [Site Locations].City, [Site Locations].[Phone Number]
FROM [Site Locations]; with [Site Name] as the bound column) will have
multiple entries for every site in Savannah, one for every zip code in
Savannah. If I change the bound column for City to the ID, the site
locations combo box shows the ID number in the address, not the city, and
the queries come up blank.

The quick and dirty fix is changing the city name for each zip code into
something unique, like Savannah West side, Savannah Midtown, etc., but this
screws up mailing addresses based on the Site Locations table. What's the
best way to fix this?
Click to expand...
Click to expand...
 
Back
Top