473,686 Members | 3,531 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

lookup questions

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....

Nov 13 '05 #1
3 2913
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.publi c.access in which I'm
involved. Did you see where the other person wrote
"AAAAAAAAAAHHHH HHHHHHHHHHHH!"? 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******@TAKEO UTatt.net> wrote in message
news:fq******** *************@b gtnsc05-news.ops.worldn et.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....

Nov 13 '05 #2

"Larry Daugherty" <La************ ********@verizo n.net> wrote in message
news:FO******** ********@nwrddc 02.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
Nov 13 '05 #3
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 "Programmin g 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******@TAKEO UTatt.net> wrote in message
news:U6******** *************@b gtnsc04-news.ops.worldn et.att.net...

"Larry Daugherty" <La************ ********@verizo n.net> wrote in message
news:FO******** ********@nwrddc 02.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

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2032
by: Lucas Lemmens | last post by:
Dear pythonians, I've been reading/thinking about the famous function call speedup trick where you use a function in the local context to represent a "remoter" function to speed up the 'function lookup'. "This is especially usefull in a loop where you call the function a zillion time" they say. I think this is very odd behavior.
1
3354
by: Zachary Turner | last post by:
I want to make a Lookup Field based on another Lookup field. In other words, I have this table A with two fields: ID and Name, where ID is an Autonumber and Name is a friendly name. Then I have a table B which links to A via a Lookup Field so that I can select the friendly name from combo box. Now, I want to have a table C which links to table B via a lookup field, and only displays the items which are selected in some combo box of some...
10
2201
by: MJ | last post by:
How can you update a Lookup List after it is created. It is not tied to any table. I have tried adding the new data at the table level, but when I go to the Form to input, it isn't on the list. Any suggestions?
1
1705
by: jhicsupt via AccessMonster.com | last post by:
Bear with me – a long question but hopefully someone can help me. I have a survey which uses weighting to get the final score. I’m trying to find the best way to calculate the final score. I have two tables A table with all the employee information which includes the , , and . A table that will be used as a lookup to determine the weighting of that
5
3896
by: Kevin | last post by:
I was reading on the mvps.org site not to use the lookup wizard in the table design, but rather use a query. I'm confused by this, given that the lookup wizard basically creates a query and uses it as the data source - you can see it in the "Lookup" tab in the table design window. So, what gives? :)
1
3634
by: LurfysMa | last post by:
I am working on an electronic flashcard program. Most of the subjects are simple lists of questions and answers. Those seem to be working. Some of the "subjects" have "categories" of questions. One example is a kind of trivia subject. There are several "categories" such as "geography", "history", etc., like in the Trivial Pursuit game. Each card has a question in each category.
4
1224
by: someone | last post by:
Could someone direct me to a site that would help me with the following: Have database setup and loaded. Want to create web page that will use database to do lookup of database on screen and allow selection of field. Ie... lookup council member in lookup window, select and have proper/correct committees councilperson belongs to appear. TIA
11
2649
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in tblPeople called PreferredPet. 2. A lookup field that stores text values. Create a text field in tblPeople called PreferredPetID and use it to lookup an identical text field in tblPreferredPets.
2
1164
by: Kursat | last post by:
Hi, In my Windows applications I generally use lookup forms to show a list of values to users and these windows are generally modal. Users select any item from list, press OK button and details are displayed on main window. I want to implement same logic in my ASP.NET applications but I have no idea about some points such as : "How can I display a modal browser window?", "How can I get selected item from lookup list when user select an...
0
8581
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8766
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7598
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5795
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4307
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2944
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1933
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.