need help on record changing

[JohnE]

Hello. I have a situation in which the higher ups are restructuring
territories/areas based on zip codes. Which now will require the changing
the designated area of 16,000 records. There are 3 tables that are involved;

tblArea - has the updated restructuring

tblCustomer - has the customer info including the zip code and the tblArea
primary key (AreaPK) as a foreign key

tblAreaZipCode - has the listing of the zip codes for the different areas
and the tblArea (AreaPK) primary key is a foreign key

I need to change the AreaPK (int) in the tblCustomer table to match the one
located in the tblAreaZipCode for the associated zip code. I am at a loss as
to how to do a mass undertaking such as this and ask for help from the group.
I'm sure this can be done thru some type of coding or scripting that will
automate the entire process. I suspect that there will be zip codes in the
customer table that may not be in the tblAreaZipCode.

If anyone has had to do something similar and is willing to help out and get
me started in the right direction, it is appreciated.

Thanks in advance to anyone who responds.

.... John

 

[Baz]

One crucial piece of information missing from your description is what you
want to do about customer zip codes which are not in the tblAreaZipCode
table. My guess is that you will probably want to fix them. A simple
unmatched query should find them for you, there is a wizard to help you
build such a query if you don't know how.

Assuming that you fix the zip codes in the customer table so that they all
match a zip code in the tblAreaZipCode table, then all you need to do to the
Area foreign key on the customers table is to delete it! It is now
redundant! The relationship between the customer's zip code and the
tblAreaZipCode table tells you exactly what area each customer is in, you
don't need to redundantly store the area on the customer table.

Seriously, though, I wouldn't actually delete the field, because you can
guarantee that as soon as you do so some clever dick will decide to go back
to the old area codes. I would simply delete the field's relationships and
rename it, so that you have it there as a historical record and in case you
need to revert to it.

 

[JohnE]

Thanks for the advice. However, that didn't really help out. As mentioned,
what and how do I make the changes to the 16,000 records?

 

[John W. Vinson]

Hello. I have a situation in which the higher ups are restructuring
territories/areas based on zip codes. Which now will require the changing
the designated area of 16,000 records. There are 3 tables that are involved;

tblArea - has the updated restructuring

tblCustomer - has the customer info including the zip code and the tblArea
primary key (AreaPK) as a foreign key

tblAreaZipCode - has the listing of the zip codes for the different areas
and the tblArea (AreaPK) primary key is a foreign key

I need to change the AreaPK (int) in the tblCustomer table to match the one
located in the tblAreaZipCode for the associated zip code. I am at a loss as
to how to do a mass undertaking such as this and ask for help from the group.
I'm sure this can be done thru some type of coding or scripting that will
automate the entire process. I suspect that there will be zip codes in the
customer table that may not be in the tblAreaZipCode.

If anyone has had to do something similar and is willing to help out and get
me started in the right direction, it is appreciated.

With the caveats that Baz brought up... a very simple Update query should do
most or all of the work for you. BACK UP YOUR DATABASE first just in case!

Create a Query joining tblCustomer to tblAreaZipCode, joining on Zip. (You
should really first use the Unmatched Query Wizard to find all the zip codes
in tblCustomer which do NOT have a match in tblAreaZipCode, and either fix
these or set those records' AreaPK to a value indicating "unknown area").

That done, change the query to an Update query using the Query menu option, or
the query type drop-down tool in the query design toolbar. You'll see a new
line in the query grid, UpdateTo.

Update tblCustomer.AreaPK to

[tblAreaZipCode].[AreaPK]

The brackets are essential (otherwise it will try to update the AreaPK to the
text string "tblAreaZipCode.AreaPK" which certainly won't help!!!)

I will agree with Baz, though: if you have the correspondance from zip code to
area stored once in tblAreaZipCode, then the AreaPK should *simply not exist*
in tblCustomer. If it does, you're storing the same information in two
different places - and it will be all too easy to introduce errors! What if a
customer informs you that her zip code is entered incorrectly, and you chage
it? She might now be in a different area, but you won't detect that fact
without rerunning the update query or manually checking.

John W. Vinson [MVP]

 

[Baz]

I repeat, you don't need to. The area field on the customers table is
redundant. Given a customer's zip code, you can find out their area by
looking it up in the tblAreaZipCode table. You have eliminated the need to
store the area against each customer.

In other words, on the customer table, instead of having area as a foreign
key related to tblAreas, you now have zip code as a foreign key related to
tblAreaZipCode.