One to One relationship - How? (1 Viewer)

[Carl Foster]

Hi

I have the following tables in a database:

1) Customers
2) Employees
3) Suppliers
4) Contacts

The contacts table contains all the names and address info while the other tables contain specific info related to customers, employees and suppliers. I want to link each of those tables to the contacts table.

This is confusing me and it seems the only way is to have a one to one relationship. Firstly is this right and if so how do I set up a one to one relationship as the relationship window only seems to allow one to many?

Thanks for your help

 

[KeithG]

to make a 1 to 1 both fields need to be primary keys.

 

[Pat Hartman]

The contacts table would be the "parent" in the relationship and would normally have an autonumber as its primary key. The other relationships are "children" and their pk should be defined as Long Integer. your application needs to populate the pk in the appropriate "child" table after the "parent" is inserted.

 

[workmad3]

Both fields don't need to be primary keys, but they do both need to have their index set to 'yes, no duplicates' to make them unique, otherwise the structure allows for a one to many relationship

 

[dsigner]

The contacts table is going to be the main table with three extension tables for each of the customers etc. This will work OK but I suspect that in using this data users will work with one group at a time so most of the time it is going to be better to include the contact data in each of the other files. Make sure that the field structure for the contact information is identical then on the rare occasions when you want to mail all three you can do three batches from one command button.