Recently it occurred to me that maybe it's not enough to TELL people Lookups are almost always bad. There's a world famous page that offers eight significant problems with Lookups. My complaints are actually focused on the fallout from Number 6 on that list. Lookup fields in tables simply make it far too easy for inexperienced users to screw up royally.
Let's look at just one example, which is all too common. First I'll show how it should be done, if you really, really just have to do it yourself. The following discussion is based on a three table database of employees, departments, and employee assignments to departments. You can find the originals of these tables, btw, on GPCData.com
The first table, shown in design view and datasheet, is pretty straightforward, a Primary Key, a native key used by the company to identify departments, and the name of the department. No surprises here.
|Department Table in Design View|
|Department Table in Datasheet View|
In the next two screenshots, you see the employee table in both design view and datasheet view. Again, nothing out of the ordinary here.
|Employee Table in Design View|
|Employee Table in Datasheet View|
|Employee Department History Table in Design View.|
The first four fields in this table are really all that is needed. The last two fields, EmployeeIDLKP, and DepartmentIDLKP are only there to illustrate why Lookup fields in tables are One Bad Idea.
The primary key on this table is actually a compound key on EmployeeID, DepartmentID and FromDate. That allows us to designate a unique combination for each assignment. Combined with other rules, it keeps us from duplicating assignments inappropriately, while allowing overlapping assignments. That's all outside the scope of the current discussion though.
So, as you know. This design stores PKs from department and employee tables exactly as they appear in the parent tables with no ambiguity,
I added two lookup fields to illustrate how they work. These are, btw, designed so that the proper PKs are stored as FKs in the look up fields. The screenshot above shows the row source for the employeeID. It is the first column in the row source, which is also hidden. That means when we flip this table to design view, we'll see the employee name, not their PK. A win for the person who wants to see the name in a table.