In Dev Anish's "...Ten Commandments..." for healthy Access usage I pretty m
uch understand and live by all  - except:
=A0
"... thou shalt abhor the use of "Lookup Fields" which art the creation of 
the Evil One."

I haven't a clue what "lookup fields are or why they might be a bad idea.  
  Can anyone explain?

Thanks in advance!

Charlie
=A0

if they are that evil maybe it is best that you don't know ! 

-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...]
Sent: Monday, December 16, 2002 1:33 PM
To: Access
Subject: [access] What are Lookup fields ?


In Dev Anish's "...Ten Commandments..." for healthy Access usage I 
pretty
much understand and live by all  - except:
=A0
"... thou shalt abhor the use of "Lookup Fields" which art the creation 
of
the Evil One."

I haven't a clue what "lookup fields are or why they might be a bad 
idea.
Can anyone explain?

Thanks in advance!

Charlie
=A0

OK Seriously I'm not sure what he refers to here... I think bound 
lookups on
forms...

-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...]
Sent: Monday, December 16, 2002 1:33 PM
To: Access
Subject: [access] What are Lookup fields ?


In Dev Anish's "...Ten Commandments..." for healthy Access usage I 
pretty
much understand and live by all  - except:
=A0
"... thou shalt abhor the use of "Lookup Fields" which art the creation 
of
the Evil One."

I haven't a clue what "lookup fields are or why they might be a bad 
idea.
Can anyone explain?

Thanks in advance!

Charlie
=A0

Good point, but then again, how do I know that I am not using those BAD THI
NGS right now, and just don't know it?

C

> if they are that evil maybe it is best that you don't know !
> 
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...] [mailto:cgoodwin@c...
net.com]]
> Sent: Monday, December 16, 2002 1:33 PM
> To: Access
> Subject: [access] What are Lookup fields ?
> 
> 
> In Dev Anish's "...Ten Commandments..." for healthy Access usage I pretty
> much understand and live by all  - except:
> =A0
> "... thou shalt abhor the use of "Lookup Fields" which art the creation of
> the Evil One."
> 
> I haven't a clue what "lookup fields are or why they might be a bad idea.
> Can anyone explain?
> 
> Thanks in advance!
> 
> Charlie
> =A0
> 
> 

Dave,

I'm feeling like perhaps I'm missing some elementary terminology.    Are we
 talking about a combo using it's bound field also as it's record source, s
o that a user no longer has to type, say "Boston", into an address field, a
fter that value is entered once, but just has to start typing the first cou
ple of letters to get the value to come up again?

C



> OK Seriously I'm not sure what he refers to here... I think bound lookups
 on
> forms...
>
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...] [mailto:cgoodwin@c...
net.com]]
> Sent: Monday, December 16, 2002 1:33 PM
> To: Access
> Subject: [access] What are Lookup fields ?
>
>
> In Dev Anish's "...Ten Commandments..." for healthy Access usage I pretty
> much understand and live by all  - except:
> =A0
> "... thou shalt abhor the use of "Lookup Fields" which art the creation of
> the Evil One."
>
> I haven't a clue what "lookup fields are or why they might be a bad idea.
> Can anyone explain?
>
> Thanks in advance!
>
> Charlie
> =A0
>
>

Hi Charlie

Very similar to this, yes. It's where you set up a combo box lookup on a
foreign key in the TABLE DESIGN. Once you've done this the user can
never see the data that=92s held in the table - all they get to see is 
the
lookup.

I'm not sure how evil it is...

Brian

-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...]
Sent: 17 December 2002 16:18
To: Access
Subject: [access] Re: What are Lookup fields ?

Dave,

I'm feeling like perhaps I'm missing some elementary terminology.    Are
we talking about a combo using it's bound field also as it's record
source, so that a user no longer has to type, say "Boston", into an
address field, after that value is entered once, but just has to start
typing the first couple of letters to get the value to come up again?

C



> OK Seriously I'm not sure what he refers to here... I think bound
lookups on
> forms...
>
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...]
[mailto:cgoodwin@c...]]
> Sent: Monday, December 16, 2002 1:33 PM
> To: Access
> Subject: [access] What are Lookup fields ?
>
>
> In Dev Anish's "...Ten Commandments..." for healthy Access usage I
pretty
> much understand and live by all  - except:
> =A0
> "... thou shalt abhor the use of "Lookup Fields" which art the
creation of
> the Evil One."
>
> I haven't a clue what "lookup fields are or why they might be a bad
idea.
> Can anyone explain?
>
> Thanks in advance!
>
> Charlie
> =A0
>
>

Hi Charlie,

Brian is right. Mr. Ashish is referring to the lookup filed wizard
feature for table fileds that was new with Access 2k. This feature
essentially embeds queries (SQL statements) in tables, which a lot of
database "purists" have a problem with. It gets the whole db schema
design thing bass ackwards.



>From: "Charlie Goodwin" <cgoodwin@c...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Tue, 17 Dec 2002 11:18:06 -0500
>
>Dave,
>
>I'm feeling like perhaps I'm missing some elementary terminology.    Are we 
>talking about a combo using it's bound field also as it's record source, so 
>that a user no longer has to type, say "Boston", into an address field, 
>after that value is entered once, but just has to start typing the first 
>couple of letters to get the value to come up again?
>
>C
>
>
>
> > OK Seriously I'm not sure what he refers to here... I think bound 
>lookups on
> > forms...
> >
> > -----Original Message-----
> > From: Charlie Goodwin [mailto:cgoodwin@c...] 
>[mailto:cgoodwin@c...]]
> > Sent: Monday, December 16, 2002 1:33 PM
> > To: Access
> > Subject: [access] What are Lookup fields ?
> >
> >
> > In Dev Anish's "...Ten Commandments..." for healthy Access usage I 
>pretty
> > much understand and live by all  - except:
> >  
> > "... thou shalt abhor the use of "Lookup Fields" which art the creation 
>of
> > the Evil One."
> >
> > I haven't a clue what "lookup fields are or why they might be a bad 
>idea.
> > Can anyone explain?
> >
> > Thanks in advance!
> >
> > Charlie
> >  
> >
> >
>

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

Just wanted to add my two cents and say that I agree with Mr. Ashish. 
Lookups in tables are, in my opinion, a lousy feature, and I never use 'em.

> Hi Charlie,

Brian is right. Mr. Ashish is referring to the lookup filed wizard
feature for table fileds that was new with Access 2k. This feature
essentially embeds queries (SQL statements) in tables, which a lot of
database "purists" have a problem with. It gets the whole db schema
design thing bass ackwards.



>From: "Charlie Goodwin" <cgoodwin@c...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Tue, 17 Dec 2002 11:18:06 -0500
>
>Dave,
>
>I'm feeling like perhaps I'm missing some elementary terminology.    Are 
we 
>talking about a combo using it's bound field also as it's record source, 
so 
>that a user no longer has to type, say "Boston", into an address field, 
>after that value is entered once, but just has to start typing the first 
>couple of letters to get the value to come up again?
>
>C
>
>
>
> > OK Seriously I'm not sure what he refers to here... I think bound 
>lookups on
> > forms...
> >
> > -----Original Message-----
> > From: Charlie Goodwin [mailto:cgoodwin@c...] 
>[mailto:cgoodwin@c...]]
> > Sent: Monday, December 16, 2002 1:33 PM
> > To: Access
> > Subject: [access] What are Lookup fields ?
> >
> >
> > In Dev Anish's "...Ten Commandments..." for healthy Access usage I 
>pretty
> > much understand and live by all  - except:
> >  
> > "... thou shalt abhor the use of "Lookup Fields" which art the 
creation 
>of
> > the Evil One."
> >
> > I haven't a clue what "lookup fields are or why they might be a bad 
>idea.
> > Can anyone explain?
> >
> > Thanks in advance!
> >
> > Charlie
> >  
> >
> >
>

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

	This confuses me now, before they say that look up tables are great because it prevents the
programmer from doing lot of workload if ever there any changes on "lists" like for example mode of
payments.  If there new mode of payment that the company would offer then with out look up fields,
the programmer has to go to all the forms that has the Mode of Payment field and add the new mode of
payment unlike in using look up table, all the programmer has to do is just update the lookup table
and then wolla it's done.

	May be what Anish is saying is that lookup fields that are bound, but for unbound look ups
I think is not evil.  I use look up tables a lot.  But I don't bound it, all I do is query the table
fill the combo box then close the recordset and connection.

	Any idea what makes it evil?

enzo c",)?
http://www.tropangwatakwatak.tk

-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...]
Sent: Wednesday, December 18, 2002 12:18 AM
To: Access
Subject: [access] Re: What are Lookup fields ?


Dave,

I'm feeling like perhaps I'm missing some elementary terminology.    Are we talking about a combo using it's bound field also as
it's record source, so that a user no longer has to type, say "Boston", into an address field, after that value is entered once,
but just has to start typing the first couple of letters to get the value to come up again?

C



> OK Seriously I'm not sure what he refers to here... I think bound lookups on
> forms...
>
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...] [mailto:cgoodwin@c...]]
> Sent: Monday, December 16, 2002 1:33 PM
> To: Access
> Subject: [access] What are Lookup fields ?
>
>
> In Dev Anish's "...Ten Commandments..." for healthy Access usage I pretty
> much understand and live by all  - except:
>  
> "... thou shalt abhor the use of "Lookup Fields" which art the creation of
> the Evil One."
>
> I haven't a clue what "lookup fields are or why they might be a bad idea.
> Can anyone explain?
>
> Thanks in advance!
>
> Charlie
>  
>
>

Hi Enzo,

Lookup TABLES are GOOD.
Lookup FIELDS are BAD.

Lookup fields are created IN tables by opening the Data Type
dropdown list in Design View and selecting Lookup Wizard. I
thought this feature was new with A2K but it was available in
A97 too. Keep on using lookup TABLES. They're good.

See:

http://support.microsoft.com/default.aspx?scid=KB;en-us;304464&
http://support.microsoft.com/default.aspx?scid=KB;en-us;304463&

Hope that helps.

Bob

>From: "Enzo Zaragoza" <enzaux@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Wed, 18 Dec 2002 09:10:24 +0800
>
>
>	This confuses me now, before they say that look up tables are great 
>because it prevents the
>programmer from doing lot of workload if ever there any changes on "lists" 
>like for example mode of
>payments.  If there new mode of payment that the company would offer then 
>with out look up fields,
>the programmer has to go to all the forms that has the Mode of Payment 
>field and add the new mode of
>payment unlike in using look up table, all the programmer has to do is just 
>update the lookup table
>and then wolla it's done.
>
>	May be what Anish is saying is that lookup fields that are bound, but for 
>unbound look ups
>I think is not evil.  I use look up tables a lot.  But I don't bound it, 
>all I do is query the table
>fill the combo box then close the recordset and connection.
>
>	Any idea what makes it evil?
>
>enzo c",)?
>http://www.tropangwatakwatak.tk
>
>-----Original Message-----
>From: Charlie Goodwin [mailto:cgoodwin@c...]
>Sent: Wednesday, December 18, 2002 12:18 AM
>To: Access
>Subject: [access] Re: What are Lookup fields ?
>
>
>Dave,
>
>I'm feeling like perhaps I'm missing some elementary terminology.    Are we 
>talking about a combo using it's bound field also as
>it's record source, so that a user no longer has to type, say "Boston", 
>into an address field, after that value is entered once,
>but just has to start typing the first couple of letters to get the value 
>to come up again?
>
>C
>
>
>
> > OK Seriously I'm not sure what he refers to here... I think bound 
>lookups on
> > forms...
> >
> > -----Original Message-----
> > From: Charlie Goodwin [mailto:cgoodwin@c...] 
>[mailto:cgoodwin@c...]]
> > Sent: Monday, December 16, 2002 1:33 PM
> > To: Access
> > Subject: [access] What are Lookup fields ?
> >
> >
> > In Dev Anish's "...Ten Commandments..." for healthy Access usage I 
>pretty
> > much understand and live by all  - except:
> >  
> > "... thou shalt abhor the use of "Lookup Fields" which art the creation 
>of
> > the Evil One."
> >
> > I haven't a clue what "lookup fields are or why they might be a bad 
>idea.
> > Can anyone explain?
> >
> > Thanks in advance!
> >
> > Charlie
> >  
> >
> >
>
>
>
>
>


_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus

	OIC so I mixed things up! thanks for clearing out!

Thanks,

enzo c",)?
http://www.tropangwatakwatak.tk

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Wednesday, December 18, 2002 9:55 AM
To: Access
Subject: [access] Re: What are Lookup fields ?


Hi Enzo,

Lookup TABLES are GOOD.
Lookup FIELDS are BAD.

Lookup fields are created IN tables by opening the Data Type
dropdown list in Design View and selecting Lookup Wizard. I
thought this feature was new with A2K but it was available in
A97 too. Keep on using lookup TABLES. They're good.

See:

http://support.microsoft.com/default.aspx?scid=KB;en-us;304464&
http://support.microsoft.com/default.aspx?scid=KB;en-us;304463&

Hope that helps.

Bob

>From: "Enzo Zaragoza" <enzaux@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Wed, 18 Dec 2002 09:10:24 +0800
>
>
>	This confuses me now, before they say that look up tables are great
>because it prevents the
>programmer from doing lot of workload if ever there any changes on "lists"
>like for example mode of
>payments.  If there new mode of payment that the company would offer then
>with out look up fields,
>the programmer has to go to all the forms that has the Mode of Payment
>field and add the new mode of
>payment unlike in using look up table, all the programmer has to do is just
>update the lookup table
>and then wolla it's done.
>
>	May be what Anish is saying is that lookup fields that are bound, but for
>unbound look ups
>I think is not evil.  I use look up tables a lot.  But I don't bound it,
>all I do is query the table
>fill the combo box then close the recordset and connection.
>
>	Any idea what makes it evil?
>
>enzo c",)?
>http://www.tropangwatakwatak.tk
>
>-----Original Message-----
>From: Charlie Goodwin [mailto:cgoodwin@c...]
>Sent: Wednesday, December 18, 2002 12:18 AM
>To: Access
>Subject: [access] Re: What are Lookup fields ?
>
>
>Dave,
>
>I'm feeling like perhaps I'm missing some elementary terminology.    Are we
>talking about a combo using it's bound field also as
>it's record source, so that a user no longer has to type, say "Boston",
>into an address field, after that value is entered once,
>but just has to start typing the first couple of letters to get the value
>to come up again?
>
>C
>
>
>
> > OK Seriously I'm not sure what he refers to here... I think bound
>lookups on
> > forms...
> >
> > -----Original Message-----
> > From: Charlie Goodwin [mailto:cgoodwin@c...]
>[mailto:cgoodwin@c...]]
> > Sent: Monday, December 16, 2002 1:33 PM
> > To: Access
> > Subject: [access] What are Lookup fields ?
> >
> >
> > In Dev Anish's "...Ten Commandments..." for healthy Access usage I
>pretty
> > much understand and live by all  - except:
> >  
> > "... thou shalt abhor the use of "Lookup Fields" which art the creation
>of
> > the Evil One."
> >
> > I haven't a clue what "lookup fields are or why they might be a bad
>idea.
> > Can anyone explain?
> >
> > Thanks in advance!
> >
> > Charlie
> >  
> >
> >
>
>
>
>
>


_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus

I'm a bit confused now, not by what they are, but by what makes them proble
matical.

Lookup fields allow one, for example, just the way a combo or list box on a
 form would, to make use of the hidden column, to store a value, say, an ID
, but view a text description.   Even when you look in the table, what you 
see is the text description, though the field is numeric.   As far as I can
 tell, the field actually stores the number - but displays the text.

Just make a form from the table and the field that uses a lookup will have 
a combo built in the moment you drag it from the field list.

One thing that makes it interesting is that if you go back later, back into
 the table, back to that field, and change the value in the lookup tab from
 combo, back to text, the IDs reappear - yet the forms retain their inherit
ed combos and everything still works.   I am guessing that the field has be
en storing the ID (in this example) all along, and just showing us the asso
ciated text.   Even in the table's datasheet view.

I'm assuming the data, in my example, an ID in the hidden column, remains t
he item that's really stored, and that what's displayed changes when one us
es a lookup field.   I assume it's kind of like dates where the underlying 
data usually is different than what gets displayed to the user.

What I'm unsure of is what makes all this bad.

Charlie




>
> 	This confuses me now, before they say that look up tables are great beca
use it prevents the
> programmer from doing lot of workload if ever there any changes on "lists
" like for example mode of
> payments.  If there new mode of payment that the company would offer then
 with out look up fields,
> the programmer has to go to all the forms that has the Mode of Payment fi
eld and add the new mode of
> payment unlike in using look up table, all the programmer has to do is ju
st update the lookup table
> and then wolla it's done.
>
> 	May be what Anish is saying is that lookup fields that are bound, but fo
r unbound look ups
> I think is not evil.  I use look up tables a lot.  But I don't bound it, 
all I do is query the table
> fill the combo box then close the recordset and connection.
>
> 	Any idea what makes it evil?
>
> enzo c",)=99
> http://www.tropangwatakwatak.tk
>
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...] [mailto:cgoodwin@c...
net.com]]
> Sent: Wednesday, December 18, 2002 12:18 AM
> To: Access
> Subject: [access] Re: What are Lookup fields ?
>
>
> Dave,
>
> I'm feeling like perhaps I'm missing some elementary terminology.    Are 
we talking about a combo using it's bound field also as
> it's record source, so that a user no longer has to type, say "Boston", i
nto an address field, after that value is entered once,
> but just has to start typing the first couple of letters to get the value
 to come up again?
>
> C
>
>
>
> > OK Seriously I'm not sure what he refers to here... I think bound looku
ps on
> > forms...
> >
> > -----Original Message-----
> > From: Charlie Goodwin [mailto:cgoodwin@c...] [mailto:cgoodwin@c...
nknet.com]] [mailto:cgoodwin@c...]] [mailto:cgoodwin@c...]]]
> > Sent: Monday, December 16, 2002 1:33 PM
> > To: Access
> > Subject: [access] What are Lookup fields ?
> >
> >
> > In Dev Anish's "...Ten Commandments..." for healthy Access usage I pret
ty
> > much understand and live by all  - except:
> > =A0
> > "... thou shalt abhor the use of "Lookup Fields" which art the creation
 of
> > the Evil One."
> >
> > I haven't a clue what "lookup fields are or why they might be a bad ide
a.
> > Can anyone explain?
> >
> > Thanks in advance!
> >
> > Charlie
> > =A0
> >
> >
>
>
>
>
>

Bob,

<< Lookup TABLES are GOOD.
Lookup FIELDS are BAD. >>

I agree with Charlie.  I still don't see why this is so.  For a field that 
has a discreet list of items where "Limit to List" is set to "Yes", and 
the rowsource of the list is set to table/query (not value list), lookup 
fields are GOOD.  If you set up a lookup field in a table, then when 
creating forms, that field will always appear as a combobox with the 
proper list items as the rowsource... always.  You set it once at the 
table and each and every form will be autoformatted.  No need to do it 
each time.

Even a lookup field in a table with a value list as a rowsource would 
still facilitate form building.  However, I tend not to use value list 
unless the choices are universal and final, e.g. the dropdown has units of 
measure for temperature and the only choices you want EVER are °C and °F.

So, now I'm curious.  What's SO bad about lookup fields?

Greg

I think it really depends on how you use Access, and how you program it.
You can be anywhere along a spectrum from using an occaisonal form created
by a Wizard, to disconnected client-server VBA and unbound forms.  The
closer you are to the latter (which is where I am), the more that features
like lookup fields tend to cause grief, because they are harder to catalog
and recognize when doing maintenance.  

For example I have some apps that use fields that have as a record source a
SQL query, like "Select name from tbl_managers where department = X"  Let's
say I have to modify tbl_manager to remove the department field.  If I
search all my code for references to "tbl_managers" or the "department"
field, I will never find this reference buried in a field property on a
form.  So my level of application-specific knowledge required to do
effective maintenance on this app has just gone up.  Assign somebody not
familiar withthe app to make the change and theya re going to miss stuff
like this.  Now, if that same field was populated from the same query, but
imbedded in my VBA code and populated at runtime, a simple text search of
the VBA code will find it and it can be easily corrected.



-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...]
Sent: Tuesday, December 17, 2002 11:05 PM
To: Access
Subject: [access] Re: What are Lookup fields ?


I'm a bit confused now, not by what they are, but by what makes them
problematical.

Lookup fields allow one, for example, just the way a combo or list box on a
form would, to make use of the hidden column, to store a value, say, an ID,
but view a text description.   Even when you look in the table, what you see
is the text description, though the field is numeric.   As far as I can
tell, the field actually stores the number - but displays the text.

Just make a form from the table and the field that uses a lookup will have a
combo built in the moment you drag it from the field list.

One thing that makes it interesting is that if you go back later, back into
the table, back to that field, and change the value in the lookup tab from
combo, back to text, the IDs reappear - yet the forms retain their inherited
combos and everything still works.   I am guessing that the field has been
storing the ID (in this example) all along, and just showing us the
associated text.   Even in the table's datasheet view.

I'm assuming the data, in my example, an ID in the hidden column, remains
the item that's really stored, and that what's displayed changes when one
uses a lookup field.   I assume it's kind of like dates where the underlying
data usually is different than what gets displayed to the user.

What I'm unsure of is what makes all this bad.

Charlie




>
> 	This confuses me now, before they say that look up tables are great
because it prevents the
> programmer from doing lot of workload if ever there any changes on "lists"
like for example mode of
> payments.  If there new mode of payment that the company would offer then
with out look up fields,
> the programmer has to go to all the forms that has the Mode of Payment
field and add the new mode of
> payment unlike in using look up table, all the programmer has to do is
just update the lookup table
> and then wolla it's done.
>
> 	May be what Anish is saying is that lookup fields that are bound,
but for unbound look ups
> I think is not evil.  I use look up tables a lot.  But I don't bound it,
all I do is query the table
> fill the combo box then close the recordset and connection.
>
> 	Any idea what makes it evil?
>
> enzo c",)(tm)
> http://www.tropangwatakwatak.tk
>
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...]
[mailto:cgoodwin@c...]]
> Sent: Wednesday, December 18, 2002 12:18 AM
> To: Access
> Subject: [access] Re: What are Lookup fields ?
>
>
> Dave,
>
> I'm feeling like perhaps I'm missing some elementary terminology.    Are
we talking about a combo using it's bound field also as
> it's record source, so that a user no longer has to type, say "Boston",
into an address field, after that value is entered once,
> but just has to start typing the first couple of letters to get the value
to come up again?
>
> C
>
>
>
> > OK Seriously I'm not sure what he refers to here... I think bound
lookups on
> > forms...
> >
> > -----Original Message-----
> > From: Charlie Goodwin [mailto:cgoodwin@c...]
[mailto:cgoodwin@c...]] [mailto:cgoodwin@c...]]
[mailto:cgoodwin@c...]]]
> > Sent: Monday, December 16, 2002 1:33 PM
> > To: Access
> > Subject: [access] What are Lookup fields ?
> >
> >
> > In Dev Anish's "...Ten Commandments..." for healthy Access usage I
pretty
> > much understand and live by all  - except:
> >  
> > "... thou shalt abhor the use of "Lookup Fields" which art the creation
of
> > the Evil One."
> >
> > I haven't a clue what "lookup fields are or why they might be a bad
idea.
> > Can anyone explain?
> >
> > Thanks in advance!
> >
> > Charlie
> >  
> >
> >
>
>
>
>
>

I didn't want to make it look like I am or am not in "favor" of lookup fiel
ds, but have been unsure of why they have a cloud above them.   Dave's last
 message makes it appear that when maintaining a complex DB, lookup fields 
could become one more unneeded complexity to unravel.

I appreciate the extra effort taken to make it all clear.   Thanks to all.

Charlie



> Bob,
> 
> << Lookup TABLES are GOOD.
> Lookup FIELDS are BAD. >>
> 
> I agree with Charlie.  I still don't see why this is so.  For a field that
> has a discreet list of items where "Limit to List" is set to "Yes", and
> the rowsource of the list is set to table/query (not value list), lookup
> fields are GOOD.  If you set up a lookup field in a table, then when
> creating forms, that field will always appear as a combobox with the
> proper list items as the rowsource... always.  You set it once at the
> table and each and every form will be autoformatted.  No need to do it
> each time.
> 
> Even a lookup field in a table with a value list as a rowsource would
> still facilitate form building.  However, I tend not to use value list
> unless the choices are universal and final, e.g. the dropdown has units of
> measure for temperature and the only choices you want EVER are =B0C and 
=B0F.
> 
> So, now I'm curious.  What's SO bad about lookup fields?
> 
> Greg

e.g. the dropdown has units of measure for temperature and the only 
choices
you want EVER are =B0C and =B0F.

Hey! What about the Kelvin and Rankine temperature scales!

8-)

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Gregory Serrano [mailto:SerranoG@m...]
Sent: Wednesday, December 18, 2002 07:49
To: Access
Subject: [access] Re: What are Lookup fields ?

Bob,

<< Lookup TABLES are GOOD.
Lookup FIELDS are BAD. >>

I agree with Charlie.  I still don't see why this is so.  For a field 
that
has a discreet list of items where "Limit to List" is set to "Yes", and
the rowsource of the list is set to table/query (not value list), 
lookup
fields are GOOD.  If you set up a lookup field in a table, then when
creating forms, that field will always appear as a combobox with the
proper list items as the rowsource... always.  You set it once at the
table and each and every form will be autoformatted.  No need to do it
each time.

Even a lookup field in a table with a value list as a rowsource would
still facilitate form building.  However, I tend not to use value list
unless the choices are universal and final, e.g. the dropdown has units 
of
measure for temperature and the only choices you want EVER are =B0C and 
=B0F.

So, now I'm curious.  What's SO bad about lookup fields?

Greg

And the ever popular R=E9aumur temperature scale?

Let's not be an exclusive club!

C




> e.g. the dropdown has units of measure for temperature and the only choic
es
> you want EVER are =B0C and =B0F.
>
> Hey! What about the Kelvin and Rankine temperature scales!
>
> 8-)
>
> Rand E Gerald
> Database Specialist
> Information Services / Operations
> Bah=E1'=ED National Office
> 1233 Central St.
> Evanston IL 60201
> (xxx) xxx-xxxx
>
> -----Original Message-----
> From: Gregory Serrano [mailto:SerranoG@m...] [mailto:SerranoG@m...
higan.gov]]
> Sent: Wednesday, December 18, 2002 07:49
> To: Access
> Subject: [access] Re: What are Lookup fields ?
>
> Bob,
>
> << Lookup TABLES are GOOD.
> Lookup FIELDS are BAD. >>
>
> I agree with Charlie.  I still don't see why this is so.  For a field that
> has a discreet list of items where "Limit to List" is set to "Yes", and
> the rowsource of the list is set to table/query (not value list), lookup
> fields are GOOD.  If you set up a lookup field in a table, then when
> creating forms, that field will always appear as a combobox with the
> proper list items as the rowsource... always.  You set it once at the
> table and each and every form will be autoformatted.  No need to do it
> each time.
>
> Even a lookup field in a table with a value list as a rowsource would
> still facilitate form building.  However, I tend not to use value list
> unless the choices are universal and final, e.g. the dropdown has units of
> measure for temperature and the only choices you want EVER are =B0C and 
=B0F.
>
> So, now I'm curious.  What's SO bad about lookup fields?
>
> Greg
>

I wasn't certain how to spell R=E9aumur!

It's listed as historically significant.  Only a student of the history 
of
science would know such trivia!

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...]
Sent: Wednesday, December 18, 2002 11:35
To: Access
Subject: [access] Re: What are Lookup fields ?

And the ever popular R=E9aumur temperature scale?

Let's not be an exclusive club!

C




> e.g. the dropdown has units of measure for temperature and the only
choices
> you want EVER are =B0C and =B0F.
>
> Hey! What about the Kelvin and Rankine temperature scales!
>
> 8-)
>
> Rand E Gerald
> Database Specialist
> Information Services / Operations
> Bah=E1'=ED National Office
> 1233 Central St.
> Evanston IL 60201
> (xxx) xxx-xxxx
>
> -----Original Message-----
> From: Gregory Serrano [mailto:SerranoG@m...]
[mailto:SerranoG@m...]]
> Sent: Wednesday, December 18, 2002 07:49
> To: Access
> Subject: [access] Re: What are Lookup fields ?
>
> Bob,
>
> << Lookup TABLES are GOOD.
> Lookup FIELDS are BAD. >>
>
> I agree with Charlie.  I still don't see why this is so.  For a field 
that
> has a discreet list of items where "Limit to List" is set to "Yes", 
and
> the rowsource of the list is set to table/query (not value list), 
lookup
> fields are GOOD.  If you set up a lookup field in a table, then when
> creating forms, that field will always appear as a combobox with the
> proper list items as the rowsource... always.  You set it once at the
> table and each and every form will be autoformatted.  No need to do 
it
> each time.
>
> Even a lookup field in a table with a value list as a rowsource would
> still facilitate form building.  However, I tend not to use value 
list
> unless the choices are universal and final, e.g. the dropdown has 
units of
> measure for temperature and the only choices you want EVER are =B0C 
and =B0F.
>
> So, now I'm curious.  What's SO bad about lookup fields?
>
> Greg
>

OK, lookup fields aren?t the creation of the Evil One.

Paper clip guy is the creation of the Evil One!!

Anyway, here?s my thought, beyond agreeing with Dave?s important points?

In an Access-centric universe, there is probably not a whole lot wrong
with lookup fields. They do "facilitate form building"...in an Access-
centric universe. But their use tends to blur an important
distinction between:

a) the database, and
b) the database management system (DBMS)

Strictly speaking, a well-designed database consists of the physical
tables; the defined views, queries and stored procedures; and the rules
the database engine will enforce to protect the data. The database per
se does not include the application, the implementation, which consists
of the forms and reports the user interacts with, and which are always
DBMS specific. I like to keep my databases ?pure?, i.e., as free as
possible from DBMS-specific features, like lookup fields.  This
facilitates building front-ends for my Access databases with front-end
development tools other than Access.

Starting with Access 97, Microsoft began to uncouple the Access front-
end tools from the Jet database engine. This is a good thing. It
enables developers to attach other types of front-ends to Access
databases. Yes, lookup fields automatically appear as combo boxes on
Access forms. But Visual Basic, HTML and ASP don?t know anything about
them. They are a feature of the implementation, not of the database per
se, which is the simple combination of database structure and data.  So
I try to ignore implementation-specific considerations as much as
possible when designing any database. That way I end up with well-
designed databases that are completely ?indifferent? to the application
(Access, VB, ASP, HTML, plus a zillion others) that implements them.
When it comes to table design, less is more.  Embedding virtual tables
in table field properties (which is what lookup fields are):

a) is questionable from a relational database design best practices
   perspective

b) is superfluous when using front-end development tools other than
   Access

c) risks introducing unnecessary programming errors into applications

Anyway?

Since Dev started this whole discussion, I went to Dev and I
says, ?Dev, what do ya? mean ?dem lookup fields is the creation of
Beelzebub, the ruler of the demons himself. That?s mighty strong
language Dev, ain?t it.?

And Dev gets back to me and says:

?Primary reason being that a lookup field does not display the stored
value, making it hard for developers to go in afterwards and see a
relationship established at table-level when it should be handled on
the form with a lookup for the id.?

-- Dev

Pretty lame reason for invoking Satan, Dev. But to each his own?




>From: "Gregory Serrano" <SerranoG@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Wed, 18 Dec 2002 13:49:17
>
>Bob,
>
><< Lookup TABLES are GOOD.
>Lookup FIELDS are BAD. >>
>
>I agree with Charlie.  I still don't see why this is so.  For a field that
>has a discreet list of items where "Limit to List" is set to "Yes", and
>the rowsource of the list is set to table/query (not value list), lookup
>fields are GOOD.  If you set up a lookup field in a table, then when
>creating forms, that field will always appear as a combobox with the
>proper list items as the rowsource... always.  You set it once at the
>table and each and every form will be autoformatted.  No need to do it
>each time.
>
>Even a lookup field in a table with a value list as a rowsource would
>still facilitate form building.  However, I tend not to use value list
>unless the choices are universal and final, e.g. the dropdown has units of
>measure for temperature and the only choices you want EVER are °C and °F.
>
>So, now I'm curious.  What's SO bad about lookup fields?
>
>Greg


_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE* 
http://join.msn.com/?page=features/junkmail

Bob Bedell wrote:
...(snip)...
> Paper clip guy is the creation of the Evil One!!
> 
I use the Genius; he gives much better answers than Clippy. Besides, he 
demonstrates relativity by sneezing and blowing himself off the desktop.

Paul

I realize I'm probably beating a dead horse here, but it's
kinda' interesting how some folks can get a little emotional
about this lookup field issue. The following are informative
and at times a bit entertaining:

Assorted John Vinson rants (MVP) on microsoft forums:

"My biggest objection to Lookup fields is that they conceal the
relational nature of the data. When you look at a table - you
should *see what is in the table*.  That's not what you see when
you put combo boxes in the table datasheet!  I've just seen far
too many questions on this forum from people who simply don't
understand tables and relationships, *because* they have been
seduced by this misfeature."

"In my opinion - and I have no experience with the performance
issue - the use of Lookup fields in table datasheet view has far
more problems than benefits, and I *never* use them and try to
persuade everyone to avoid them too.  Just one example: each
Lookup field creates a Relationship with its associated indices,
*even if those indices already exist*!  This expands the database
and degrades update performance. Zap them, is my advice."

>Does anyone have an opinion as to whether or not it is a good or bad idea 
>to create lookup fields directly in your tables rather than setting them up 
>in forms?

"Have you ever dangled a tasty sardine in front of a hungry cat?
Yes. I have a vigorous opinion of putting lookup fields in
tables. Unless you have a *rock solid* understanding of relational
theory and the detailed structure of your database, it is A Very Bad
Idea. It gets you very little - it makes your table datasheet view more
human readable, at the cost of concealing the actual contents of
the table, and making sorting and searching the table more difficult;
it also makes it a tiny bit easier to put combo boxes on forms
instead of textboxes.  In addition, the Lookup Wizard creates
relationships and new indexes on your tables, *even if such indexes
already exist*, wasting space, using up your 32-index limit, and making
updating less efficient. But I advise people never to use this
misfeature; just put the combo boxes on Forms, which is what you should
be using to look at your data anyway. Table datasheets are for
debugging; and as such they should display what they actually contain
rather than concealing it."


>Using a lookup field is it possible to get Access2000 to use the
>values in the field on is doing the lookup in?

"Well, I *intensely* dislike the so-called "Lookup" field type. It
merely conceals the data in your table behind a value from
another table. I recommend that it NEVER be used; you can very easily
store your tables with the actual data that they contain visible, and
use a Query or a combo box or other control on a Form or Report to
retrieve data from the linked ("lookup", if that's what you want to
call it) table. What I object to is the perverse idea that you should
define a lookup *FIELD* in your main table, and use it in your table
datasheet. This accomplishes absolutely nothing other than to conceal
the actual contents of your table, cause confusion about the relational
structure of your data, and mislead users into trying to do data entry
and searching in table datasheets instead of properly doing so on
Forms."

"It's perfectly easy to put a Combo Box - a "lookup", if you want
to call it that - on a Form. The table lookup wizard makes this the
default, but at the cost of concealing the actual contents of
your table; encouraging you to use table datasheets for data entry,
almost never a good idea; creating a new relationship between the main
table and the lookup table even if such a relationship already exists;
and adding redundant indexes to your table. If you leave the tables
alone, and use a Form to do your data entry, you get the best of both
worlds. Use table datasheets for debugging and design ONLY, and use the
Form and the rich kit of tools that the Form provides for your data
entry."

"You're yet another victim of Microsoft's misleading, misdesigned, and
all but useless so-called "Lookup" feature.  The DealID *IS* what's in
your table; the Lookup is merely concealing that fact from you. Base
your Report, not directly on the Table, but on a query joining your
Deal table to the main table by DealID. This gives you the DealName as
a field in the query, and you can display it on your report."

"Joan has one of the major ones - when you look at a table containing
Lookup fields, you do not see what is actually in the table. It's
disguised.  This causes sorting, searching and linking to be VERY
confusing and usually wrong. Another issue is that every Lookup field
creates a new, hidden Relationship between the tables involved,
including creating new indices in both tables - *even if a relationship
and indices already exist*. This wastes space and slows updates and
provides absolutely NO benefit. A subtle problem is that it discourages
people just learning Access from dealing with and understanding the
relational nature of their tables. It "magically" brings in linked
fields - in a black-box manner - so that the user has no incentive to
understand the relational linkages.  I've seen many, many messages here
from people who put in lookup fields and ended up getting thoroughly
mixed up about the structure of their own database."

"The modest but real savings in convenience in creating forms is, in my
opinion, heavily outweighed by the confusion caused to people just
trying to learn Access. In my experience, using a lookup field in a
table encourages the bad habit of using table datasheets for data
entry and viewing, and is a barrier to learning how relational
databases work."







>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Wed, 18 Dec 2002 19:25:50 +0000
>
>OK, lookup fields aren?t the creation of the Evil One.
>
>Paper clip guy is the creation of the Evil One!!
>
>Anyway, here?s my thought, beyond agreeing with Dave?s important points?
>
>In an Access-centric universe, there is probably not a whole lot wrong
>with lookup fields. They do "facilitate form building"...in an Access-
>centric universe. But their use tends to blur an important
>distinction between:
>
>a) the database, and
>b) the database management system (DBMS)
>
>Strictly speaking, a well-designed database consists of the physical
>tables; the defined views, queries and stored procedures; and the rules
>the database engine will enforce to protect the data. The database per
>se does not include the application, the implementation, which consists
>of the forms and reports the user interacts with, and which are always
>DBMS specific. I like to keep my databases ?pure?, i.e., as free as
>possible from DBMS-specific features, like lookup fields.  This
>facilitates building front-ends for my Access databases with front-end
>development tools other than Access.
>
>Starting with Access 97, Microsoft began to uncouple the Access front-
>end tools from the Jet database engine. This is a good thing. It
>enables developers to attach other types of front-ends to Access
>databases. Yes, lookup fields automatically appear as combo boxes on
>Access forms. But Visual Basic, HTML and ASP don?t know anything about
>them. They are a feature of the implementation, not of the database per
>se, which is the simple combination of database structure and data.  So
>I try to ignore implementation-specific considerations as much as
>possible when designing any database. That way I end up with well-
>designed databases that are completely ?indifferent? to the application
>(Access, VB, ASP, HTML, plus a zillion others) that implements them.
>When it comes to table design, less is more.  Embedding virtual tables
>in table field properties (which is what lookup fields are):
>
>a) is questionable from a relational database design best practices
>   perspective
>
>b) is superfluous when using front-end development tools other than
>   Access
>
>c) risks introducing unnecessary programming errors into applications
>
>Anyway?
>
>Since Dev started this whole discussion, I went to Dev and I
>says, ?Dev, what do ya? mean ?dem lookup fields is the creation of
>Beelzebub, the ruler of the demons himself. That?s mighty strong
>language Dev, ain?t it.?
>
>And Dev gets back to me and says:
>
>?Primary reason being that a lookup field does not display the stored
>value, making it hard for developers to go in afterwards and see a
>relationship established at table-level when it should be handled on
>the form with a lookup for the id.?
>
>-- Dev
>
>Pretty lame reason for invoking Satan, Dev. But to each his own?
>
>
>
>
>>From: "Gregory Serrano" <SerranoG@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: What are Lookup fields ?
>>Date: Wed, 18 Dec 2002 13:49:17
>>
>>Bob,
>>
>><< Lookup TABLES are GOOD.
>>Lookup FIELDS are BAD. >>
>>
>>I agree with Charlie.  I still don't see why this is so.  For a field that
>>has a discreet list of items where "Limit to List" is set to "Yes", and
>>the rowsource of the list is set to table/query (not value list), lookup
>>fields are GOOD.  If you set up a lookup field in a table, then when
>>creating forms, that field will always appear as a combobox with the
>>proper list items as the rowsource... always.  You set it once at the
>>table and each and every form will be autoformatted.  No need to do it
>>each time.
>>
>>Even a lookup field in a table with a value list as a rowsource would
>>still facilitate form building.  However, I tend not to use value list
>>unless the choices are universal and final, e.g. the dropdown has units of
>>measure for temperature and the only choices you want EVER are °C and °F.
>>
>>So, now I'm curious.  What's SO bad about lookup fields?
>>
>>Greg
>
>
>_________________________________________________________________
>The new MSN 8: advanced junk mail protection and 2 months FREE* 
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to 
>unsubscribe send a blank email to 


_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. 
http://join.msn.com/?page=features/virus

Thanks Bob!  That's saved me a ****-load of reading as I figured I'd have to
read this thread since it's been getting so much traffic!

Must admit, I never even new they existed and I've been using Access
professionally since Access V1.1!

I think most people have pretty much summed up the drawbacks. The main one
that comes to my mind, and is quite a good response/explanation for quite a
number of different issues we see on this list, and it relates to relational
theory:

"We are not meant to view / understand data straight from a table."  That's
what Queries / Forms / Reports / Front-ends etc are for.

Cheers,
Andrew

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Thursday, 19 December 2002 12:50 PM
To: Access
Subject: [access] Re: What are Lookup fields ?


I realize I'm probably beating a dead horse here, but it's
kinda' interesting how some folks can get a little emotional
about this lookup field issue. The following are informative
and at times a bit entertaining:

Assorted John Vinson rants (MVP) on microsoft forums:

"My biggest objection to Lookup fields is that they conceal the
relational nature of the data. When you look at a table - you
should *see what is in the table*.  That's not what you see when
you put combo boxes in the table datasheet!  I've just seen far
too many questions on this forum from people who simply don't
understand tables and relationships, *because* they have been
seduced by this misfeature."

"In my opinion - and I have no experience with the performance
issue - the use of Lookup fields in table datasheet view has far
more problems than benefits, and I *never* use them and try to
persuade everyone to avoid them too.  Just one example: each
Lookup field creates a Relationship with its associated indices,
*even if those indices already exist*!  This expands the database
and degrades update performance. Zap them, is my advice."

>Does anyone have an opinion as to whether or not it is a good or bad idea 
>to create lookup fields directly in your tables rather than setting them up

>in forms?

"Have you ever dangled a tasty sardine in front of a hungry cat?
Yes. I have a vigorous opinion of putting lookup fields in
tables. Unless you have a *rock solid* understanding of relational
theory and the detailed structure of your database, it is A Very Bad
Idea. It gets you very little - it makes your table datasheet view more
human readable, at the cost of concealing the actual contents of
the table, and making sorting and searching the table more difficult;
it also makes it a tiny bit easier to put combo boxes on forms
instead of textboxes.  In addition, the Lookup Wizard creates
relationships and new indexes on your tables, *even if such indexes
already exist*, wasting space, using up your 32-index limit, and making
updating less efficient. But I advise people never to use this
misfeature; just put the combo boxes on Forms, which is what you should
be using to look at your data anyway. Table datasheets are for
debugging; and as such they should display what they actually contain
rather than concealing it."


>Using a lookup field is it possible to get Access2000 to use the
>values in the field on is doing the lookup in?

"Well, I *intensely* dislike the so-called "Lookup" field type. It
merely conceals the data in your table behind a value from
another table. I recommend that it NEVER be used; you can very easily
store your tables with the actual data that they contain visible, and
use a Query or a combo box or other control on a Form or Report to
retrieve data from the linked ("lookup", if that's what you want to
call it) table. What I object to is the perverse idea that you should
define a lookup *FIELD* in your main table, and use it in your table
datasheet. This accomplishes absolutely nothing other than to conceal
the actual contents of your table, cause confusion about the relational
structure of your data, and mislead users into trying to do data entry
and searching in table datasheets instead of properly doing so on
Forms."

"It's perfectly easy to put a Combo Box - a "lookup", if you want
to call it that - on a Form. The table lookup wizard makes this the
default, but at the cost of concealing the actual contents of
your table; encouraging you to use table datasheets for data entry,
almost never a good idea; creating a new relationship between the main
table and the lookup table even if such a relationship already exists;
and adding redundant indexes to your table. If you leave the tables
alone, and use a Form to do your data entry, you get the best of both
worlds. Use table datasheets for debugging and design ONLY, and use the
Form and the rich kit of tools that the Form provides for your data
entry."

"You're yet another victim of Microsoft's misleading, misdesigned, and
all but useless so-called "Lookup" feature.  The DealID *IS* what's in
your table; the Lookup is merely concealing that fact from you. Base
your Report, not directly on the Table, but on a query joining your
Deal table to the main table by DealID. This gives you the DealName as
a field in the query, and you can display it on your report."

"Joan has one of the major ones - when you look at a table containing
Lookup fields, you do not see what is actually in the table. It's
disguised.  This causes sorting, searching and linking to be VERY
confusing and usually wrong. Another issue is that every Lookup field
creates a new, hidden Relationship between the tables involved,
including creating new indices in both tables - *even if a relationship
and indices already exist*. This wastes space and slows updates and
provides absolutely NO benefit. A subtle problem is that it discourages
people just learning Access from dealing with and understanding the
relational nature of their tables. It "magically" brings in linked
fields - in a black-box manner - so that the user has no incentive to
understand the relational linkages.  I've seen many, many messages here
from people who put in lookup fields and ended up getting thoroughly
mixed up about the structure of their own database."

"The modest but real savings in convenience in creating forms is, in my
opinion, heavily outweighed by the confusion caused to people just
trying to learn Access. In my experience, using a lookup field in a
table encourages the bad habit of using table datasheets for data
entry and viewing, and is a barrier to learning how relational
databases work."







>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Wed, 18 Dec 2002 19:25:50 +0000
>
>OK, lookup fields aren't the creation of the Evil One.
>
>Paper clip guy is the creation of the Evil One!!
>
>Anyway, here's my thought, beyond agreeing with Dave's important points...
>
>In an Access-centric universe, there is probably not a whole lot wrong
>with lookup fields. They do "facilitate form building"...in an Access-
>centric universe. But their use tends to blur an important
>distinction between:
>
>a) the database, and
>b) the database management system (DBMS)
>
>Strictly speaking, a well-designed database consists of the physical
>tables; the defined views, queries and stored procedures; and the rules
>the database engine will enforce to protect the data. The database per
>se does not include the application, the implementation, which consists
>of the forms and reports the user interacts with, and which are always
>DBMS specific. I like to keep my databases "pure", i.e., as free as
>possible from DBMS-specific features, like lookup fields.  This
>facilitates building front-ends for my Access databases with front-end
>development tools other than Access.
>
>Starting with Access 97, Microsoft began to uncouple the Access front-
>end tools from the Jet database engine. This is a good thing. It
>enables developers to attach other types of front-ends to Access
>databases. Yes, lookup fields automatically appear as combo boxes on
>Access forms. But Visual Basic, HTML and ASP don't know anything about
>them. They are a feature of the implementation, not of the database per
>se, which is the simple combination of database structure and data.  So
>I try to ignore implementation-specific considerations as much as
>possible when designing any database. That way I end up with well-
>designed databases that are completely "indifferent" to the application
>(Access, VB, ASP, HTML, plus a zillion others) that implements them.
>When it comes to table design, less is more.  Embedding virtual tables
>in table field properties (which is what lookup fields are):
>
>a) is questionable from a relational database design best practices
>   perspective
>
>b) is superfluous when using front-end development tools other than
>   Access
>
>c) risks introducing unnecessary programming errors into applications
>
>Anyway...
>
>Since Dev started this whole discussion, I went to Dev and I
>says, "Dev, what do ya' mean 'dem lookup fields is the creation of
>Beelzebub, the ruler of the demons himself. That's mighty strong
>language Dev, ain't it."
>
>And Dev gets back to me and says:
>
>"Primary reason being that a lookup field does not display the stored
>value, making it hard for developers to go in afterwards and see a
>relationship established at table-level when it should be handled on
>the form with a lookup for the id."
>
>-- Dev
>
>Pretty lame reason for invoking Satan, Dev. But to each his own...
>
>
>
>
>>From: "Gregory Serrano" <SerranoG@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: What are Lookup fields ?
>>Date: Wed, 18 Dec 2002 13:49:17
>>
>>Bob,
>>
>><< Lookup TABLES are GOOD.
>>Lookup FIELDS are BAD. >>
>>
>>I agree with Charlie.  I still don't see why this is so.  For a field that
>>has a discreet list of items where "Limit to List" is set to "Yes", and
>>the rowsource of the list is set to table/query (not value list), lookup
>>fields are GOOD.  If you set up a lookup field in a table, then when
>>creating forms, that field will always appear as a combobox with the
>>proper list items as the rowsource... always.  You set it once at the
>>table and each and every form will be autoformatted.  No need to do it
>>each time.
>>
>>Even a lookup field in a table with a value list as a rowsource would
>>still facilitate form building.  However, I tend not to use value list
>>unless the choices are universal and final, e.g. the dropdown has units of
>>measure for temperature and the only choices you want EVER are °C and °F.
>>
>>So, now I'm curious.  What's SO bad about lookup fields?
>>
>>Greg
>
>
>_________________________________________________________________
>The new MSN 8: advanced junk mail protection and 2 months FREE* 
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to 
>unsubscribe send a blank email to 


_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. 
http://join.msn.com/?page=features/virus



IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.

~~~~~~~~
"We are not meant to view / understand data straight from a table."  That's
what Queries / Forms / Reports / Front-ends etc are for."
~~~~~~~~
	I totally agree with this.

enzo c",)?
http://www.tropangwatakwatak.tk

-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Thursday, December 19, 2002 10:42 AM
To: Access
Subject: [access] Re: What are Lookup fields ?


Thanks Bob!  That's saved me a ****-load of reading as I figured I'd have to
read this thread since it's been getting so much traffic!

Must admit, I never even new they existed and I've been using Access
professionally since Access V1.1!

I think most people have pretty much summed up the drawbacks. The main one
that comes to my mind, and is quite a good response/explanation for quite a
number of different issues we see on this list, and it relates to relational
theory:

"We are not meant to view / understand data straight from a table."  That's
what Queries / Forms / Reports / Front-ends etc are for.

Cheers,
Andrew

-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Thursday, 19 December 2002 12:50 PM
To: Access
Subject: [access] Re: What are Lookup fields ?


I realize I'm probably beating a dead horse here, but it's
kinda' interesting how some folks can get a little emotional
about this lookup field issue. The following are informative
and at times a bit entertaining:

Assorted John Vinson rants (MVP) on microsoft forums:

"My biggest objection to Lookup fields is that they conceal the
relational nature of the data. When you look at a table - you
should *see what is in the table*.  That's not what you see when
you put combo boxes in the table datasheet!  I've just seen far
too many questions on this forum from people who simply don't
understand tables and relationships, *because* they have been
seduced by this misfeature."

"In my opinion - and I have no experience with the performance
issue - the use of Lookup fields in table datasheet view has far
more problems than benefits, and I *never* use them and try to
persuade everyone to avoid them too.  Just one example: each
Lookup field creates a Relationship with its associated indices,
*even if those indices already exist*!  This expands the database
and degrades update performance. Zap them, is my advice."

>Does anyone have an opinion as to whether or not it is a good or bad idea
>to create lookup fields directly in your tables rather than setting them up

>in forms?

"Have you ever dangled a tasty sardine in front of a hungry cat?
Yes. I have a vigorous opinion of putting lookup fields in
tables. Unless you have a *rock solid* understanding of relational
theory and the detailed structure of your database, it is A Very Bad
Idea. It gets you very little - it makes your table datasheet view more
human readable, at the cost of concealing the actual contents of
the table, and making sorting and searching the table more difficult;
it also makes it a tiny bit easier to put combo boxes on forms
instead of textboxes.  In addition, the Lookup Wizard creates
relationships and new indexes on your tables, *even if such indexes
already exist*, wasting space, using up your 32-index limit, and making
updating less efficient. But I advise people never to use this
misfeature; just put the combo boxes on Forms, which is what you should
be using to look at your data anyway. Table datasheets are for
debugging; and as such they should display what they actually contain
rather than concealing it."


>Using a lookup field is it possible to get Access2000 to use the
>values in the field on is doing the lookup in?

"Well, I *intensely* dislike the so-called "Lookup" field type. It
merely conceals the data in your table behind a value from
another table. I recommend that it NEVER be used; you can very easily
store your tables with the actual data that they contain visible, and
use a Query or a combo box or other control on a Form or Report to
retrieve data from the linked ("lookup", if that's what you want to
call it) table. What I object to is the perverse idea that you should
define a lookup *FIELD* in your main table, and use it in your table
datasheet. This accomplishes absolutely nothing other than to conceal
the actual contents of your table, cause confusion about the relational
structure of your data, and mislead users into trying to do data entry
and searching in table datasheets instead of properly doing so on
Forms."

"It's perfectly easy to put a Combo Box - a "lookup", if you want
to call it that - on a Form. The table lookup wizard makes this the
default, but at the cost of concealing the actual contents of
your table; encouraging you to use table datasheets for data entry,
almost never a good idea; creating a new relationship between the main
table and the lookup table even if such a relationship already exists;
and adding redundant indexes to your table. If you leave the tables
alone, and use a Form to do your data entry, you get the best of both
worlds. Use table datasheets for debugging and design ONLY, and use the
Form and the rich kit of tools that the Form provides for your data
entry."

"You're yet another victim of Microsoft's misleading, misdesigned, and
all but useless so-called "Lookup" feature.  The DealID *IS* what's in
your table; the Lookup is merely concealing that fact from you. Base
your Report, not directly on the Table, but on a query joining your
Deal table to the main table by DealID. This gives you the DealName as
a field in the query, and you can display it on your report."

"Joan has one of the major ones - when you look at a table containing
Lookup fields, you do not see what is actually in the table. It's
disguised.  This causes sorting, searching and linking to be VERY
confusing and usually wrong. Another issue is that every Lookup field
creates a new, hidden Relationship between the tables involved,
including creating new indices in both tables - *even if a relationship
and indices already exist*. This wastes space and slows updates and
provides absolutely NO benefit. A subtle problem is that it discourages
people just learning Access from dealing with and understanding the
relational nature of their tables. It "magically" brings in linked
fields - in a black-box manner - so that the user has no incentive to
understand the relational linkages.  I've seen many, many messages here
from people who put in lookup fields and ended up getting thoroughly
mixed up about the structure of their own database."

"The modest but real savings in convenience in creating forms is, in my
opinion, heavily outweighed by the confusion caused to people just
trying to learn Access. In my experience, using a lookup field in a
table encourages the bad habit of using table datasheets for data
entry and viewing, and is a barrier to learning how relational
databases work."







>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: What are Lookup fields ?
>Date: Wed, 18 Dec 2002 19:25:50 +0000
>
>OK, lookup fields aren't the creation of the Evil One.
>
>Paper clip guy is the creation of the Evil One!!
>
>Anyway, here's my thought, beyond agreeing with Dave's important points...
>
>In an Access-centric universe, there is probably not a whole lot wrong
>with lookup fields. They do "facilitate form building"...in an Access-
>centric universe. But their use tends to blur an important
>distinction between:
>
>a) the database, and
>b) the database management system (DBMS)
>
>Strictly speaking, a well-designed database consists of the physical
>tables; the defined views, queries and stored procedures; and the rules
>the database engine will enforce to protect the data. The database per
>se does not include the application, the implementation, which consists
>of the forms and reports the user interacts with, and which are always
>DBMS specific. I like to keep my databases "pure", i.e., as free as
>possible from DBMS-specific features, like lookup fields.  This
>facilitates building front-ends for my Access databases with front-end
>development tools other than Access.
>
>Starting with Access 97, Microsoft began to uncouple the Access front-
>end tools from the Jet database engine. This is a good thing. It
>enables developers to attach other types of front-ends to Access
>databases. Yes, lookup fields automatically appear as combo boxes on
>Access forms. But Visual Basic, HTML and ASP don't know anything about
>them. They are a feature of the implementation, not of the database per
>se, which is the simple combination of database structure and data.  So
>I try to ignore implementation-specific considerations as much as
>possible when designing any database. That way I end up with well-
>designed databases that are completely "indifferent" to the application
>(Access, VB, ASP, HTML, plus a zillion others) that implements them.
>When it comes to table design, less is more.  Embedding virtual tables
>in table field properties (which is what lookup fields are):
>
>a) is questionable from a relational database design best practices
>   perspective
>
>b) is superfluous when using front-end development tools other than
>   Access
>
>c) risks introducing unnecessary programming errors into applications
>
>Anyway...
>
>Since Dev started this whole discussion, I went to Dev and I
>says, "Dev, what do ya' mean 'dem lookup fields is the creation of
>Beelzebub, the ruler of the demons himself. That's mighty strong
>language Dev, ain't it."
>
>And Dev gets back to me and says:
>
>"Primary reason being that a lookup field does not display the stored
>value, making it hard for developers to go in afterwards and see a
>relationship established at table-level when it should be handled on
>the form with a lookup for the id."
>
>-- Dev
>
>Pretty lame reason for invoking Satan, Dev. But to each his own...
>
>
>
>
>>From: "Gregory Serrano" <SerranoG@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: What are Lookup fields ?
>>Date: Wed, 18 Dec 2002 13:49:17
>>
>>Bob,
>>
>><< Lookup TABLES are GOOD.
>>Lookup FIELDS are BAD. >>
>>
>>I agree with Charlie.  I still don't see why this is so.  For a field that
>>has a discreet list of items where "Limit to List" is set to "Yes", and
>>the rowsource of the list is set to table/query (not value list), lookup
>>fields are GOOD.  If you set up a lookup field in a table, then when
>>creating forms, that field will always appear as a combobox with the
>>proper list items as the rowsource... always.  You set it once at the
>>table and each and every form will be autoformatted.  No need to do it
>>each time.
>>
>>Even a lookup field in a table with a value list as a rowsource would
>>still facilitate form building.  However, I tend not to use value list
>>unless the choices are universal and final, e.g. the dropdown has units of
>>measure for temperature and the only choices you want EVER are °C and °F.
>>
>>So, now I'm curious.  What's SO bad about lookup fields?
>>
>>Greg
>
>
>_________________________________________________________________
>The new MSN 8: advanced junk mail protection and 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to 


_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus



IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.

To sum up, what I think I'm hearing from this discussion is that for the 
small office that deals with small amounts of data, that look-up fields 
don't really do any great harm; however, when you graduate to the big 
leagues with front ends, back ends, SQL servers, VB6, networks, and tens 
of thousands to millions of pieces of data, look-up fields:

1) create unnecessary and slowing effects on data relationships;
2) muddy the tables with extra and unneeded "extras" that should be dealt
   with in queries, forms, and reports;
3) promote the bad habit of data entry in tables instead of forms.

Thanks for the enlightenment.  I'm in that position where I'm approaching 
graduation to the "big leagues" so this info is helpful.

Greg

I have been following this thread with interest, because I didn't know what
a lookup field was. Now I (think) I know, its when you have a combo box in a
table?  Actually I've come across some of those without realising what they
were, and they confused me totally, so I avoid them like the plague.
Now the business of The Genius and his demonstration of relativity, thats
more like it!

Seasons greetings everyone, Wesley Kendrick

----- Original Message -----
From: "Paul Streeter" <PStreeter@C...>
To: "Access" <access@p...>
Sent: Wednesday, December 18, 2002 7:59 PM
Subject: [access] Re: What are Lookup fields ?


> Bob Bedell wrote:
> ...(snip)...
> > Paper clip guy is the creation of the Evil One!!
> >
> I use the Genius; he gives much better answers than Clippy. Besides, he
> demonstrates relativity by sneezing and blowing himself off the desktop.
>
> Paul
>
>
>
>

Or someone using a piece of software for converting units and constants....
   

C




> I wasn't certain how to spell R=E9aumur!
> 
> It's listed as historically significant.  Only a student of the history of
> science would know such trivia!
> 
> Rand E Gerald
> Database Specialist
> Information Services / Operations
> Bah=E1'=ED National Office
> 1233 Central St.
> Evanston IL 60201
> (xxx) xxx-xxxx
> 
> -----Original Message-----
> From: Charlie Goodwin [mailto:cgoodwin@c...] [mailto:cgoodwin@c...
net.com]]
> Sent: Wednesday, December 18, 2002 11:35
> To: Access
> Subject: [access] Re: What are Lookup fields ?
> 
> And the ever popular R=E9aumur temperature scale?
> 
> Let's not be an exclusive club!
> 
> C
> 
> 
> 
> 
> > e.g. the dropdown has units of measure for temperature and the only
> choices
> > you want EVER are =B0C and =B0F.
> >
> > Hey! What about the Kelvin and Rankine temperature scales!
> >
> > 8-)
> >
> > Rand E Gerald
> > Database Specialist
> > Information Services / Operations
> > Bah=E1'=ED National Office
> > 1233 Central St.
> > Evanston IL 60201
> > (xxx) xxx-xxxx
> >
> > -----Original Message-----
> > From: Gregory Serrano [mailto:SerranoG@m...] [mailto:SerranoG@m...
ichigan.gov]]
> [mailto:SerranoG@m...]] [mailto:SerranoG@m...]]]
> > Sent: Wednesday, December 18, 2002 07:49
> > To: Access
> > Subject: [access] Re: What are Lookup fields ?
> >
> > Bob,
> >
> > << Lookup TABLES are GOOD.
> > Lookup FIELDS are BAD. >>
> >
> > I agree with Charlie.  I still don't see why this is so.  For a field t
hat
> > has a discreet list of items where "Limit to List" is set to "Yes", and
> > the rowsource of the list is set to table/query (not value list), lookup
> > fields are GOOD.  If you set up a lookup field in a table, then when
> > creating forms, that field will always appear as a combobox with the
> > proper list items as the rowsource... always.  You set it once at the
> > table and each and every form will be autoformatted.  No need to do it
> > each time.
> >
> > Even a lookup field in a table with a value list as a rowsource would
> > still facilitate form building.  However, I tend not to use value list
> > unless the choices are universal and final, e.g. the dropdown has units
 of
> > measure for temperature and the only choices you want EVER are =B0C and
 =B0F.
> >
> > So, now I'm curious.  What's SO bad about lookup fields?
> >
> > Greg
> >
> 
> 

If this thread could support fancy internet gop features like animated stuf
f...you would now be seeing a white flag waving nervously back and forth....

In the thankful absence of that, may I redeem myself by promising, no, sole
mnly swearing, on a stack of VBA texts, that I will NEVER, EVER, EVER use a
 lookup field on any app that has the slightest chance of being distributed
 or used in any practical manner?

In fact I will probably never create one again; not even as an experiment t
o be immediately deleted.

I had no idea that it was creating unwanted extra indexes and stuff behind 
the scenes.   I HATE when software doesn't warn me when it is surreptitious
ly doing stuff I don't ask for.

I also HATE the nasty smarmy animated xxxxxxx characters like the paperclip
 and it's siblings.

Am I redeemed?

Charlie




> I have been following this thread with interest, because I didn't know wh
at
> a lookup field was. Now I (think) I know, its when you have a combo box i
n a
> table?  Actually I've come across some of those without realizing what th
ey
> were, and they confused me totally, so I avoid them like the plague.
> Now the business of The Genius and his demonstration of relativity, thats
> more like it!
>
> Seasons greetings everyone, Wesley Kendrick
>
> ----- Original Message -----
> From: "Paul Streeter" <PStreeter@C...>
> To: "Access" <access@p...>
> Sent: Wednesday, December 18, 2002 7:59 PM
> Subject: [access] Re: What are Lookup fields ?
>
>
> > Bob Bedell wrote:
> > ...(snip)...
> > > Paper clip guy is the creation of the Evil One!!
> > >
> > I use the Genius; he gives much better answers than Clippy. Besides, he
> > demonstrates relativity by sneezing and blowing himself off the desktop.
> >
> > Paul
> >
> >
> >
> >
>
>