Appending specific data from a query to multiple tables.

Question

I have excel data that I imported into Access. The data has companies with multiple addresses, phone numbers, contact people and email addresses. I ran multiple queries which Duplicate Companies with multiple addresses, companies with sames addresses but different names, companies with multiple contact persons who have different phone numbers and email addresses and companies with one address, one phone number but multiple contact persons.

The company query has all of the data fields. What I would like to do is run an append query on company (which has one address, one phone number but multiple contact persons) appending company the company name to company which auto-generates an ID.  Also, set the value of CreatedBy to "System", date is defaulted to Date$() and IsActive to "Yes" on all appended tables Take that ID and address data and append to CompanyAddresses.

The same thing with the phone number and email address.

The contacts are a bit trickier because there three job classifications two fields that can contains different names, the same name or no name. So that data would have to be appended to the contacts tables as two entries with different job titles depending on which column the name was in, if the same name was in both columns the job title would have to be concatenated. There would be no entry in the contact phone or email tables. I have included the relationship to give you a better idea of what I am trying to accomplish.

Answer 1

> run an append query on company (which has one address

I stopped right there, because in your diagram I clearly see a one-to-many (1:M) relation. If you wanted 1:1, you would make CompanyAddresses.CompanyID the PK.

As a general rule of thumb, you write an append query for each table you want to add to.

Answer 2

Thank Tom,

Could not sleep last night and that is what I came up with. In addition to what you suggested a FOR ... EACH loop on CompanyName in AllCompanies picking up its PK from Company to add as FK of the other tables. I have not work it out yet but go something like this. Any suggestions would be greatly appreciated.

REB

Dim JobTiltle1 As Title!

Dim JobTitle1 As Title2

For Each COMPANY in AllCompanies

       INSERT INTO Company (Company)

       SELECT [AllCompanies].[Company]

       FROM [AllCompanies];

       INSERT INTO CompanyAddresses (Address, ID)

       SELECT [AllCompanies].[Address], Company.ID

       FROM [AllCompanies];

       INSERT INTO CompanyEmailAddr (EmailAddr, ID)

       SELECT [AllCompanies].[Email], Company.ID

       FROM [AllCompanies];

       INSERT INTO CompanyPhones (Number, ID)

       SELECT [AllCompanies].[Office Phone Number], Company.ID

       FROM [AllCompanies];

       INSERT INTO CompanyEmailAddr (EmailAddr, ID)

       SELECT [AllCompanies].[Email], Company.ID

       FROM [AllCompanies];

       IF AllCompanies.JobTitle1 <> "" THEN

           INSERT INTO Contacts (Contact, ID, JobTitle)

          SELECT [AllCompanies].[JobTitle1], Company.ID, Title1

          FROM [AllCompanies];

      END

       IF AllCompanies.JobTitle2 <> "" THEN

           INSERT INTO Contacts (Contact, ID, JobTitle)

          SELECT [AllCompanies].[Title2], Company.ID, Title2

          FROM [AllCompanies];

      END

Next AllCompanies

Answer 3

Tom,

I was thinking about replacing the code in one of the queries, I am sure that would work but I'm not sure how to write it in VBA.

REB

Answer 4

I've got it written pretty much as I laided it out earlier. I assigned a value to the two strings, added an IF and a couple ELSIF's. Now I just need to figure out how to call  the VBA. I read somewhere how to do that. Oh well back to the books.

Thanks again,

REB

Answer 5

sql = "insert ..."

Currentdb.Execute sql, dbFailOnError