How to use append query to update data in three tables?

[Garindan]

Hi all, I have a customer database and I've imported 36000 new customers from an excel file into a new table, called tblLeads.

I have changed the field names and types etc in this new table to match the field names already created in the existing database. The table looks like this:-

tblLeads:-
ID (PK just so it has one)
TitleID
FirstName_Initial
Surname
House_FlatNumber
Street
Town_CityID
StatusID
Postcode
HomePhoneNumber

So these are all the new customers in the new table.

I'm sure this is easy but I'm stuck...
My existing tables are like this...

tblCustomers:-
CustomerNumber (PK)
TitleID
FirstName_Initial
Surname
House_FlatNumber
Street
Town_CityID
StatusID

Then...

tblCustomerPostCode:-
CustomerNumber
Postcode

and..

tblHomePhoneNumber:-
CustomerNumber
HomePhoneNumber

So how can I append the data to my existing tables since the matching phone number and postcode has to go into a separate table? I'm a bit stuck, many thanks for any help!!!

 

[MarkK]

There's no good reason--that I can see--for the PostCode and HomePhoneNumber to be in separate tables.

 

[Thales750]

There's no good reason--that I can see--for the PostCode and HomePhoneNumber to be in separate tables.

I alway put Phone numbers, addresses and email in seperate tables, that way you are not limited to a set number of items.

 

[Garindan]

Yes they're in separate tables because we don't have post codes and home numbers recorded for all our customers.

So any ideas? Many thanks.

 

[Thales750]

Yes they're in separate tables because we don't have post codes and home numbers recorded for all our customers.

So any ideas? Many thanks.

It will take several queries

1. Append the data to a new customer table. This will give you a new primary Key

2. Append only the phone numbers and the primary key to the foriegn key in the phone table

3, repete 2 for addresses.

 

[Garindan]

Sorry but I don't follow? Could you explain more? Cheers

 

[Thales750]

Make sure all tables exist and they must contain the following fields:

They all must have a primary key and “tblCustomerPostCode” and “tblHomephoneNumber” must have a foreign key

tblCustomers:
CustomersID (AutoNumber)


tblCustomerPostCode
CustomerPostCodeID (AutoNumber)
CustomersID (Number. Long Integer)


tblHomephoneNumber
HomephoneNumberID (AutoNumber)
CustomersID (Number. Long Integer)


Create an append query using the table tblLeads

1. Append it to tblCustomers using all of the fields that you want to be populated. The ID field from tblLeads should refer to the CustomerID field in tblCustomers.

2. Append to tblCustomerPostCode the ID field should refer to CustomerID

3. Repete for tblHomephoneNumber the ID field should refer to CustomerID

I agree with lagbolt, I can see no reason why you would separate the Address from the post code. Even if you wanted to have the option of multiple addresses for each customer you would still want the post code to be in the same table as the address. In which case I would store all of the addresses in a separate table with the post code.

 

[Garindan]

It's ok, thanks for the help but I worked it out myself.

I added two fields to the existing customer table, 'postcode' and 'homephone'., which meant i could just append all the records as they were and each customer would get a primary key in my original table.

Then I created two append queries from tblCustomers to tblCustomerPostcode and tblHomePhoneNumbers to add those in. Then deleted the temporary fields from tblCustomers.

I agree with lagbolt, I can see no reason why you would separate the Address from the post code. Even if you wanted to have the option of multiple addresses for each customer you would still want the post code to be in the same table as the address. In which case I would store all of the addresses in a separate table with the post code.

As I said before, I had a separate table for Postcodes as of the initial 6000 customers in the database only a few actually had postcodes. So the data was separated to prevent erroneous data.

Ok all done now, many thanks for taking the time to reply

 

[MarkK]

To me, if a customer doesn't have a postcode, simply leave the field blank. Maybe if you wanted multiple postcodes or mutiple homephones for a customer it would make sense, but the names of those things make it seem unlikely it's really a one-to-many relationship.
So if you remove that complication it makes the task you are posting about much easier and you hit two birds with one ... idea.