By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

The Second Commandment (of Access)

P: n/a
I just browsed through some of my Access links when I came across the
Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the points
I heartily agree with (and wish that my predecessor had followed) but
-- alas -- being a relative beginner to Access, I can't see the
reasoning behind one of the points and the site does not provide any
rationale / explanation for its presence either:

2. Thou shalt never allow thy users to see or edit tables directly,
but only through forms and thou shalt abhor the use of "Lookup Fields"
which art the creation of the Evil One.

I always thought Lookup Fields were very convenient. They don't seem
to alter the data structure and seem to serve as a shortcut to having
to open up a second table to look up values. I don't see why they
should be shunned.

Do most developers here adhere strictly to these guidelines? Are
there exceptions where violating the commandments would be okay?

Thanks in advance for any input on this matter.
Nov 12 '05 #1
Share this Question
Share on Google+
30 Replies


P: n/a
"Andante.in.Blue" <05********@sneakemail.com> wrote in message
news:bc*************************@posting.google.co m...
I just browsed through some of my Access links when I came across the
Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the points
I heartily agree with (and wish that my predecessor had followed) but
-- alas -- being a relative beginner to Access, I can't see the
reasoning behind one of the points and the site does not provide any
rationale / explanation for its presence either:

2. Thou shalt never allow thy users to see or edit tables directly,
but only through forms and thou shalt abhor the use of "Lookup Fields"
which art the creation of the Evil One.

I always thought Lookup Fields were very convenient. They don't seem
to alter the data structure and seem to serve as a shortcut to having
to open up a second table to look up values. I don't see why they
should be shunned.

Do most developers here adhere strictly to these guidelines? Are
there exceptions where violating the commandments would be okay?


IMO whatever usefulness a Lookup field might have is only when one is using the table
directly as an interface and there is simply no reason to ever do that. It's like
having a steering wheel in the back seat of your car. It might work, but steering
from back there would still be a stupid thing to do.

One of the biggest problems with Lookup fields at the table level is with newer users
who don't understand that what they *see* when they look at the Lookup field is not
what the table actually has stored. This causes much confusion when they later build
a query against that table and don't get the values they were expecting.
Nov 12 '05 #2

P: n/a
> One of the biggest problems with Lookup fields at the table level is with
newer users
who don't understand that what they *see* when they look at the Lookup field is not what the table actually has stored. This causes much confusion when they later build a query against that table and don't get the values they were expecting.


Believe me, this goes beyond irking a "newer" user. I, too, am occasionally
thrown off by this scenario when asked to troubleshoot a problem or modify an
object in a client's project (there's nothing like *discovering* little "treats"
like this after my work has begun <g>). Defining a "Lookup" field at the table
level is okay during the design phase (it does offer some benefits when building
forms), but when the designing is complete, set the "display control" for the
field back to "textbox" so that the value displayed is the value stored.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #3

P: n/a
FWIW, I absolutely cannot stand lookups, for the exact reasons stated
- say you are unfortunate enough to inherit a database with lookups in
it. Ever try debugging one of those? Looks like it's doing one thing
when it's doing something else. And mucking with what a user sees in
a *table* is totally nuts. The only person with direct access to a
table should be the developer, and he should know better than to use
that junk. Just makes documenting and figuring things out later that
much more difficult.
Nov 12 '05 #4

P: n/a
05********@sneakemail.com (Andante.in.Blue) wrote in
news:bc*************************@posting.google.co m:
I just browsed through some of my Access links when I came across the
Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the points
I heartily agree with (and wish that my predecessor had followed) but
-- alas -- being a relative beginner to Access, I can't see the
reasoning behind one of the points and the site does not provide any
rationale / explanation for its presence either:

2. Thou shalt never allow thy users to see or edit tables directly,
but only through forms and thou shalt abhor the use of "Lookup Fields"
which art the creation of the Evil One.

I always thought Lookup Fields were very convenient. They don't seem
to alter the data structure and seem to serve as a shortcut to having
to open up a second table to look up values. I don't see why they
should be shunned.

Do most developers here adhere strictly to these guidelines? Are
there exceptions where violating the commandments would be okay?

Thanks in advance for any input on this matter.


Actually, if you follow the link behind the phrase "lookup fields"
(http://www.mvps.org/access/lookupfields.htm) you'll see the eight
reasons they feel the way they do about them.

As for the The Ten Commandments of Access, I'd bet that almost all
developers adhere to them. Not because the MVPs laid them down, of
course, but because they're common sense if you're building applications
that others will use or if not, that are going to be at all complicated.
Following them makes for a better product.

Now, you could safely ignore a lot of these if you're building a database
to store info about your celebrity sock collection or whatever, because
it's only you who has to deal with the bad consequences. Foisting bad
consequences on clients is a good way to make "Access Developer" your
former career.

-Carlos
Nov 12 '05 #5

P: n/a
On 23 Sep 2003 10:15:43 -0700, 05********@sneakemail.com (Andante.in.Blue)
wrote:
I just browsed through some of my Access links when I came across the
Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the points
I heartily agree with (and wish that my predecessor had followed) but
-- alas -- being a relative beginner to Access, I can't see the
reasoning behind one of the points and the site does not provide any
rationale / explanation for its presence either:

2. Thou shalt never allow thy users to see or edit tables directly,
but only through forms and thou shalt abhor the use of "Lookup Fields"
which art the creation of the Evil One.

I always thought Lookup Fields were very convenient. They don't seem
to alter the data structure and seem to serve as a shortcut to having
to open up a second table to look up values. I don't see why they
should be shunned.

Do most developers here adhere strictly to these guidelines? Are
there exceptions where violating the commandments would be okay?


Personally, I sometimes use lookups as a convenience for the developer (me)
so I can see what a foreigh key field actually refers to without having to
run a query or open the other table to look at it.

Of course, there is then the problem of also being able to see what the
value actually -is-. I solve that by making my the text column in my
lookup query an expression something like [FooName] & " (" & [FooID] & ")".
Now, the down-side is that this same lookup gets added to combo controls
when I drag fields onto forms, and I have to edit it each time. So anyway,
I guess I waffle back and forth on this.
Nov 12 '05 #6

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<3a********************************@4ax.com>. ..
On 23 Sep 2003 10:15:43 -0700, 05********@sneakemail.com (Andante.in.Blue)
wrote:
I just browsed through some of my Access links when I came across the
Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the points
I heartily agree with (and wish that my predecessor had followed) but
-- alas -- being a relative beginner to Access, I can't see the
reasoning behind one of the points and the site does not provide any
rationale / explanation for its presence either:

2. Thou shalt never allow thy users to see or edit tables directly,
but only through forms and thou shalt abhor the use of "Lookup Fields"
which art the creation of the Evil One.

Could someone tell me what you mean by using "Lookup Fields". Thanks.
Lisa
Nov 12 '05 #7

P: n/a
"Lisa Henkel" <li********@monad.net> wrote in message
news:43**************************@posting.google.c om...
Could someone tell me what you mean by using "Lookup Fields". Thanks.
Lisa


It's essentially the practice of creating ComboBoxes in your table design. Got to
any table in design view and you will have two tabs in the lower portion of the
design grid. The second is named "Lookup".
Nov 12 '05 #8

P: n/a
Thanks for the responses everyone! Very informative. And you are
right, Carlos, I did miss the link on the page ><

So then, the standard practice is to design with the lookup fields
enabled (to make entering sample data in tables easier on the dev),
and then remove them when we are "finished" with the design phase? I
presume compacting the DB will undo any bloating problems the lookup
would have caused? What if we need to come back to the database at a
later point?

I understand that end users should never have to even look at the
tables behind the DB, but my predecessor has left a database that is
so buggy that all the users have regularly resorted to modifying
entries in the tables. I suppose this has led to a complacency among
the users that it's okay to have / need to edit the tables. I suspect
this "knee jerk" behavior will be difficult to remedy. Whenever
something doesn't look right, they will probably jump in the table and
try to fix it. Is there anything I can do about this? (well, other
than fixing the DB so it won't be necessary -- which I'm trying to do
atm)
Nov 12 '05 #9

P: n/a
When you say that, in a look up field, what you see is not what is
actually stored, you are referring to the value that is displayed
rather than the FK being stored? I guess this would be remedied by a
link in the query that you are building. So what you are saying is to
use a query to link up the values and have forms refer to the query
rather than to a table with a lookup field? Or rather, would it be
preferable to have the form refer to the table directly and have the
FK's combobox have a special row source that helps it display a
replacement / complement value?
Nov 12 '05 #10

P: n/a
First, they are not really that bad, but when you run into trouble with
them, you will find that you begin to dislike then very much!

So, I going state that as long as they work for you, the more power
to you!

Now, lets clearly define what those developers are talking about!

Use of the lookup wizard feature at the TABLE DESIGN level

I repeat: lookup feature at the TABLE desing level is the the thing to
advoid.

In other words, you are encouraged to use the combo box wizard in a form.
You are even ok to use the lookup wizard when you build a query (often, many
people don't realize that the lookup wizard also works when you build a
query!). However, since you are in the query builder, it is probably much
nicer to draw a join line, and use the relation ability of the query builder
to pull in that lookup value you need to lookup via a join. (ie: it is much
clear and better to simply drop in the additional tables into the query
builder
instead of using lookups).
(more on this in second)..

So, as a general concept of database designs, you encouraged to pull data
from tables all the time. This idea of pulling values from other tables is
the very essence of a relational database. So, yes, use good designs that
encourage values to be looked up from other tables (but, avoid that lookup
feature at the table level). You build and design tables in a database. You
then join these tables together. However, each table should have it fields,
and when you need to lookup values in other tables, you use sql to do this.

So, lots of relations, and looking up values is encouraged in your designs.

However, at the table level, there is few problems as to why the lookup
wizard feature is bad:

The often quoted reason is that then the novice user will NOT know what is
going on! Golly, that is not the best argument here! After all, we can drive
our car to work, and we don't know what the motor does, or even what it
looks like! That don't mean we should not drive!

I mean, the look up feature is there to help users, and make things easy.

However, here is some REAL pit falls:

First, in any good application design, you will RARELY be editing the data
in the tables directly. So, most of the time, we are talking bout a
continues form, or a standard data entry form. This means the benefits of
the lookup are very much reduced. (in those forms, you can quickly build the
combo box with the combo box wizard, and this is most certainly encouraged).

Anyway, just try and build a simple report based on that table with a lookup
field. The first problem that arises is that the text box on the report will
appear by default as a combo box. That don't look very good. Worse, is now
try and use the sorting and grouping options in the report. You get the
weird situation where in the sorting and grouping option, your report will
sort by the ID value, but display by the look up value! This is sheer
confusing, and it don't make sense.

You will say, golly, why does this thing not sort by the value it displays?
Worse, is how do you in fact sort by the actual looked up value? I mean if
we have a simple list of fruits in a look up table, we probably will want to
sort by the looked value! The problem is that you can NOT sort by the look
up value! The end result is you dump the table lookup feature, and then have
to build a query and join in the actual text value of the fruit from that
table into the reports query anyway!. You then can sort on that! (wow...that
built in lookup feature did not save one bit of work here...did it!).

So, your report will sort by the id value, and displays the by lookup value
unless you dump the use of the lookup. Yuk!

What if you load the data into a reocrdset, then again, what do you get for
the lookup value field? (you get the id value, and again, if you need to get
the value from the other table, you wind up creating a query again! (or
write some code to grab the text value in the lookup table). So, once again,
in code with reocrdsets, the table lookup feature does nothing, and again
tends to surprise the developer as to what value will be returned!. Once
again, you don't save any coding by using this table lookup do you? (it is
ignored in reocrdsets)

Further, lets say later on we decide add another field to our fruits table.
Lets say we need to add the colour of the fruit. You have to realize that
over time, database do change a lot! You must *design* for change!

So, now we just add an additional field to the fruits table called colour.

Now, go back to those reports and drop in the colour field beside the fruit
text! OOPS, can't do that!! Of course, the fruit name is a lookup Value.
However, to display the fruit name AND THE colour, then all of those
queries, reports etc. will now need to be modified to display the colour of
the fruit. And of course, to bring in this additional field of colour, we
CAN NOT use the table lookup feature.

Of course, what is really is the problem here is that will have to build
queries to do this! Something we should have done in the first place!!!!
Now, you got a report with both lookups and a join to this other table.
Well, golly, you might as well dump the lookup, since you have to bring in
the fields from the fruits table via sql joins anyway! If you had built
queries in the first place, then you would just simply plop in this extra
field into the report, and you are done. With the table lookup approach, you
are now stopping in the 12 places in the database, and now building the
queries, or even changing the report source from a table to query. This work
really starts to add up, and bogs you down because of poor design choice in
the first place. We should be able to add this additional field, and not all
of sudden stop dead in our tracks to build queries everywhere.

Worse, it gets messy, as all of a sudden you have to decide if you want dump
the table lookup, as it does not make sense to have both a look up field,
and then some additional fields from that same lookup table brought in via a
sql query join! Yuk!. Worse, after you get knee deep into the project, you
CAN NOT safety remove the lookup field, as then you might introduce bugs and
will have to hunt down every single spot (form, report etc) in the database
that relied on the table lookup feature (that you are now trying to
remove!).
!
So, there is some frustrations in using the lookup. However, if you have not
run into the above problems, then it is most un-fair of me say don't use
them. There is tons of users out there that use and enjoy, and even find
increased productivity from using the table lookup feature. I have to say
that your mileage will vary on this one.

However, due to problems like the above, I do advoid them. Futher, advoiding
them makes you lean the corect skills for other sql database systems you
will use.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

Nov 12 '05 #11

P: n/a
"Rick Brandt" <RB*****@Hunter.Com> wrote in message

" One of the biggest problems with Lookup fields at the table level is
with newer users who don't understand that what they *see* when they
look at the Lookup field is not what the table actually has stored.
This causes much confusion when they later build a query against that
table and don't get the values they were expecting."

If you mean " newer user " to Microsoft Access then probably you are
right.
But if you mean " newer user " to computing as well as MS Access -
like myself - then I'd say you don't see the benefit that a beginner
can gain by using it for a start.

Kind Regards
Nov 12 '05 #12

P: n/a
05********@sneakemail.com (Andante.in.Blue) wrote in
<bc**************************@posting.google.com >:
Thanks for the responses everyone! Very informative. And you are
right, Carlos, I did miss the link on the page ><

So then, the standard practice is to design with the lookup fields
enabled (to make entering sample data in tables easier on the
dev), and then remove them when we are "finished" with the design
phase? . . .
If you want to edit tables in datasheet view, create a query and
put the lookups in the query. All you need to do is right click on
the column you want a lookup on and display the properties sheet.
It has a second tab for the lookup, much like the tab in table
design.
. . . I presume compacting the DB will undo any bloating
problems the lookup would have caused? What if we need to come
back to the database at a later point?
The lookup should never at any point whatsoever be stored in the
table, even for prototyping. Personally, I don't type data into
apps via table view during development or at any other point, so I
can't even conceive of a scenario in which what you are suggesting
would make sense.
I understand that end users should never have to even look at the
tables behind the DB, . . .
Yes.
. . . but my predecessor has left a database that
is so buggy that all the users have regularly resorted to
modifying entries in the tables. I suppose this has led to a
complacency among the users that it's okay to have / need to edit
the tables. I suspect this "knee jerk" behavior will be difficult
to remedy. Whenever something doesn't look right, they will
probably jump in the table and try to fix it. Is there anything I
can do about this? (well, other than fixing the DB so it won't be
necessary -- which I'm trying to do atm)


Replace the table views with queries that replicate the same thing
and have them use those (and prohibit them from using tables
directly), and then start building forms that make it possible for
them to fix the problems that they are resorting to table view to
fix.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13

P: n/a
no****@nospam.nospam (Steve Jorgensen) wrote in
<3a********************************@4ax.com>:
On 23 Sep 2003 10:15:43 -0700, 05********@sneakemail.com
(Andante.in.Blue) wrote:
I just browsed through some of my Access links when I came across
the Ten Commandments of Access
(http://www.mvps.org/access/tencommandments.htm). Some of the
points I heartily agree with (and wish that my predecessor had
followed) but -- alas -- being a relative beginner to Access, I
can't see the reasoning behind one of the points and the site
does not provide any rationale / explanation for its presence
either:

2. Thou shalt never allow thy users to see or edit tables
directly, but only through forms and thou shalt abhor the use of
"Lookup Fields" which art the creation of the Evil One.

I always thought Lookup Fields were very convenient. They don't
seem to alter the data structure and seem to serve as a shortcut
to having to open up a second table to look up values. I don't
see why they should be shunned.

Do most developers here adhere strictly to these guidelines? Are
there exceptions where violating the commandments would be okay?


Personally, I sometimes use lookups as a convenience for the
developer (me) so I can see what a foreigh key field actually
refers to without having to run a query or open the other table to
look at it.

Of course, there is then the problem of also being able to see
what the value actually -is-. I solve that by making my the text
column in my lookup query an expression something like [FooName] &
" (" & [FooID] & ")". Now, the down-side is that this same lookup
gets added to combo controls when I drag fields onto forms, and I
have to edit it each time. So anyway, I guess I waffle back and
forth on this.


In the *table*, Steve? Why not use a saved query for that?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #14

P: n/a
RB*****@Hunter.Com (Rick Brandt) wrote in
<bk************@ID-98015.news.uni-berlin.de>:
"Lisa Henkel" <li********@monad.net> wrote in message
news:43**************************@posting.google. com...
Could someone tell me what you mean by using "Lookup Fields".
Thanks. Lisa


It's essentially the practice of creating ComboBoxes in your table
design. Got to any table in design view and you will have two
tabs in the lower portion of the design grid. The second is named
"Lookup".


And it's a feature that's not needed in table view, because it can
be replicated in queries.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #15

P: n/a
ru****@hotmail.com (kemal) wrote in
<e3**************************@posting.google.com >:
"Rick Brandt" <RB*****@Hunter.Com> wrote in message

" One of the biggest problems with Lookup fields at the table
level is with newer users who don't understand that what they
*see* when they look at the Lookup field is not what the table
actually has stored. This causes much confusion when they later
build a query against that table and don't get the values they
were expecting."

If you mean " newer user " to Microsoft Access then probably you
are right.
But if you mean " newer user " to computing as well as MS Access -
like myself - then I'd say you don't see the benefit that a
beginner can gain by using it for a start.


That benefit can be gotten by using the lookups in queries viewed
in datasheet view, which to an end-user will be indistinguishable
from a table in datasheet view.

So, again, there is simply no need for the lookup in table design,
ever.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #16

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote in news:94007F1CEdfentonbwaynetinvali@
24.168.128.86:
Replace the table views with queries that replicate the same thing
and have them use those (and prohibit them from using tables
directly),


This instruction sounds straightforward enough to be automatable. Anyone ever tried scanning the
tabledef, automatically creating a query that implements the lookup, and removing the lookup from the
tabledef?

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos
Nov 12 '05 #17

P: n/a
ru****@hotmail.com (kemal) wrote in message news:<e3**************************@posting.google. com>...
"Rick Brandt" <RB*****@Hunter.Com> wrote in message

" One of the biggest problems with Lookup fields at the table level is
with newer users who don't understand that what they *see* when they
look at the Lookup field is not what the table actually has stored.
This causes much confusion when they later build a query against that
table and don't get the values they were expecting."

If you mean " newer user " to Microsoft Access then probably you are
right.
But if you mean " newer user " to computing as well as MS Access -
like myself - then I'd say you don't see the benefit that a beginner
can gain by using it for a start.

Kind Regards


Just for fun, try writing a query against the lookup, and you'll see
what drives me nuts about them... what you see definitely ain't what
you get!
Nov 12 '05 #18

P: n/a
"Ross Presser" wrote
... Anyone ever tried scanning the
tabledef, automatically creating a
query that implements the lookup,
and removing the lookup from the
tabledef?


I haven't heard of anyone doing this, but seems perfectly doable to me. As
soon as I learned about the "new, great" lookup field, I immediately
thought, "Violates RDB design. That's a no-no." and have never used one
since, except in a sample database that I used to figure out the answer to a
newsgroup question. Thus, I certainly never had any personal need to
automate getting rid of lookup fields.

Might be a nice thing for someone with some spare time to put together and
make available for people who didn't understand the problems with lookup
fields and have used them frequently, though.

Larry Linson
Microsoft Access MVP
Nov 12 '05 #19

P: n/a
On Wed, 24 Sep 2003 16:17:59 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

....
Of course, there is then the problem of also being able to see
what the value actually -is-. I solve that by making my the text
column in my lookup query an expression something like [FooName] &
" (" & [FooID] & ")". Now, the down-side is that this same lookup
gets added to combo controls when I drag fields onto forms, and I
have to edit it each time. So anyway, I guess I waffle back and
forth on this.


In the *table*, Steve? Why not use a saved query for that?


"That" being as the source for tables or where to do the lookup? I guess
it's just convenient to have the lookup always there if I open the table to
browse. I guess I could (and possilby should) do it with a query instead,
and use a naming convention to easily identify the browse query for each
table.

OK, time to institute a new habit.
Nov 12 '05 #20

P: n/a
> >If you mean " newer user " to Microsoft Access then probably you
are right.
But if you mean " newer user " to computing as well as MS Access -
like myself - then I'd say you don't see the benefit that a
beginner can gain by using it for a start.


That benefit can be gotten by using the lookups in queries viewed
in datasheet view, which to an end-user will be indistinguishable
from a table in datasheet view.
So, again, there is simply no need for the lookup in table design,
ever.


You are right. Although i don't claim there is a certain need to use
it
as far as the programming is concerned.
What i claim is that there are certain benefits to use it as far as a
beginner
is concerned to smooth the steep learning curve and understand what is
going on. Will they confuse later on ? Oh please let them. At the end
they will gain
so much experience and hold of their design more strongly when tried
to overcome the confusing bits.
I still think that lookup field in table design is a very good idea
and very thoughtful of MS Access.

Kind regards
Nov 12 '05 #21

P: n/a
ru****@hotmail.com (kemal) wrote in
<e3*************************@posting.google.com> :
>If you mean " newer user " to Microsoft Access then probably
>you are right.
>But if you mean " newer user " to computing as well as MS
>Access - like myself - then I'd say you don't see the benefit
>that a beginner can gain by using it for a start.


That benefit can be gotten by using the lookups in queries
viewed in datasheet view, which to an end-user will be
indistinguishable from a table in datasheet view.
So, again, there is simply no need for the lookup in table
design, ever.


You are right. Although i don't claim there is a certain need to
use it
as far as the programming is concerned.
What i claim is that there are certain benefits to use it as far
as a beginner
is concerned to smooth the steep learning curve and understand
what is going on. Will they confuse later on ? Oh please let them.
At the end they will gain
so much experience and hold of their design more strongly when
tried to overcome the confusing bits.
I still think that lookup field in table design is a very good
idea and very thoughtful of MS Access.


I think it's the kind of thing that demonstrates a principle I've
articulated repeatedly:

Ease of learning and ease of use are often mutually contradictory
goals.

In the short term, lookups in tables make certain things easy to
learn. In the long run, as voluminously demonstrated by Albert's
post, they make the program harder to use.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #22

P: n/a
"Albert D. Kallal" <ka****@msn.com> wrote in message news:<LZgcb.13423$TM4.5200@pd7tw2no>...
I repeat: lookup feature at the TABLE desing level is the the thing to
advoid.

I use the lookup feature in my table design all the time. I must have
missed something in the Access training class I took. Let's say I
have a table of employees with employeeid as the PK, with a field for
last name and a field for first name. I have a second table of
departments, with departmentid as PK and a field for the department
name. In order to create a relationship between the two tables, I
have to add a field to the employee table for department. I always
use the lookup feature to enter this info. Otherwise, wouldn't I have
to open my department table, look up the number for the department,
then go back to my employee table to enter it? What have I
missed?????

JD
Nov 12 '05 #23

P: n/a
<jd****@yahoo.com> wrote in message
news:75**************************@posting.google.c om...
"Albert D. Kallal" <ka****@msn.com> wrote in message

news:<LZgcb.13423$TM4.5200@pd7tw2no>...
I repeat: lookup feature at the TABLE desing level is the the thing to
advoid.

I use the lookup feature in my table design all the time. I must have
missed something in the Access training class I took. Let's say I
have a table of employees with employeeid as the PK, with a field for
last name and a field for first name. I have a second table of
departments, with departmentid as PK and a field for the department
name. In order to create a relationship between the two tables, I
have to add a field to the employee table for department. I always
use the lookup feature to enter this info. Otherwise, wouldn't I have
to open my department table, look up the number for the department,
then go back to my employee table to enter it? What have I
missed?????


You're missing the fact that the lookup field is making it easier for you to use your
tables for data entry and that is not what tables should be used for. Data entry
should always be done with forms and data viewing either with forms or reports.

If you were using a form you would add a ComboBox that would give you the "see one
thing - save another" functionality that the lookup field is providing you at the
table level and you wouldn't be obfuscating what data is actually stored in the
table.
Nov 12 '05 #24

P: n/a
<jd****@yahoo.com> wrote in message
news:75**************************@posting.google.c om...
I use the lookup feature in my table design all the time. I must have
missed something in the Access training class I took. Let's say I
have a table of employees with employeeid as the PK, with a field for
last name and a field for first name. I have a second table of
departments, with departmentid as PK and a field for the department
name. In order to create a relationship between the two tables, I
have to add a field to the employee table for department. I always
use the lookup feature to enter this info. Otherwise, wouldn't I have
to open my department table, look up the number for the department,
then go back to my employee table to enter it? What have I
missed?????


You may have missed:

http://www.mvps.org/access/lookupfields.htm

If you want to learn the correct method of designing a database table, you
need to avoid lookup fields. If you are working in the tables all by
yourself, on a database which will *never* be used by others, *never* be
secured, and *never* be upsized, do what you want. No one will know or care.
--
Arvin Meyer, MCP, MVP
Microsoft Access
http://www.datastrat.com
http://www.mvps.org/access
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003
Nov 12 '05 #25

P: n/a
Thank you for the very detailed explanation Albert. Your post sums up
the thread pretty well. Thank you to all who contributed to this
thread; your discussion has been most valuable. I shall announced
that I have fully converted to non-lookup-table-ism <g>. Will start
on it in the very next database I design. Thank you.
Nov 12 '05 #26

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote in message news:<94***************************@24.168.128.74> ...
I think it's the kind of thing that demonstrates a principle I've
articulated repeatedly:

Ease of learning and ease of use are often mutually contradictory
goals.


Unfortunately, I have to agree with David's asessment. That seems to
be the trend of our time. Things being made more convenient, and then
ppl using them out of habbit without understanding what the shortcut
has replaced.

I have to admit I fall victim to these pitfalls quite often, but I try
to extricate myself as best I can. The major ones are usually well
documented enough to come up on a neophyte's learning attempts, but
the more "esoteric" points such as these then to fall in between the
cracks. Thank you for pointing that out.
Nov 12 '05 #27

P: n/a
"Rick Brandt" <RB*****@Hunter.Com> wrote in message news:<bk************@ID-98015.news.uni-berlin.de>...

You're missing the fact that the lookup field is making it easier for you to use your
tables for data entry and that is not what tables should be used for. Data entry
should always be done with forms and data viewing either with forms or reports.

If you were using a form you would add a ComboBox that would give you the "see one
thing - save another" functionality that the lookup field is providing you at the
table level and you wouldn't be obfuscating what data is actually stored in the
table.


That's what I've missed...I've been to 3 2-day classes, and I've gone
through the Access training cd's, I have several books, and I've never
heard or read that tables should not be used for data entry.

JD
Nov 12 '05 #28

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote in message

I think it's the kind of thing that demonstrates a principle I've
articulated repeatedly:

Ease of learning and ease of use are often mutually contradictory
goals.

Words " are often " here sound a bit - how do you say it ? - exaggerated
if not irrelevant.
In the short term, lookups in tables make certain things easy to
learn. In the long run, as voluminously demonstrated by Albert's
post, they make the program harder to use.


Obviously doesn't need my approval.
Nov 12 '05 #29

P: n/a
jd****@yahoo.com (jd****@yahoo.com) wrote in
news:75**************************@posting.google.c om:
That's what I've missed...I've been to 3 2-day classes, and I've gone
through the Access training cd's, I have several books, and I've never
heard or read that tables should not be used for data entry.


When your access program contains thousands, or hundreds of thousands of dollars worth of data, you'll
feel differently.

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos
Nov 12 '05 #30

P: n/a
"Larry Linson" <bo*****@localhost.net> wrote in
news:e_*****************@nwrddc01.gnilink.net:
Might be a nice thing for someone with some spare time to put together
and make available for people who didn't understand the problems with
lookup fields and have used them frequently, though.


And here it is: a VBA function that examines a table 'tablename', creates a querydef 'q_tablename' with
the lookup field options from the table, and removes the lookup field options from the table.

--- cut here -- MoveLookups.bas ---
Option Compare Database

Sub MoveLookups(strTable As String)
Dim oDB As DAO.Database
Dim oTbl As DAO.TableDef
Dim oQry As DAO.QueryDef
Dim oFld As DAO.Field
Dim strSQL

Set oDB = CurrentDb
Set oTbl = oDB.TableDefs(strTable)
Debug.Print "Table '" & strTable & "'"
strSQL = "Select "
For Each oFld In oTbl.Fields
strSQL = strSQL & "[" & oFld.Name & "], "
' The entire IF statement that follows can be removed
' if you don't want the table details printed in your debug window
If oFld.Type = dbMemo Then
Debug.Print oFld.Name, "Memo"
Else
Select Case oFld.Properties("DisplayControl")
Case acTextbox
Debug.Print oFld.Name, oFld.Type, "Textbox"
Case acListbox
Debug.Print oFld.Name, oFld.Type, "Listbox"
Debug.Print "", oFld.Properties("RowSourceType"), oFld.Properties("RowSource")
Case acComboBox
Debug.Print oFld.Name, oFld.Type, "Combobox"
Debug.Print "", oFld.Properties("RowSourceType"), oFld.Properties("RowSource")
Case acCheckbox
Debug.Print oFld.Name, oFld.Type, "Checkbox"
Case Else
Debug.Print oFld.Name, oFld.Type, "huh? "; oFld.Properties("DisplayControl")
End Select
End If
Next
strSQL = Left(strSQL, Len(strSQL) - 2) & " from [" & strTable & "]"
Set oQry = oDB.CreateQueryDef("q_" & oTbl.Name, strSQL)

For Each oFld In oTbl.Fields
If oFld.Type <> dbMemo Then
If oFld.Properties("DisplayControl") = acListbox _
Or oFld.Properties("DisplayControl") = acComboBox Then
oQry.Fields(oFld.Name).Properties("DisplayControl" ) = oFld.Properties("DisplayControl")
oQry.Fields(oFld.Name).Properties("RowSourceType") = oFld.Properties("RowSourceType")
oQry.Fields(oFld.Name).Properties("RowSource") = oFld.Properties("RowSource")
oFld.Properties("DisplayControl") = acTextBox
oFld.Properties.Delete "RowSource"
oFld.Properties.Delete "RowSourceType"
End If
End If
Next
End Sub
---- cut here ----
--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos
Nov 12 '05 #31

This discussion thread is closed

Replies have been disabled for this discussion.