Evils of lookup fields

[LReber]

Hi -

Still a neophyte; I suspect I know just enough to get myself in trouble.
When I started, I inherited and learned on a table that grew to 76 or more
fields - we used Access like Excel, more or less. So have been working on
normalizing this and other databases. Looked at the 10 commandments and fear
I'm sinning . . .

I started creating a new database from scratch for museum collection - we
can't afford Past Perfect software, and own Access, so it's an easy decision.
Table structure includes:
tblAcquired (how we got item)
tblCategories
tblCondition
tblEngines
tblLocations
tblLocHistory
tblObjectNames (for standardized nomenclature)
tblObjects - the list of all the stuff
tblOriginal - is it an original, copy or restored?
tblOwners - ours or on loan to us?
tblPower - IC, electric, horse-drawn or other animal, human powered if a tool
tblStatusTypes
tblSubcategories

Some of these tables have very few records, so they could be looked up in a
value list, if that's the right term? Or is doing that evil too? Anyhow, my
question boils down to:

If we are supposed to separate data into different tables of like
information and create a relationship between them, how do we use / look up /
refer to that other information without using look-ups?

Thanks in advance - Lisa

 

[Tim Ferguson]

If we are supposed to separate data into different tables of like
information and create a relationship between them, how do we use /
look up / refer to that other information without using look-ups?

There is some misunderstanding about lookup tables and lookup fields.

Look up tables are what you seem to have lots of, and there is nothing
wrong with that. You might have a table looking like

ConditionDescriptions
ID Description
== -----------
1 Knackered
2 Acceptable
3 Smart
4 As New
99 Unseen

and then a numeric field in another table storing the 1s or 2s etc
instead of the words. No problem: you have total control over the
contents of the fields at very little cost in terms of complexity or
performance.

What is really, really, really bad is the Access Wizard for creating look
up tables, because it gets the process hopelessly badly mixed up with the
table design and forms design and GKWhat. It ends up lying to you about
what is in the table -- everyone thinks it's the text, but it's actually
the number, so that it never finds what you are looking for in a query.
It also disguises just how profligate it is with indexes, so you can hit
the 32 indexes per table limit without meaning to.

Better by far to create the tables and relationships by hand (takes all
of twentyfive seconds) and then you know what is going on. As far as UI
implementation goes, it's easy to plug the foreign table into a combo box
or list box (the combo box wizard is fine for this).

The other advantage of creating your own tables is that you can extend
their use vis:

ConditionDescriptions
ID Description VendorDiscount
== ----------- --------------
1 Knackered 33%
2 Acceptable 12%
3 Smart 5%
4 As New 0
99 Unseen 2%


and so on.


Hope that helps
All the best


Tim F

 

[LReber]

Tim - thanks for the prompt reply. I get frustrated when I don't know Why to
do something a certain way. It has been a while since I created the db, and I
don't remember if I made links from scratch or used the wizard. Laziness
being what it is, I suspect the latter. I'll check and post again if need be.

btw . . .

As far as UI
implementation goes, it's easy to plug the foreign table into a combo box
or list box (the combo box wizard is fine for this).

UI = user implementation, that is a form? or . . . ?

 

[Tim Ferguson]

UI = user implementation, that is a form? or . . . ?

User Interface: all the forms, the switchboards, the reports etc that you
create to help the user interact with the data. "UI Implementation" is just
a pretentious way of referring to creating the forms etc; but it also to
make the point that this comes after getting the data structures right.

Best of luck.
Do post back if you have further queries


Tim F

 

[LReber]

Tim - thanks for the explanation on UI (I guess I missed the friendly old 'G.')

Found I hated table wizard, so that's a relief. Does the Lookup Wizard in
table design view tend to lead to problems? I created my tables and
relationships and used the wizard to show what I wanted. At least I think
that's what I did. . . .

Thanks - Lisa

 

[Tim Ferguson]

Found I hated table wizard, so that's a relief. Does the Lookup Wizard
in table design view tend to lead to problems?

Yes: that is the one I was talking about earlier

I created my tables and
relationships and used the wizard to show what I wanted. At least I
think that's what I did. . . .

The ones you did yourself are probably fine; it's the ones the Wizard does
without telling you that are the problem...

All the best


Tim F