Form to choose a duplicate value

I am creating a Zip Code search tool. I have a Table with every zip
code and its corresponding city and state. I have also created an
input table that allows the user to input cities and states. These two
tables are connected by a query that looks up the city and state in
the zip code table and pulls back the matching zip code. The problem
is that many cities have multiple zip codes. Is there a way that I can
have the db recognize the cities that have duplicates and pull up a
separate form listing all the duplicates. The user could then select
the zip code they want to use and then return only that value to a
final output query or table with the rest of the other cities listed.
Any ideas would be appreciated.
Thanks,
Shawn Yates

Re: Form to choose a duplicate value

On Oct 22, 1:18 pm, Shawn Yates wrote:
> I am creating a Zip Code search tool. I have a Table with every zip
> code and its corresponding city and state. I have also created an
> input table that allows the user to input cities and states. These two
> tables are connected by a query that looks up the city and state in
> the zip code table and pulls back the matching zip code. The problem
> is that many cities have multiple zip codes. Is there a way that I can
> have the db recognize the cities that have duplicates and pull up a
> separate form listing all the duplicates. The user could then select
> the zip code they want to use and then return only that value to a
> final output query or table with the rest of the other cities listed.
> Any ideas would be appreciated.
> Thanks,
> Shawn Yates

Use a form with a combo box to select the state, another to select the
city, and in the click event of the combo boxes, put code to populate
a listbox with possible zipcodes. Both combo boxes should call the
same function which only works when both combo boxes have values in
them. Clicking on the listbox should then select the zipcode. How
will they know which zipcode is the correct one?

Re: Form to choose a duplicate value

On Mon, 22 Oct 2007 18:18:34 -0000, Shawn Yates wrote:

>I am creating a Zip Code search tool. I have a Table with every zip
>code and its corresponding city and state. I have also created an
>input table that allows the user to input cities and states. These two
>tables are connected by a query that looks up the city and state in
>the zip code table and pulls back the matching zip code. The problem
>is that many cities have multiple zip codes. Is there a way that I can
>have the db recognize the cities that have duplicates and pull up a
>separate form listing all the duplicates. The user could then select
>the zip code they want to use and then return only that value to a
>final output query or table with the rest of the other cities listed.
>Any ideas would be appreciated.
>Thanks,
>Shawn Yates

Don't enter City and State then lookup Zip code
Do enter Zip code then lookup City and State

In a table with people and address you don't want to enter the same
city and state for a hundred different people. You would set up a table with
fields for zip code, city, and state. You would make the zip code a Primary
Key in that table. Then in the 'main table' you would enter only the zip code,
which is called a Foreign Key. The two tables are "related" Primary Key to
Foreign Key. When you make a report, Access uses the Foreign Key in the 'main
table' to 'look up' the related city and state. Since there is only one entry
for each city and state, it can not be misspelled in the data entry process.

Zip codes are not the best example because one city can have more than one zip
code, but it is easy to visualize. Actually a zip code only denotes the Post
Office for the area where the individuals' mail goes through. It is possible
for several small towns to have a zip code with the same first five numbers,
but when that happens, the last four numbers in the 5+4 zip code nomenclature
will be different. This situation can also happen within one city if it is
very densely populated. The last four numbers denoting an area that the post
office services.

Chuck
--