Lookup Fields are evil? Or is this ok? Need a little guidance

[June7]

I agree about consistency in naming convention. I understand you were not indicating underscore was bad. But I did have issue with suggestion field names should be same. Actually first time I've ever heard anyone promote that.

[isladogs]

Whilst I can see the merits in using _fk / _pk suffixes, I agree with Pat's comments and never use them myself.

[Mark_]

One special note about using lookups in ACCESS; you will often want to base them on a query rather than the underlying table. This means you can create queries that return just the subset that is relevant in an easy to use manner.

In your particular case, you could create a query on truck number that has the Truck number's Primary Key, the displayed truck number, and the parent carrier's carrier name for use when you want to look up the truck number. This also means you can then sort the truck numbers by carrier to logically group them together. Very easy to do when defining a query but a little more difficult if you try to work it into the source for a combo box.

[Pat Hartman]

If you want to suffix with _pk and _fk, I don't object. I don't do it, but it is not wrong. CustomerID_PK and CustomerID_FK is not confusing. It is the subtle differences like customer vs customerID or cust or custID which I object to. That type of inconsistency just causes confusion. Keep in mind that in queries that join related tables, you don't select both the pk and the fk. You only select the fk so you should never end up with with two fields named CustomerID having to be selected in the same query. Selecting both or just selecting the PK are mistakes made by people who don't understand relationships.