Hi,

Can anyone tell me how to create one-to-one relationships within access 2000/2003? By default, it creates one-to-many..

Thanks for help

Why would you want a one-to-one relationship? I ask because they are very uncommon.

I am creating a general admin db for a charity. They have volunteers, so will need to store their personal details. However, the public (who are not volunteers) make one-off donations and the address is taken. I have a separate address table. So one volunteer will only have one address, one donator will only have one address. The reason why I have a separate address table is because I have 2 diff entities using the same type if info.

I may be wrong with this approach....any suggestions would be welcome :o

Ah, ok.

In the Relationships you just drag from one field to another. Don't set the Referential Integrity. I don't know why it is coming up as a one-to-many as default.

Thanks - it seems to work on MS Access 200 - will give it a go on 2003 tonight :cool:

Don't set the Referential Integrity I disagree. It is ALWAYS better to let the database engine enforce RI rather than leave it to program control or worse, not bother at all. So my suggestion is to always set RI on relationships.

The cardinality of a relationship is determined by Access. It works out the relationship based on which fields are primary key fields and which fields are foreign keys.

In a 1-1 relationship, the primary key of tblA is related to the primary key of tblB. One of the tables will need to be the "parent" in the relationship. If you have cascade delete selected (and you should for this type of relationship) and you delete the "parent" record, the related record in the other table will also be deleted. Usually you have an autonumber primary key and so the table with the autonumber field naturally becomes the "parent" so make sure that is how you set it up. The related table pk will be defined as long integer.

In a 1-many relationship, the primary key of tblA is related to either a non-key field of tblB or to one field of a multi-field pk in tblB.

A many-to-many relationship is actually implemented as two 1-many relationships using an intermediate relation table.

Sounds to me like you have a design flaw. You shouldn't have separate tables for donors and volunteers. Volunteers should be flagged as such. But, it is certainly logical to have a volunteer be a donor also. So not only don't you need separate tables for volunteers and donors, unless you need to maintain multiple addresses for a single individual or share addresses for multiple individuals, you shouldn't have a separate address table either.