lookup questions

[my-wings]

I've been reading about how evil Lookup fields in tables are, but I've got
to be missing something really basic. I know this subject has been covered
before, because I've just spent an hour or two reading about it on google,
but there is something I still don't understand, and I'm hoping someone will
be willing to explain it to me in small words.

Let's say I have a table for addresses, and it includes a field for state.
What I would normally do is create a table of state abbreviations and use
the lookup wizard to link to it. Then, when I make a form for data entry
based on the address table, the field for states appears as a drop down
list.

So, what happens if I don't do the lookup table for "state"? I assume I
still need a table of state abbreviations, but ... how do I get the
information from my state table into my address table? What actually winds
up "in" the address table state field? Just text?

What I'm afraid of is that the answer somehow involves using visual basic to
take the info from the form and put it into the address table, rather than
directly updating the table from the form. And I don't know visual basic.
Does this mean I'm doomed to crappy design until I learn it?

Thanks for any clarification anyone wants to provide....

[Larry Daugherty]

Did you follow the track to www.mvps.org/access ? If not, it's worth a peek

Having a lookup table with the state codes is a great idea but you get at it
with the query for your combobox on the form rather than having it percolate
up as an artifact of a table. You have exactly the same access to your data
and in exactly the same form.

Now consider the case where you are attempting to include the state
information in a query that you've designed based on a table with a lookup
field as you describe. The query you're designing won't return the value
for a state. Try it. The lucky people who asked about lookup fields and,
having been told to avoid them, plunged ahead anyway will discover the
problems early and take remedial action. Most novices don't discover their
problem until very late in the day. They put in the lookup fields because
they looked like a lot of free functionality and it's impressive as can be
to have something so cool to show off. As time goes on they spend more
effort making their applications more useful. Their early forms aren't too
sophisticated and they probably don't even bother to create reports. As
they start to create more sophisticated forms and queries and reports
they're going to discover that the queries that make everything else work
seem not to work too well. There are some fields that return garbage or
nothing at all. Guess which fields. Some hardworking and creative types
will come up with ingenious work-arounds but, in time, nothing can overcome
the flaw. The longer it takes to come to that realization the worse the
reckoning. The only way to correct the problem is to go back and correct
the tables. Now just about every form and query that used the affected
tables will have to be changed to get the required functionality. I could
go on.

If you haven't heard it before, may I be the first to tell you that the
cornerstone of a good application is good data design - identifying and
representing the entities that play in your application space - the tables!
Get them right and everything else is possible and surprisingly easy. How
do you get them right? Get to know what you're trying to represent and then
design your tables obeying the relational rules. So help me, Codd! [E.F.
Codd and C. J. Date; heavy hitting IBMers circa 1970 - think of DB2].
There are rules of Normalization where your tables are concerned. Third
Normal Form is pretty much the minimum standard for most applications. If
you don't know what that means then you'll probably go back to "touch up"
the Access work you've done before learning.

If you just googled the words "lookup field" in all of the Access newsgroups
then you probably saw a thread on microsoft.public.access in which I'm
involved. Did you see where the other person wrote
"AAAAAAAAAAHHHHHHHHHHHHHHHH!"? He'd been warned about lookup fields, used
them anyway, was having troubles with a query that just wouldn't work and I
said I wouldn't try to help him further until he got rid of the lookup
fields.

After more carefully re-reading your post, I have a feeling that you may
have confused Lookup Fields in tables with "lookup tables". Lookup tables
are a good thing. They are just normal tables with some meaningful list
that is held for reference. I think you used the term Lookup Wizard when
you were actually referring to the Combobox Wizard. Yup. When you have
wizards enabled on the Toolbox then placing a combobox on your Form (not
Table) will cause it to spring to life and ask what you want to do.

Nomenclature is important. Fields only occur in tables. The things on
forms are Controls. Controls can be bound to fields. Lots of folks use the
terms as though they were interchangeable. They aren't.

Lookup tables good; Lookup Fields BAD. All is well.

My other favorite rant is about the mis-use of the Autonumber type. You can
google for it or find it on www.mvps.org/access

HTH
--
-Larry-
--

"my-wings" <my******@TAKEOUTatt.net> wrote in message
news:fq*********************@bgtnsc05-news.ops.worldnet.att.net...

I've been reading about how evil Lookup fields in tables are, but I've got
to be missing something really basic. I know this subject has been covered
before, because I've just spent an hour or two reading about it on google,
but there is something I still don't understand, and I'm hoping someone will be willing to explain it to me in small words.

Let's say I have a table for addresses, and it includes a field for state.
What I would normally do is create a table of state abbreviations and use
the lookup wizard to link to it. Then, when I make a form for data entry
based on the address table, the field for states appears as a drop down
list.

So, what happens if I don't do the lookup table for "state"? I assume I
still need a table of state abbreviations, but ... how do I get the
information from my state table into my address table? What actually winds
up "in" the address table state field? Just text?

What I'm afraid of is that the answer somehow involves using visual basic to take the info from the form and put it into the address table, rather than
directly updating the table from the form. And I don't know visual basic.
Does this mean I'm doomed to crappy design until I learn it?

Thanks for any clarification anyone wants to provide....

[my-wings]

"Larry Daugherty" <La********************@verizon.net> wrote in message
news:FO****************@nwrddc02.gnilink.net...

Did you follow the track to www.mvps.org/access ? If not, it's worth a peek

Yep. I actually googled the words "access lookup field evil" (without the
quotes) and it's the top entry. All the reasons not to use lookup fields
were there. But I still don't understand what to use instead.

Having a lookup table with the state codes is a great idea but you get at it with the query for your combobox on the form rather than having it percolate up as an artifact of a table. You have exactly the same access to your data and in exactly the same form.
But this is the part I don't get. How does the data get from the combo box
on the form back into your table? And what is recorded in the table? I
still have this field called "state"....how does it get any information into
it, and what kind of information is there? I understand the solution is
queries and combo boxes on forms, but I don't understand how things are
hooked together if there is no lookup field in the table.

After more carefully re-reading your post, I have a feeling that you may
have confused Lookup Fields in tables with "lookup tables".
No. I was referring to the Lookup Field you get in a table when you select
Lookup Wizard from the data types.
Lookup tables
are a good thing. They are just normal tables with some meaningful list
that is held for reference. I think you used the term Lookup Wizard when
you were actually referring to the Combobox Wizard.
Nope. Was referring to the Lookup wizard available in table design, not the
one that creates combo boxes on forms.
Lookup tables good; Lookup Fields BAD. All is well.

Or all will be well when I figure out what to use instead of lookup fields,
lol.

I appreciate your reply, but as you can see, I'm still puzzling over this.
I'm working my way through a book called Microsoft Office Access 2003 Inside
Out. I'm only on Chapter 5, so maybe things will clarify themselves as I
proceed. Right now, it looks like in the places I would have put lookup
fields, the author used the relationship builder to show a connection
between the regular table and the lookup table. When I look at the tables
with data, there are no drop down lists. Just the data itself.

Excuse me a minute while I go build a quick form.....

Back with an "ah ha!". Let's see if I have this right:

1. Create tblAddresses with a "state" field.
2. Create tlkpStates with its "state" field.
3. Go to Tools/Relationships and add both tables.
4. Drag the tblAddresses "state field" to tlkpStates "state field" and
create a many-to-one relationship.
5. Create a form.
6. Use the combo box wizard to create a combo box from tlkpStates. At the
appropriate screen, say that you want to store the result in tblAddresses
"state" field.

Then, as long as you use the form to enter things, the only things in the
tblAddresses "states" field will be entries from tlkpStates. On the other
hand, if someone were to enter directly in the table, there would be no
protection that the entry exists in the tlkpStates. Which is why you can't
let people enter things directly in tables, I'll bet.

I hope I have this right finally. The above looks like what everyone has
been saying, but without working my way through the combo box wizard, I
didn't see where the "connection" was being made.

I think I'm marginally less confused now, but hopefully someone will let me
know if I still don't understand this correctly.

Thanks.

Alice

[Larry Linson]

The RowSource of your Combo Box could have the "stateid" and "statename"
fields, in that order. If you set the bound field as the "stateid" field (in
the wizard or the ComboBox properties) but set its width to 0, the user will
see the state's name, but the stateid will be stored in your table.

To retrieve the state name with your data, you join the primary table to the
state table on stateid and pull down into the Query Builder grid the
statename from the state Table.

FYI, for "stateid" in my state lookup table, I use the USPS state code, eg.,
AK for Alaska, AR for Arkansas, ... TX for Texas, etc. That's because I want
to show it to the user and may want to use that field in some uses (e.g., an
address report or printing envelopes or mailing labels). If I had no need to
use the field, I could use a number and just not show it to the user (see
above) by setting the width in the combo to 0.

If you are even near-serious about developing database applications, invest
the time and energy to learn VBA code. If you are a "power user" wanting to
do so, I've observed that Dr. Rick Dobson's books on "Programming Microsoft
Access <versionnumber>" from Microsoft Press are useful -- that is one of
the particular audiences that he tried to address, and he succeeded in doing
so.

Larry Linson
Microsoft Access MVP

"my-wings" <my******@TAKEOUTatt.net> wrote in message
news:U6*********************@bgtnsc04-news.ops.worldnet.att.net...

"Larry Daugherty" <La********************@verizon.net> wrote in message
news:FO****************@nwrddc02.gnilink.net...

Did you follow the track to www.mvps.org/access ? If not, it's worth a peek

Yep. I actually googled the words "access lookup field evil" (without the
quotes) and it's the top entry. All the reasons not to use lookup fields
were there. But I still don't understand what to use instead.

Having a lookup table with the state codes is a great idea but you get at it

with the query for your combobox on the form rather than having it percolate

up as an artifact of a table. You have exactly the same access to your

data

and in exactly the same form.

But this is the part I don't get. How does the data get from the combo box
on the form back into your table? And what is recorded in the table? I
still have this field called "state"....how does it get any information

into it, and what kind of information is there? I understand the solution is
queries and combo boxes on forms, but I don't understand how things are
hooked together if there is no lookup field in the table.

After more carefully re-reading your post, I have a feeling that you may
have confused Lookup Fields in tables with "lookup tables".
No. I was referring to the Lookup Field you get in a table when you select
Lookup Wizard from the data types.

Lookup tables
are a good thing. They are just normal tables with some meaningful list
that is held for reference. I think you used the term Lookup Wizard

when you were actually referring to the Combobox Wizard.

Nope. Was referring to the Lookup wizard available in table design, not

the one that creates combo boxes on forms.

Lookup tables good; Lookup Fields BAD. All is well.
Or all will be well when I figure out what to use instead of lookup

fields, lol.

I appreciate your reply, but as you can see, I'm still puzzling over this.
I'm working my way through a book called Microsoft Office Access 2003 Inside Out. I'm only on Chapter 5, so maybe things will clarify themselves as I
proceed. Right now, it looks like in the places I would have put lookup
fields, the author used the relationship builder to show a connection
between the regular table and the lookup table. When I look at the tables
with data, there are no drop down lists. Just the data itself.

Excuse me a minute while I go build a quick form.....

Back with an "ah ha!". Let's see if I have this right:

1. Create tblAddresses with a "state" field.
2. Create tlkpStates with its "state" field.
3. Go to Tools/Relationships and add both tables.
4. Drag the tblAddresses "state field" to tlkpStates "state field" and
create a many-to-one relationship.
5. Create a form.
6. Use the combo box wizard to create a combo box from tlkpStates. At the
appropriate screen, say that you want to store the result in tblAddresses
"state" field.

Then, as long as you use the form to enter things, the only things in the
tblAddresses "states" field will be entries from tlkpStates. On the other
hand, if someone were to enter directly in the table, there would be no
protection that the entry exists in the tlkpStates. Which is why you can't
let people enter things directly in tables, I'll bet.

I hope I have this right finally. The above looks like what everyone has
been saying, but without working my way through the combo box wizard, I
didn't see where the "connection" was being made.

I think I'm marginally less confused now, but hopefully someone will let me know if I still don't understand this correctly.

Thanks.

Alice