Updating a table

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I have two tables. Both with the following fields.
Name
Address
City
Phone

One list (CityList) only has the name, address and city
info. There are 12,000 records in this list.

The other list (FirstList) has the name, address, city
and phone info. This has about 800 records.

I need to devise a way to get the phone number from
FirstList into CityList only on the records that match
names and addresses.

Does this make any sense? Any help is appreciated!
 
Dear Jennifer:

In the real world there can be more than one phone number for an
address. A person could have an ordinary phone, a fax phone, and a
cell phone, for example.

Rarely will someone end up on two tables with their name and address
type exactly. One will have a middle initial and the other not. One
will say St. and the other Street. Or maybe a typist will put an
extra space after the address number before the street name. All
these look virtually identical to a person, but not for the computer.

I suggest you find out more about the data using some query testing.

SELECT CL.name, FL.name, CL.address, FL.address,
CL.city, FL.city, FL.phone
FROM CityList C, FirstList F
WHERE CL.name = FL.Name
AND CL.address = FL.address
AND CL.city = FL.city

Do you get many matches? I'm guessing not. It may be up to you to
find some way of matching that works for the data you have. That
method would depend on the idiosyncracies of that data, and I can't
guess what that may involve.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top