Look up fields in table & DLookup

[Dennis]

Hi,

I'm on XP PRO / SP3 and Access via XP Office Pro w/ SP3. I'm relatively new
and still learning.

I've read where it is a bad idea to have a lookup field in a table due to
the overhead involved, not being able to see the read data in the field, and
causing lookup fields on report, which is rather slow.

First question: If I take out the lookup field, can I replace the
functionality of the lookup field in the table definition?

Second:
When I create a logical view of the table via a query, what is the best /
most effecient way to display the look up description? I guessing via a
DLOOKUP? I know I can join the two tables, but I though the join was best
used for joining two data tables not a data table and a lookup table. Any
thoughts?

 

[Jeff Boyce]

Dennis

JOPO ... just one person's opinions ...

Lookup data type fields in a table definition can cause confusion, since
they store one thing (the lookup table's row's primary key) but display
something else. This is confusing when someone is working directly in the
table (not a good idea!), and when someone is working on a form that points
to the table (either directly or via a query).

I can't speak for the overhead or performance, but the confusion factor is
often enough to skip this data type. Besides, do you really want your users
to be muckin' about in your tables?! That's what Access forms are for, to
direct and guide users' use of the data.

I've not noted a performance issue in using a join in a query to return the
"looked-up" value. And I seem to recall that the DLookup() function can
suffer serious performance issues.

My opinions? Don't use lookup datatypes in tables unless you are the ONLY
person who will ever use or maintain the database AND you will NEVER (OK,
RARELY ever) need to revisit the tables to find information.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I'm on XP PRO / SP3 and Access via XP Office Pro w/ SP3. I'm relatively new
and still learning.

I've read where it is a bad idea to have a lookup field in a table due to
the overhead involved, not being able to see the read data in the field, and
causing lookup fields on report, which is rather slow.

First question: If I take out the lookup field, can I replace the
functionality of the lookup field in the table definition?

Second:
When I create a logical view of the table via a query, what is the best /
most effecient way to display the look up description? I guessing via a
DLOOKUP? I know I can join the two tables, but I though the join was best
used for joining two data tables not a data table and a lookup table. Any
thoughts?

The first thing you should wrap your mind around is that
tables and queries should NOT be displayed to users at any
time for any reason. Once your app deals with that, the
only person that ever looks at a table/query datasheet view
is you when you are debugging some problem. You, being the
developer, should understand the real data and have no need
for the "nicities" of lookup fields or other data
formatting, you really want to see the raw data. All that
means that your questions are not particularly relevant to
anything useful.

When users need to see/edit/enter data, you should always
present it via forms or reports where formatting is
desirable and appropriate.

The part of your second question about joining tables is
that the linking fields between two (or more) related tables
should always be indexed, which are called the primary key
and foreign keys. Most of that is taken care of
automatically when you use the Relationships window to
specify thow your database's tables are related. With all
that in place, using DLookup can be horribly inefficient in
a query. Instead, you should use the appropriate type of
JOIN and sometimes a subquery or GROUP BY (Totals type
query) to get aggregate values.

 

[Dennis]

All,

Just to make clear, at NO time was it ever intended that the users should
look directly at the tables! I would be the only one looking directly at the
tables.

I did it purely for my convience. There were two purposes for putting the
lookup logic in the table definitions:

1. I could define it in one place and have it automatically appear on all
forms, queries, and reports.
2. When I look at the data (during testing) it is easier to see the
meaining of the code rather than just numbers. (That turned out to be
incorrect.)


However, after reading books and articles it was repeated explained why this
was a bad idea. Also, after working with it, you are right that it does get
a bit confusing looking at the translation rather than the raw data.

My lookup tables are indexed by the primary key (which are the FK in the
original data table). I will modify my queries to join the data table and my
lookup tables.

One last question. The lookup tables are automatically indexed by the PK.
Do I need to index the FK in my data file also?

In the relationiship window, I dragged the tblState's PK to the State FK
field on the tblCustomer. I then check the indexes on the tblCustomer and
noticed that the Relationship process had not indexed the FK of State on
tblCustomer.

Just wanted to confirm that I need to index the State FK on tblCustomer.

And thank you for your assistants and your JOPO.

 

[John W. Vinson]

One last question. The lookup tables are automatically indexed by the PK.
Do I need to index the FK in my data file also?

No. Defining a relationship, either using the relationships window or the
Lookup Wizard, automatically creates an index on the FK.

 

[Marshall Barton]

Just to make clear, at NO time was it ever intended that the users should
look directly at the tables! I would be the only one looking directly at the
tables.

I did it purely for my convience. There were two purposes for putting the
lookup logic in the table definitions:

1. I could define it in one place and have it automatically appear on all
forms, queries, and reports.
2. When I look at the data (during testing) it is easier to see the
meaining of the code rather than just numbers. (That turned out to be
incorrect.)


However, after reading books and articles it was repeated explained why this
was a bad idea. Also, after working with it, you are right that it does get
a bit confusing looking at the translation rather than the raw data.

My lookup tables are indexed by the primary key (which are the FK in the
original data table). I will modify my queries to join the data table and my
lookup tables.

One last question. The lookup tables are automatically indexed by the PK.
Do I need to index the FK in my data file also?

In the relationiship window, I dragged the tblState's PK to the State FK
field on the tblCustomer. I then check the indexes on the tblCustomer and
noticed that the Relationship process had not indexed the FK of State on
tblCustomer.

Just wanted to confirm that I need to index the State FK on tblCustomer.

Establishing a relationship in the Relationships window
automatically creates a hidden index on the foreign key
field(s). These indexes are NOT displayed in the table's
Indexes window (don't ask me why), but you can see them by
using a little code to loop through the table's indexes
collection:

Public Sub ShowIndex(strTable As String)
Dim db As Database
Dim tdf As TableDef
Dim idx As Index

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
Debug.Print tdf.Name
For Each idx In tdf.Indexes
Debug.Print Tab; idx.Name; ": "; idx.Fields; " ";
Debug.Print IIf(idx.Foreign, "<Foreign>", "");
Debug.Print IIf(idx.Primary, "<Primary>", "");
Debug.Print
Next idx

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Note that the name of an automatically created index is semi
random and not particularly meaningful.

You can then use that procedure to see which indexes you
created in the Indexes window that duplicate automatically
created foreign key indexes and remove your redundant,
manually created indexes. This can be an important exercise
because there is a limit of 32 indexes for a table and the
maintenance overhead for redundant indexes can be a
performance drag.

 

[Dennis]

John, Marshall,

Once again thank you for your assitance! I GREATLY appreciate your help, it
has helped me in so many ways. Thanks once again.