Creating a one to one relationship

[xian2]

Hi,
I have a 'Client table' and a 'Credit Card table' that need to be seperated for security reasons but I am not sure how you set up the one to one relationship. I currently have the two Primary Key fields ('Client ID' and 'Cardnumber') linked together and this appears as a one-to-one relationship in the relationships table. However, I am not sure if this is correct as the cardnumber field will not let me change the properties e.g. input mask and data type for a credit card number and defaults to those of the clientID.

Am I doing something wrong? More details including field names can be included in necessary.

Many thanks in advance for any help

Edd

[NeoPa]

You would need a ClientID field (or equivalent) in both tables.
It would be PK in the 'Client Table' and FK in the 'Credit Card Table'.

[MMcCarthy]

Edd

As NeoPa says the tables should be as follows:

tblClients
ClientID (Primary Key)
ClientName

tblCreditCards
CardNumber (Primary Key)
ClientID (Foreign key referencing the primary key of tblClients)

The relationship between these two tables is based on the ClientID. Now you need to do two things.

1. In the tblCreditCards table, set the indexed property of the ClientID to Yes (No Duplicates). This will enforce a one to one relationship
2. click on the relationship in the relationship and tick the referential integrity and cascade boxes.

Mary

[xian2]

Many thank to both NeoPa and Mary for your great advice.

You have been really helpful in the creation of my database and I will undoubtedly return for some more assistance - if you can put up with me that is!

All help is very much appreciated and this is a truly wonderful service.

Best wishes and I hope to be able to help people also in the near future.

Edd

[MMcCarthy]

You're welcome.

Mary

[NeoPa]

Many thank to both NeoPa and Mary for your great advice.

You have been really helpful in the creation of my database and I will undoubtedly return for some more assistance - if you can put up with me that is!

All help is very much appreciated and this is a truly wonderful service.

Best wishes and I hope to be able to help people also in the near future.

Edd

That's a great attitude.
We can never have too many experts.