Adding new record to existing table

[savarin]

I have a customers table that is already populated with over 1000 names. Now I need to create a "Region" field and go back and assign each of the existing customers a region. Is there an easy way to do this?

 

[nealvenables]

If your customer data already has location data (i.e. City) and this can be readily referenced against a region, then yes.

Supposing...

tblCUSTOMERS

CustID CustName CustTown
1 Jane Smith London
2 John Smith Bristol
3 Fred Bloggs Nottingham
4 Freda Bloggs London
5 Jane Doe Bristol

The following table can be created

tblREGIONS

Town Region
London South-East
Bristol South-West
Nottingham Central

Then...

1. Add column CustRegion to tblCUSTOMERS.

2. Execute the following SQL

UPDATE tblCUSTOMERS
SET tblCUSTOMERS.CustRegion = tblREGIONS.Region
FROM tblCUSTOMERS, tblREGIONS
WHERE (tblCustomers.CustTown = tblREGIONS.Town)