Lookup Field

[Damien McBain]

Just reading the "10 commandments" on MVPS. One says not to use a lookup
field but I don't understand the explanation they give:
"A Lookup field in a table displays the looked-up value. For instance, if a
user opens a table datasheet and sees a column of company names, what is in
the table is, in fact, a numeric CompanyID, and the table is linked with a
select statement to the company table by that ID."
What do they mean by this?

 

[Kailash Kalyani]

I think it refers to perhaps the confusion caused because what you see in
the datasheet view is not what acutally is in the table.


Kailash Kalyani
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
--------------------

 

[Rick Brandt]

Just reading the "10 commandments" on MVPS. One says not to use a lookup
field but I don't understand the explanation they give:
"A Lookup field in a table displays the looked-up value. For instance, if a
user opens a table datasheet and sees a column of company names, what is in
the table is, in fact, a numeric CompanyID, and the table is linked with a
select statement to the company table by that ID."
What do they mean by this?

Lookup fields in tables are a mechanism to make the raw table a more
user-friendly interface. This is problem one as tables should never be
used as an interface in the first place. They can cause confusion for many
new users since what you see in the table is not what the table actually
contains. Later when these users build queries against these tables they
are confused when they get the actual stored values from the table instead
of the values they see when looking at it.

If you know what is going on and the consequences they can be harmless, but
they are always unnecessary since the same functionality can be added to a
form (a proper user interface) without having a lookup field defined in the
table.

To me the primary issue is that they're most useful to the very people who
are least likely to understand how they work, that is a "user" creating
some simple app for their own purposes.

 

[Douglas J. Steele]

When you create a lookup field, you tell Access the table from which to get
the names that are to be displayed in the field (let's call it tblLookup).
When you look at the table, it looks as though there is text being stored in
that field, but what's actually being stored is simply a number. The text
being displayed corresponds to the text associated with that number in
tblLookup.

What's happening under the covers is that rather than:

SELECT Field1, Field2, Field3 FROM tblData

you've got the equivalent of

SELECT tblData.Field1, tblData.Field2, tblLookup.DisplayText
FROM tblData
INNER JOIN tblLookup
WHERE tblLookup.Id = tblData.Field3

 

[Damien McBain]

When you create a lookup field, you tell Access the table from which
to get the names that are to be displayed in the field (let's call it
tblLookup). When you look at the table, it looks as though there is
text being stored in that field, but what's actually being stored is
simply a number. The text being displayed corresponds to the text
associated with that number in tblLookup.

What's happening under the covers is that rather than:

SELECT Field1, Field2, Field3 FROM tblData

you've got the equivalent of

SELECT tblData.Field1, tblData.Field2, tblLookup.DisplayText
FROM tblData
INNER JOIN tblLookup
WHERE tblLookup.Id = tblData.Field3

Oh yes I can see how that would be shitty, although I can't see why anyone
who is using the program as a relational db would want to do that. Maybe it
would be handy for a quick look at some data downloaded from a main system
or something.

Thanks to those who replied.

Damo