Spilt table for one cmpany with many contacts

[alsimbo]

I have developed a database and used a standard MS template for customer details as part of the development. The issue is that the standard template is set out for one contact for one company. I have now determined that there is the possibility for multiple contacts (and even multiple sites) for each company. I have setup all sorts of relationships with other tables and want to know if there is a way of splitting the customer table into at least 2 tables, one for the company details an the other for the multiple contacts whilst retaining the appropriate relationships with the other tables?



I did try creating a contact table and using the options for updating all relationships when I changed the source rows for the data i.e. contact name fom the new contact table and not from the customer table etc. This had limited success and I eventually gave up when I was 'going round in circles!'. I

have some understanding of Access and have developed a few internal databases, but progamming etc. is not a strong point


I've scrapped the above idea and am back at square one - any guidance would be welcome, thanks

[June7]

To split this table, assuming it does not yet have duplicate companies and the primary key of the table is an autonumber field, create a new number type field. Copy the autonumber data to this new field. Can use copy/paste of the column or an UPDATE query. Now copy the table with a new name of Contacts. Remove fields not needed in each table. Remove the number field from the original table (Companies), the autonumber field can stay in the new Contacts table.

[alsimbo]

Thanks, done that.

But, because of the previous relationships with other tables, queries and reports, I am asked for the missing 'parameter values' from the customer table where the fields have been deleted i.e. the contact name

This is the same as the issue I had yesterday - I am looking to be able to automatically update all queries, reports etc. that access those fields

[June7]

Sorry, can't automate, that will require manual design edits.