Updating more than one table (97)

Topic

New Reply

I have a table for customers, one for their site addresses and one for their contact details at the relevant site. So these three tables are joined by one-to-many relationships (their may be more than one contact at a particular site address) using AutoNumber fields.
I have to import the relevant details from a flat-file Excel spreadsheet, and Append them to the existing tables.
I managed to add the customer details to the main table, but am struggling thereafter with ‘key violations’ and ‘cross-products’!!
Is it possible to update these tables in one query? How can I resolve the key violations? Thank you, Andy.

Reply | Quote

Replies

Hi,

You have to import data into a temp table. Then append the data into One-Side table first, later append to Many-Side tables.

Hope this helps.
Tim K.

Reply | Quote

Thanks. I can append the customer list to the customers table – it gives me key violations if the company already exists in the list, which I can ignore. I have then been able to append the addresses to the addresses table, and it pulls across the company id for the relevant company. However, it is at the third stage, when I want to append contact names to the contacts table that I get problems. I get a cross-product of the records – that is, if there are four addresses and six contacts it suggests that it will append 24 records!

How can I get rid of the cross-product and ensure that it will populate the contacts table with the correct AddressID numbers? Help!

Reply | Quote

What table are you appending the records to? Your addresses don’t belong in the contact table, especially if a contact may be associated with multiple addresses. The addresses belong in an address table. Then you can link a contact to a particular address using a join table, which will hold a key to the contact table and a key to the address table.

Reply | Quote

The Excel data is in a flat-file format. It includes the company name, an address and a contact on one row. The company name is then repeated for different addresses and different contacts.

I want to add all this data to the three relevant tables. If the company doesn’t exist then a new record is created for it.

Any one company may have several site addresses, and for any one site address there may be more than one contact.

Hope the above helps?!

Reply | Quote

First of all, would I be correct in assuming that your data divides nicely in to three tables:

Reply | Quote

Thanks. I’ve got it to work, in a roudabout way – although I still don’t fully understand what the problem is.

In Excel I have a list of companies together with several addresses and in turn several contacts. I was hoping that Access would be able to ‘recognise which company we are referring to, add the addresses to the addresses table (together with the correct companyID), and similarly add the contacts (and correct addressID) to the contacts table’ – in one or two steps!

It seems so simple when explained in English. Anyone willing to have a stab at trying to explain the problem?! Cheers, Andy.

Reply | Quote

You have a one to many join between Company and Contact and a one to many join between Company and Address. Can a contact be associated with only a single address? If so, you could add an address key to the Contact table. Alternatively, you can have a join table ContactAddress that holds a contactID and an AddressID for each contact-address combination. Can a company have a contact that is not associated with an address?

The way I do this is to import the spreadsheet into a flat Access table with an autonumber key in it so that I have a way to uniquely identify each row. Then I add fields for all the keys I’m going to be creating–in this case a CompanyID, a ContactID, an AddressID. I’d then start by populating the Company table with unique company names. Then I would update the flat table with companyIDs by linking the company name field in the flat table to the company name field in the Company table and updating the flat table’s companyID field. Next, I’d identify all those company names that were near duplicates and clean them up–Microsoft and Microsoft Corp, for example. Make them uniform and rerun the update so that all of the Microsoft entries would have one and only one CompanyID. Then leave the others in the Company table but have a field in there for Usable and set it to false for the “bad” company names. That will keep them from getting in again if someone has fat fingers.

Now you have flat records with a CompanyID in them, so you can now create Address and contact records. You already have the CompanyID, so all you need to do is append the relevant information, including the companyID to the other tables. You do *not* append the company name to the other tables, only the companyID, since that’s the relational link between the tables. Create the Address record first and then update the flat table with the AddressID just as you did the companyID. I personally wouldn’t bother to clean up the address records because believe me, they won’t stay cleaned up. Once you have Company and Address records created and their keys inserted, you can create the contact records and insert the Address key if you wish. If you decide to use a join table for Contact-Address (my preference), you can easily populate that table with the contact-address ID pairs from the flat table.

Hope this helps.

Reply | Quote

Thanks very much Charlotte. A join table has not been used, and you have described what I have been doing to get this to work.
Any one company will have several addresses. At any one address there will possibly be several contacts. The contacts could also be the same for several different addresses. ALSO, it is possible that several companies may give the same address, just to complicate things!
Could this set up work with a join table and why would it be preferable? Andy.

Reply | Quote

I wouldn’t try to hard to filter the addresses down because they tend not to be entered consistently. I worked for a direct marketing company for 3+ years and finally came to the conclusion that unique addresses were nearly impossible to manage so I stopped trying so hard. You might find it simpler to have a company field in the address table and just accept whatever address is entered for a company, creating a unique address ID for each record. You could use the companyID to filter out the appropriate addresses for a company and its contacts that way. It really depends on how much control you will have over the address records. I found that users were more likely to enter addresses than they were to look them up to see if one already existed that was *almost* exactly the same.

If the addresses can be linked to multiple contacts and the contacts can be associated with multiple addresses, you *have* to use a join table because it’s the only way to create a many-to-many join. With a join table, you can connect each contact to whatever addresses are appropriate. If a single address has multiple contacts, you’ll have as many records as you need. If a single contact is associated with multiple addresses, no problem. There simply isn’t any other way to do it in Access.

Reply | Quote

Although some companies may share the same address, this is more of a coincidence than anything central to the design of the database. Any one contact is, in general, based at a particular address for a particular company. Although in theory the same contact may occur for different addresses and/or companies the likelihood of this is extremely rare.

Therefore, I am still unconvinced of the need for a join table. What problems are likely to occur if I don’t use a join table? Andy.

Reply | Quote