The lookup system

Why one table

A typical Microsoft Access database grows a pile of small lookup tables: status, category, assigned, and so on. Each combo on a form is wired to one of those tables. That works, but every new list means another table, another relationship, and another admin screen.

The Nifty Access lookup system keeps those lists in one table, tlkpInfo. Each list is a group inside that table — an imaginary table, if you like, designated by the value in tlkpInfoGrp. The combo on the form is still a normal Access combo. The extra is the Edit button beside it, which opens that list only.

The five columns

Access field names, not aliases. The combo and the Edit form all read these five:

In Access, Hide Me is a real Yes/No field. Combo SQL uses tlkpInfoDel = False, not the text 'No'. (A SQLite port of the same idea once stored Yes/No as text — that is a mapping trap, not the Access rule.)

How a group works

tlkpInfoGrp is not a separately numbered “virtual table index” (1, 2, 3…). It is the ID of the parent row. That is the whole trick, and the part that is easy to flatten by mistake.

Row 1 is reserved as a placeholder. It is not a combo value:

• tlkpInfoID = 1, description Group Name, tlkpInfoGrp = 0.
• Nothing else has group 0. Zero is not a real ID; the root has no parent, so you cannot put a database foreign key from tlkpInfoGrp to tlkpInfoID unless you allow 0 or use Null for the root.
• The number 1 (that row’s ID) is how you list the groups: WHERE tlkpInfoGrp = 1.

Here are the sample rows from the Lookup combo demo:

As a tree, the same rows look like this:

• Group Name ID 1 · Grp 0
  • Status ID 2 · Grp 1
    • Open ID 5 · Grp 2
    • In Progress ID 6 · Grp 2
    • Resolved ID 7 · Grp 2
    • Old status ID 8 · hidden
  • Category ID 3 · Grp 1
    • Software ID 9 · Grp 3
    • Hardware ID 10 · Grp 3
    • Process ID 11 · Grp 3
  • Assigned ID 4 · Grp 1
    • Role A ID 12 · Grp 4
    • Role B ID 13 · Grp 4
    • Role C ID 14 · Grp 4
    • Retired role ID 15 · hidden

To add a new list you insert a header with Grp = 1 (so it hangs off Group Name), then insert members whose Grp is that header’s new ID. Look up a group by name plus Grp = 1, not by hard-coding AutoNumbers that will differ from database to database.

What the combo stores

The combo shows the description and stores the ID on the business row (StatusID, CategoryID, AssignedToID on the sample items table). Bound column 1, ID column width zero — the usual Access pattern.

Status on the demo is group 2 (the ID of the Status header). Visible members, plus the current record’s ID so Hide Me cannot blank a bound combo:

SELECT tlkpInfo.tlkpInfoID,
       IIf([tlkpInfoDel],[tlkpInfoDesc] & " (hidden)",[tlkpInfoDesc])
FROM tlkpInfo
WHERE (([tlkpInfoGrp]=2 AND [tlkpInfoDel]=False)
    OR [tlkpInfoID]=[StatusID])
ORDER BY IIf([tlkpInfoDel],1,0), [tlkpInfoOrder];

Select ID then description, then order by tlkpInfoOrder. A query that returns only the name cannot be bound as a key.

There is no Access relationship that says “this Long must be a member of group 2”. A foreign key to tlkpInfoID can point at Group Name, at a header, or at a member. Keeping a combo inside one group is application logic (the WHERE clause on that combo), not an engine constraint.

Hide Me, not delete

The Edit list shows hidden members so you can untick Hide Me. The combo lists visible members, and also the current record’s ID (labelled (hidden)) so the control does not go blank.

If a saved item still has Status = 8, “Old status” must remain in the table or the name cannot be shown. Tick Hide Me to take it out of new choices. Do not hard-delete the row.

The Edit button

Next to each combo is Edit. It opens one editor form, already filtered to that combo’s group. The user never opens tlkpInfo as a table. They stay on the sample form, press Edit, tick Hide Me or type a new value, close. Closing requeries the combo they came from.

In Access the editor is a pop-up (frmTlkpInfo). On the web it is an HTML dialog over the same page. The subform (or the dialog table) does not allow deleting rows: hide, do not delete.

Some combos also use Not In List: type a name that is not in the list, confirm, and a new member is appended to that group. The demo’s Add box in the dialog is the same idea.

Nested lists

The demo is two levels: Group Name → list header → members. The same parent-pointer rule can go deeper. If Assigned had teams, and each team had people, people would have Grp = that team’s ID. After you pick a team, the people combo is WHERE tlkpInfoGrp = [the team ID you just stored]. Cascading drop-downs fall out of the same column.

Depth is not limited to two. The invariant is always: tlkpInfoGrp = parent tlkpInfoID.

What this is not

• It is not the Microsoft table Lookup Wizard (those are field properties on a table, not this pattern).
• It is not a value list typed into the combo itself. The list lives in tlkpInfo so every form can share it.
• Canonical Issues on this site still uses dedicated lookup tables (tlkpIssueStatus and friends: ID, Name, SortOrder, Active). That shape gives a real foreign key per list. tlkpInfo is the generic one-table system. We do not silently replace those dedicated tables with tlkpInfo.