Hi, I have a question related to a relationship between two tables, as listed:

**Table: Main**
id (PK)
contact1
contact2
*************

**Table: Contact**
id (PK)
surname
firstname
************

I would very much like to set up a relationship between these two tables which allows me to relate two different Contact-records to each Main-record. And, it has to be in a way that look upon the Main-table as the parent, and therefor allows me to automaticly delete the two contact-records when deleting the parent main-record.

If I set up the right indeces MS Access allows me to make a one-to-one relationship, but in the wrong direction, in other words: the contact-table has the parent role.

I hope this was understandable, and I'm happy for any response.

Several issues, first, you should be more descriptive in naming your PKs. This helps make your relations more obvious. Second, its not clear what your main table is. If its only to record contacts it doesn't make sense. assuming the Main table has more info then what you really have is a many to many relation. so your tables should look like this:

tblMain
MainID (PK Autonumber)
ContactID (FK)
other info relating to main

tblContacts
ContactID (PK Autonumber)
surname
firstname

tkxMaincontacts
MainContactID (PK Autonumber)
ContactID (FK)
MainID (FK)

Thank you for your answer. I guess I have to be more precise in describing the use of my tables.

I'm designing software where I use a Contact-table to store all information about different contacts in a project. This may be former owners of buildings, new owners, entrepeneurs, suppliers and so on. The table may contain information of both firms and persons, and there are an attribute (contactType) which is set if its a person or a firm. There are also an attribute which is set to describe the relationType (ex. formerOwner, mainContact, secondContact, supplier...)

My main goal is to keep all contact info in one table to make searches and queries easy, and of course to keep this kind of information in one place.

Further, to describe the need of a one-to-one relationship, I'll give one more example:

First, there is a project-table and the project has one former owner, which is stored in the contact-table:

tblProject
projectID (PK Autonumber)
contactID (FK)

tblContact
contactID (PK Autonumber)
contactType (person = 1, firm = 2)
relationType (formerOwner = 1, mainCostumerConctact = 2...)
surname (is used if person)
firstname (is used if person)
firmname (is used if firm)

Second, there is a costumer-table, and each project has one or more costumers. Each costumer has two main contacts (which are always there), and may also have more additional contacts.

tblCostumer
costumerID (PK Autonumber)
projectID (FK)
mainContactID (FK, unique index, null allowed)
secondContactID (FK, unique index, null allowed)

To keep track of the additional contacts I use the following table, and use a many-to-many relationship.:

tblCostumerContact
costumerContactID (PK Autonumber)
costumerID (FK)
contactID (FK)


The important thing here is, if a costumer is deleted in the database, all the related contacts needs to be deleted to. I would like the DB to take care of this. I can't get the costumer-table to act as the parent

Are you saying that a contact would be associated with ONLY one Costumer? its not possible for for a contact to be associated with multiple projects? That sounds unlikely.

I just don't see a one to one relation here. I'm also not clear on costumer (do you mean customer?). Are Costumers listed inthe Contacts table as well?

If a contact belongs to 1 and only 1 costumer then the CostumerID goes in the contact table. Then when you delete the Costumer, the contact will also be deleted (assuming you chose to enforce RI and cascade delete). You presently have the contactID in the Costumer table and that makes Contact the parent.

Thank you both. Your comments made me see things in a better way. I am pleased. And, I ment "customer"!