Help with building update query with two tables

[Imran J Khan]

Hi,
I have a list of addresses, in five fields each, e.g. street address 1,
street address 2, city, postal code. An address can be repeated. I would like
to group these address and assign each unique address an ID#, which I would
like to link to a new table, containing the new ID#, the address itself, so
if need be, I can remove the address from the original table and just leave
the ID# behind. This way I will have two tables, the first one with the
address ID# only, and the new one with the ID# and the actual five address
fields.
How can I do this? I am trying to use an update query, although I have a
beginners level skill in VBA, so I could do this in a module. I just need
help getting started.
Thanks in advance.
Imran

 

[Ken Snell \(MVP\)]

Before doing anything, make a backup copy of the database.

First, create an empty table with six fields: one that is an autonumber
field (make it the primary key field), and five to hold the address
information.

Second, create and run an append query to copy the address information to
the new table:

INSERT INTO
NewTableName (AddressField1, AddressField2, AddressField3,
AddressField4, AddressField5)
SELECT DISTINCT OriginalTableName.AddressField1,
OriginalTableName.AddressField2, OriginalTableName.AddressField3,
OriginalTableName.AddressField4, OriginalTableName.AddressField5
FROM OriginalTableName;


Third, add a new field to the original table. Make it a Number data type,
with Field Size of Long Integer. This will hold the primary key value from
the new table. Call this field AddressID.

Fourth, use a query like this to update this new AddressID field with the
primary key:

UPDATE OriginalTableName
SET AddressID =
(SELECT NewTableName.AutonumberFieldName
FROM NewTableName
WHERE Nz(NewTableName.AddressField1, "NullValue") =
Nz(OriginalTableName.AddressField1, "NullValue") AND
Nz(NewTableName.AddressField2, "NullValue") =
Nz(OriginalTableName.AddressField2, "NullValue") AND
Nz(NewTableName.AddressField3, "NullValue") =
Nz(OriginalTableName.AddressField3, "NullValue") AND
Nz(NewTableName.AddressField4, "NullValue") =
Nz(OriginalTableName.AddressField4, "NullValue") AND
Nz(NewTableName.AddressField5, "NullValue") =
Nz(OriginalTableName.AddressField5, "NullValue"));


Fifth, verify that the update was done correctly.

Sixth, you'll need to change any other queries, forms, reports, etc. that
are using the original five address fields. You now will need to join to the
new table using the AddressID field and bring in the address fields from
that new table.

Seventh, you can delete the five address fields from the original table.

 

[Imran J Khan]

Wow! Thanks. I had managed to do most of what you suggested, but was stuck on
the null values. Your solution solved that problem as well. Fantastic.
Thank you.
Imran