In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?

Robert

They can be the same.

They can be the same name, but when using them in the same query, you have to explicitly use the table names. For example, if you have two customer tables and both include "Cust_ID" as a field, then you have to refer to them like this:

TableName1.Cust_ID
TableName2.Cust_ID

Otherwise, Access will tell you that "Cust_ID" is ambiguous; I.e., it can refer to more than one field.

If you are willing to type that table name when using code that references the common field, some authorities suggest not only that the fields forming the base of your relationship CAN have the same number, but they go farther and say they SHOULD have the same name. In the final analysis, it is a style issue. As long as you are consistent in your use of the name or names, you can go either way. But be aware that purists will strongly favor using the same names in both tables where possible.

Also as a matter of consistency, if it happens that you have two tables that are related and more than one of the field names MIGHT be the same in both tables, some purists would say that you don't allow that - as a confusion avoidance technique.

I prefer that the name of a FK be the same as the PK to which it points. This is always possible unless you have two (or more) references to the same PK from a single table. For example, in a junction table that maintains relationships between people, you might have a FromPersonID and a ToPersonID in a Parent-Child pair that both each point to a different PersonID in tblPerson.

I would also find acceptable a naming scheme that suffixed each FK with _FK. So you would have PersonID_FK but that still doesn't solve the problem that arises when you have two FKs to the same PK in a table. I find the suffix (or any similar concept) to be inferier because if for some reason, you removed a relationship after the app is built or added one, most people wouldn't bother to change the name of the FK field and so the suffix would loose its meaning.