Foreign Key Fields

[mwfelix]

I have a table with Client info, I then have child tables with info on what workshops the client has attended or if the client recieved services. The idea is to use "ClientName" as the primary key in the main "Client Info" Table and then use this same field as the Foreign key in the smaller child tables. Everything seems to work out except if I want to "Enfore Referential Integrity" in the relationship. When I try to do that I get this error message:

Relationships must be on the same number of fields with the same data types

Is this going to screw me up later?

Thanks
mark

 

[RoyVidar]

For relationship, the field must have the same datatype and field size, that's probably what's giving you the errormessage (i e a text field must be the same size in both tables).

Is this going to "screw you up later" - well, I don't know, but in "my world" I can have different clients having the same name, which in my eyes makes the field unsuited as primary key. I often find that tables containing such information (clients, contacts, vendors, customers) lack a candidate key. Candidate key being one field or a combination of fields that will uniquely identify each record. A composite primary key consisting of more than two fields, can be a pain in the butt, in parent tables linking to child tables, so in such case, I often end up using some numbering shceme (Autonumber is one possibility, or use some kind of custom numbering). Here's a link with some usefull tips on table creation, choosing primary keys etc http://www.microsoft-accesssolutions.co.uk/tables.htm

 

[Chunk]

For relationship, the field must have the same datatype and field size, that's probably what's giving you the errormessage (i e a text field must be the same size in both tables).

Is this going to "screw you up later" - well, I don't know, but in "my world" I can have different clients having the same name, which in my eyes makes the field unsuited as primary key. I often find that tables containing such information (clients, contacts, vendors, customers) lack a candidate key. Candidate key being one field or a combination of fields that will uniquely identify each record. A composite primary key consisting of more than two fields, can be a pain in the butt, in parent tables linking to child tables, so in such case, I often end up using some numbering shceme (Autonumber is one possibility, or use some kind of custom numbering). Here's a link with some usefull tips on table creation, choosing primary keys etc http://www.microsoft-accesssolutions.co.uk/tables.htm

I agree with this comment. In short, how about just using the attribute "ClientID", which is a number unique to each client?