Primary Keys

[Lucy02]

Hi guys

I was wondering if anyone would be able to help me? I have two tables which can be linked together as they both contain a field called Company Name. However, in one of the tables I want Company Name to become my primary key but this field contains duplicates. How can Isolve this so that I can still link my two tables together?

Thanks in advance

 

[statsman]

You can still link your tables but it gets a little confusing.
If you have Table1 and Table2 and they both contain the field names CompanyName, Contact and TelNo (for example) when you create your query (I assume you're using a query to connect them) you will get the following:
Table1:CompanyName
Table2:CompanyName
Table1:Contact
Table2:Contact
Table1:TelNo
Table2:TelNo

Having short term memory problems myself I usually will alter the field names slightly. Example:
In Table1 - CompanyName
In Table2 - CompName
In Table1 - ContactName
In Table2 - Contact
In Table1 - PhoneNo
In Table2 - TelNo

You would then set your relationship between CompanyName and CompName.

In this way you can tell which table is which when you try to "fine tune" your query.

Hope this helps.

 

[Pat Hartman]

Company name is a poor choice as a primary key since it may contain duplicates. If you cannot remove the duplicates, you must either use a different field or use an autonumber.

If you have control over the data, use an autonumber to define a company, and use that autonumber (defined as a long integer) as the foreign key in the related table. Do not use the name field.

statsman - it is better practice to use EXACTLY the same names for the primary key/foreign key pairs. That gives a visual clue that those are the fields on which two tables should be joined. With your method, you (or your successor) need to reference the relationship window to jog your memory.