Append and Update Query Question

[Guest]

I have a table 1 with fields Zip Code, City, State, Consultant and SVC Office
and an Updated Zip Code Table 2 which has fields Zip, City, ST and County.

I have added the field County to table 1 as I want that new column.

I created an update query and brought down State, City, County and Zip Code
as fields from table 1. I joined table to tbale 1 by ZIP with arrow from 2
to 1. I also placed the 4 field from table 2 in the update to fields.

The query runs and almost works but I am getting duplicate ZIp Codes. How
do restrict the table to have no duplicate zip codes. Neither table has a
primary key.

 

[Guest]

I believe I solved my problem. I just set Table 1 indexing to no duplicates
for Zip Code field. My numbers of zip codes dropped from roughly 45,000 to
43,000 and I did not spot any duplicates.

 

[Randy]

I believe I solved my problem. I just set Table 1 indexing to no
duplicates
for Zip Code field. My numbers of zip codes dropped from roughly 45,000
to
43,000 and I did not spot any duplicates.

Brian, Probably not. As I see Table1 is a history/transaction table, and
(except for the County field) it should be left intact, since it tracks all
the visits for the consultants. By indexing to no-duplicates on field Zip
Code, you may have deleted visits to different SVC offices which are within
the same area or Zip Code. Also you may have dropped different-time visits
to the same SVC office.

If you were just populating the new field "County" on Table1, based on the
contents of Table 2, all you need it to do was to create an update query on
Table 1, field County, using a simple join (with no-arrow) between the two
tables on field Zip Code.

-Randy

 

[Guest]

I think I am OK as this ZIP Code Table only stores the ZIP Codes and other
tables deal with anything requiring more than one visit to any zip code.

We just use the ZIP Code Table to pull up the person by a query prompt
assigned to a particular zip code.