Lookup Tables: Cascading Updates... Strategy Question

[(PeteCresswell)]

Until now, I have been heavily biased towards lookup tables where
every row has an "ID" and a "Name".

e.g. For tlkpDealer:

DealerID DealerName
-------------------
1 Goldman Sachs
2 Lehman
-------------------


Then in, for instance, tblSecurity I would store DealerID and
look up the name as needed. Just seemed cleaner: storing a
number of determinate length, indexing on same instead of text
fields, and so-forth.

But now I've begun doing a quick-and-dirty conversion for a user
from a rat's nest of spreadsheets to an MS Access back end.

The path of least resistance seems tb to dispense with ID numbers
and just link to lookup tables directly on name and let cascading
updates take care of any changes.

We're talking sorts of things like Dealers, Brokers, Investment
Types, Coupon Types, and so-forth.

Is there a "Gotcha" lurking here? Apostrophes/Quotes in names?
Other illegal characters?

IOW: In the end might I wish I'd stuck with the ID/Name scheme?

 

[Maelinar]

A strategy that I have seen employed to circumnavigate around lookup tables
is to use an unbound form - hence you use a dropdown/search/find/lookup to
bring in the data that you want, which becomes effective when you have more
than one variable that you may wish to search for (Dealers, Brokers,
Investment Types, Coupon Types, and so-forth)

Another way of considering this is that instead of scrolling through all of
the fields within the table (lookup table), you will not be accessing the
table at all until you have specified the data you want it to lookup.

The risk, naturally, is that somebody won't be able to spell - so I'd
consider using dropdowns over free text to limit your search wherever
possible.

Mæl