Lookup fields

One of the advices on MVPS.org is not to use lookup fields in tables, their
commandment reads:
'thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One'

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup fields?

Thanks,
Luis.

Hi,


A possible problem with Lookup Field is that it displays data that is
not the one stored in the table, and may mislead someone not aware of it.
Personally, I use them often, but reformat the list so that confusion is
less possible.


Hoping it may help,
Vanderghast, Access MVP



"Luis Miguel" wrote in message
...
One of the advices on MVPS.org is not to use lookup fields in tables,
their
commandment reads:
'thou shalt abhor the use of "Lookup Fields" which art the creation of the
Evil One'

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup fields?

Thanks,
Luis.

Thanks for your prompt response.

I think I did not explain this correctly. I meant a field in a table that is
a Combo Box and its "row source type" is a Table/Query. That's what I though
they were referring to.

Is there anything wrong with this setup?

tblEmployees
employee_id (autonumber, primary key)
first_name
last_name
supervisor_id (number, Combo box, row source type: Table/Quey {Name:
tblSupervisor.first_name & " " tblSupervisor.last_name}, column width:
0";2")

tblSupervisor
supervisor_id (autonumber, primary key)
first_name
last_name

qrySupervisor
Name: tblSupervisor.first_name & " " tblSupervisor.last_name

In other words there is a relationship between tblEmployees.supervisor_id
and tblSupervisors.supervisor_id. I use the query to form the Name with the
first_name and the last_name fields, and I hide the supervisor_id field
making the first column's width zero.

Thanks,
Luis.

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
Hi,


A possible problem with Lookup Field is that it displays data that is
not the one stored in the table, and may mislead someone not aware of it.
Personally, I use them often, but reformat the list so that confusion is
less possible.


Hoping it may help,
Vanderghast, Access MVP



"Luis Miguel" wrote in message
...
One of the advices on MVPS.org is not to use lookup fields in tables,
their
commandment reads:
'thou shalt abhor the use of "Lookup Fields" which art the creation of
the
Evil One'

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup
fields?

Thanks,
Luis.

Luis

Michel's response was germane -- if you've defined a table field as "lookup"
data type, the table will display the "looked up" value, but hold the
underlying key value (a foreign key).

One issue with this happens when querying against the table. It would be
(and is, based on 'group posts) too easy to use one of the displayed values
as a criterion, and then be frustrated when no rows are returned. No rows
would be returned because the actual value in the field is a key (the stored
value), not the displayed value.

Good luck

Jeff Boyce
Access MVP

On Thu, 17 Jun 2004 07:03:33 -0400, "Luis Miguel"
wrote:

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup fields?

The criticism is primarily directed against the use of Table
Datasheets for data entry. Sure, a Lookup field makes it easier to
enter data directly into a table datasheet; and if you know its
drawbacks and limitations (creating redundant indexes and
relationships even if they already exist, concealing the actual
contents of your table from view, making it much harder to sort or
search the table, ...) and are comfortable with it, by all means go
ahead.

But in any professional application, the users will NEVER see table
datasheets - all interaction with the data occurs on Forms. And it's
perfectly straightforward to put a Combo Box (a "lookup") on a Form,
with or without the field being defined as a Lookup Field in the
table.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

John, I understand your point about not using datasheets for data entry and
would never let a user interact directly with them. That's why I didn't
mentioned anything about it.

Correct me if I'm wrong, instead of using the setup I explained in my
earlier post what I need to do to avoid the drawbacks and limitations you
mentioned is:
1 - Create a relationship between the fields employee_id in the two tables
2 - Create a combo box that looks up the formatted data (Name: first_name &
" " & last_name) in a form, but post the real value (employee_id) to the
foreign key on the other table

Thanks,

Luis.

"John Vinson" wrote in message
...
On Thu, 17 Jun 2004 07:03:33 -0400, "Luis Miguel"
wrote:

I have never seen this kind of advice in an Access book. Could somebody
explain a technique on how to replace the functionality of lookup fields?

The criticism is primarily directed against the use of Table
Datasheets for data entry. Sure, a Lookup field makes it easier to
enter data directly into a table datasheet; and if you know its
drawbacks and limitations (creating redundant indexes and
relationships even if they already exist, concealing the actual
contents of your table from view, making it much harder to sort or
search the table, ...) and are comfortable with it, by all means go
ahead.

But in any professional application, the users will NEVER see table
datasheets - all interaction with the data occurs on Forms. And it's
perfectly straightforward to put a Combo Box (a "lookup") on a Form,
with or without the field being defined as a Lookup Field in the
table.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

You are absolutely right, if I run a query using the displayed values I get
a data type error message. In order to retrieve data I have to use the value
of the foreign key.

Thanks,

Luis.

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Luis

Michel's response was germane -- if you've defined a table field as
"lookup"
data type, the table will display the "looked up" value, but hold the
underlying key value (a foreign key).

One issue with this happens when querying against the table. It would be
(and is, based on 'group posts) too easy to use one of the displayed
values
as a criterion, and then be frustrated when no rows are returned. No rows
would be returned because the actual value in the field is a key (the
stored
value), not the displayed value.

Good luck

Jeff Boyce
Access MVP

On Thu, 17 Jun 2004 17:58:11 -0400, "Luis Miguel"
wrote:

Correct me if I'm wrong, instead of using the setup I explained in my
earlier post what I need to do to avoid the drawbacks and limitations you
mentioned is:
1 - Create a relationship between the fields employee_id in the two tables
2 - Create a combo box that looks up the formatted data (Name: first_name &
" " & last_name) in a form, but post the real value (employee_id) to the
foreign key on the other table

That's exactly the correct technique.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public