Lookup fields in tables bad?

=?Utf-8?B?RnJlZA==?= wrote in
:

"All of the advantages of lookup fields in tables are only
there if you work directly with tables." to that I'd add "or
queries"

Creating lookups in a query is just fine, seems to me, as it saves a
join. On the other hand, it has all the problems that lookups have
in tables if you use that query in the FROM clause of another query.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 24 Jan 2009 02:56:08 GMT, "David W. Fenton"
wrote:

Creating lookups in a query is just fine, seems to me, as it saves a
join.

Does it though? I was under the impression that a lookup just creates a join,
"under the covers".
--

John W. Vinson [MVP]

John W. Vinson wrote in
news
On 24 Jan 2009 02:56:08 GMT, "David W. Fenton"
wrote:

Creating lookups in a query is just fine, seems to me, as it saves
a join.

Does it though? I was under the impression that a lookup just
creates a join, "under the covers".

I think that in certain circumstances, retrieving two recordsets
(the query recordset and the combo box recordset) is going to be
more efficient than retrieving the same data joined in a single
recordset. If the combo box has lots of records (10s of thousands),
it will definitely be more efficient, as Rushmore will kick in for
the dropdown (i.e., retrieving only as many records have been
requested for viewing), whereas with a join, the whole index is
going to have to be retrieved and processed by whatever kind of join
is used.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

My point is that in-table lookup fields don't just add dropdown functionality
to direct views of tables, they add otherwise unavailable functionality to
query views.

It may be that 95% of the time that one would be better off avoiding
in-table lookup fields, (and we very much appreciate this advice from the
experts) but 95% is not 100% so it should be taken as such rather than as a
categorical rule.

Some of our situations are in the 5%.

=?Utf-8?B?RnJlZA==?= wrote in
:

My point is that in-table lookup fields don't just add dropdown
functionality to direct views of tables, they add otherwise
unavailable functionality to query views.

It may be that 95% of the time that one would be better off
avoiding in-table lookup fields, (and we very much appreciate this
advice from the experts) but 95% is not 100% so it should be taken
as such rather than as a categorical rule.

Some of our situations are in the 5%.

I strongly disagree. Lookup tables do not belong in tables at all.

In queries, I don't it's much of an issue, but should be used only
in queries that are used by the developer, since one should never
just dump a user into a query datasheet. If you're using a saved
query as a recordsource of a form, then you'll end up with the same
problems that you get with lookup fields in table definitions.

So, for me:

1. Tables: avoid 100% of the time

2. Queries: avoid 100% of the time for recordsources.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David,

So, if my mom, who knows Access just a little, takes a few minutes to make
an mdb to put her grocery list on, another minute for the wizard to make a
dropdown list consisting of the four sections of the grocery store, she:

- should never look at the data in a table or query directly?

- if she wants dropdown cabability, she should hire a developer for her for
her gocety list who knows how to do that in forms with combo boxes etc?

I have this debate with my son who thinks that the rules that are good for
his 200,000,000 record SQL databases should be categorical rules, i.e. rules
for my mom's grocery list.

Many developers presume the situation and all Access uses are developed
applications. I.E. a substantial investment to make a an application
suitable for use by lots of people who know nothing about Access and have
poor data entry disclipline.

In our company's case, we have many databases which are quickly put together
by one person than then used by just 1-4 engineers / power users. They make
lots of use of doing searches (different every time) by putting criteria in
the query grid. I don't know any other way to give them that functionality
without using the query directly.

That's the other "5% " In my opinion, really good practices such as you
describe should be strong influences on design, not necessarily categorical
rules.

"Fred" wrote in message
news


So, if my mom, who knows Access just a little, takes a few minutes to
make
an mdb to put her grocery list on, another minute for the wizard to make
a
dropdown list consisting of the four sections of the grocery store, she:

- should never look at the data in a table or query directly?

- if she wants dropdown cabability, she should hire a developer for her
for
her gocety list who knows how to do that in forms with combo boxes etc?


My 2p worth: she should use Excel. Or the back of an envelope like I do

Keith.

=?Utf-8?B?RnJlZA==?= wrote in
news
So, if my mom, who knows Access just a little, takes a few
minutes to make an mdb to put her grocery list on, another minute
for the wizard to make a dropdown list consisting of the four
sections of the grocery store, she:

- should never look at the data in a table or query directly?

No. She should create a form (wizards, again).

- if she wants dropdown cabability, she should hire a developer
for her for her gocety list who knows how to do that in forms with
combo boxes etc?

No. Wizards.

I have this debate with my son who thinks that the rules that are
good for his 200,000,000 record SQL databases should be
categorical rules, i.e. rules for my mom's grocery list.

If lookup fields didn't cause problems, then I wouldn't be against
them, but the kinds of problems they cause are mystifying to novice
users, and best avoided.

And editing in tables is a bad idea for those users, too. It's not
like it's hard to create a form.

Many developers presume the situation and all Access uses are
developed applications. I.E. a substantial investment to make a
an application suitable for use by lots of people who know nothing
about Access and have poor data entry disclipline.

In our company's case, we have many databases which are quickly
put together by one person than then used by just 1-4 engineers /
power users. They make lots of use of doing searches (different
every time) by putting criteria in the query grid. I don't know
any other way to give them that functionality without using the
query directly.

That's the other "5% " In my opinion, really good practices such
as you describe should be strong influences on design, not
necessarily categorical rules.

Well, we disagree. I think you're buying your users future problems
because you lack the knowledge to do it right. I've been there and
have spent years fixing up the terrible things I did back in the day
when I didn't know any better. The sooner you learn to do it right,
but fewer of those future problems you will be creating.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hi Allen; et al,

Thanks so much for the great outline on this subject, and I appreciate very
much your taking the time to help me understand.

I would have replied sooner, but I’ve been down hard with the flu.

In fact I have frequented your site Allen, as well as others posted in this
discussion group trying to gain what knowledge I can from the pros. I’ve
spent hours reading the helpful posts here, and those interesting and helpful
resources you and others make available to guys like me who are not.

And I thank everyone in this group for your generosity, and for your
willingness and your enthusiasm to help others.

To be sure, I have a long, long way to go; but I look forward to reading
more of your writings on this and other topics.

Thanks again for the posts and the help!

Best regards,
Robert


"Allen Browne" wrote:

Okay, lets see if we can clarify things for you here.

In table design, if you choose a field of type "Lookup Wizard", you fire up
a wizard that many of us don't like. We tend to avoid this wizard because of
its side effects. You have probably read this:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

But no one is saying that you should not use foreign keys. Every non-trivial
database has lookup tables -- small tables that contain the valid values for
a field in a table that looks them up. That is the only way to correctly
normalize your data.

There is a debate about whether the lookup table should have a numeric field
as its primary key (in which case your foreign key field will also be a
Number), or whether you should use a natural key (Text type.) If you have a
lookup table for categories, each category name will be unique. If they are
also relatively brief (just a couple of dozen characters), it makes sense to
me to use that as the natural key instead of introducing an artificial key
(such as an AutoNumber.) Sounds like you may have done that in some cases.

Next the question arises as to whether your main table should use a text box
or a combo box for the foreign key field. Combos are great on forms, but
many developers avoid them in the table because it masks what data is really
here. For example, if you use the Lookup Wizard, you get a field of type
Number, but it displays the text. That's confusing for developers and
newbies alike: you actually have to open the table in design view in order
to build a WHERE clause on the field, because you don't know what delimiters
you need just by looking at the data.

You also raise the possibility of using value lists rather than a lookup
table. IMHO, that's the worst possible solution. It's trivial to let the
user add more records to a lookup table, but modifying the items in a lookup
list requires design changes. For more info on this, see:
http://allenbrowne.com/ser-27.html#ValueList

So, FWIW, my recommendations would be:
- Do use foreign keys.
- Avoid the Lookup wizard.
- Use natural keys where appropriate.
- Use combos in your table only rarely.
- A Value List in a combo in a table is too inflexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert5833" wrote in message
...
Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things can
happen to the wayward soul who unwittingly builds lookup fields into his
tables, and second; if such practice should be avoided like the plague,
how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the
table
level. Is this the same, and just as bad as having an SQL lookup field in
a
table?

In a copy of a database I’m working on now, I changed all of the table
level
lookup fields (SQL type) to straight text boxes and I’m waiting now for
that
copy to implode…but it hasn’t yet, and all of my forms are working
correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to enter
any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's only
after a first pass at building one, and just before I create a form for
data
entry purposes. Since my forms all have lookup fields in them, will my
house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at me
for my ignorance or even just offer anecdotal comments on what may be my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL

Hello David,

Thanks for taking the time to give that insight. But I think that we're
talking about two different things / situations.

The "my mom" part was metaphorical, but your post didn't cover the real
example I gave......there are lots of databases out there where there is no
developer, and which are created in minutes and used by only on or two power
users. The don't look at data in table views, but often look at it query
views, setting completely different sort and filter criteria on different
fields with each use.

I think that the newer post in this section by Albert D. Kallal brings
together what both of us are saying.