473,701 Members | 2,465 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need an alternative to Lookup fields

I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems that are hard
to find. The main problem I am having right now is that I have a report
that is sorted by one of these lookup fields and it only displays the
record's ID number. When I add the source table to the query it makes
several records disappear from the output and leave incorrect
I have two tables, (1) Homes & (2) Tenants.

Homes = HomeID + Address + Tenant + Rent
Tenant = TenantID + FName + LName + SSN

The Home.Tenant field is a lookup field which stores the
Tenant.TenantID but displays Tenant.FName + " " + Tenant.LName.

I want to create a report of all homes sorted by Tenant. This works as
long a the Homes table is the only one in my queries design window.
However, when I make the report from this query and sort by Tenant,
only the TenantID number is displayed. I understand that that is what
is stored in the table but, is there a way to use that information and
have the report display the FName and LName for the corrisponding
record? When I try to add the Tenant table to my query it just makes
the records without a tenant disappear and displays the wrong name for
the actual tenant.

I am willing to change all of the lookup fields but I am not sure how
to do that exactly. How can I keep the dropdown lists for entering data
into my fields AND display actual names on my report instead of the

I have searched around and found lots of threads stating not to use
lookup field but non with detailed instructions on how to avoid this.
Is don't mind makng lots of tables if it makes my database more
efficient and cleaner.
Let me know if you need more information.
Thanks in advance.

Nov 13 '05 #1
3 10652
You want to be "clear" on what we mean when you should avoid lookup fields.

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!
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 design level is the thing to avoid.

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
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). I repeat, we are talking about this feature
at the table design 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
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 learn the correct skills for other sql database systems you
will use.

When you build a form, feel to use the combo box wizard to lookup
values as often as you want, and this is a good and recommended

So, to "bring in" other additional fields from those other tables, you
simply fire up the query builder.

If you have 5 other tables that are used for these lookups, then simply drop
in those additional 5 tables.

You now of course have to draw the join lines from the "main" table to these
other tables with the information you need.

So, draw the join lines, and then double click on the join line, and make
the join a left join (select the option to always include the main table
records...and possible a child record).

Now, you are free to drop into the query builder any field, and even those
"descriptio n" fields etc from those other tables. Those field can no be used
in the report..and sorted on correctly.

So, there is no reason to use the lookup field feature in a form, as you can
just use the combo box wizard.

And, for reports..just fire up the query builder and start dropping in all
those tables as above..and again you don't need the lookup feature. And, you
will find that there is NO confusing as to if the ID of the lookup, or
description will show, and no surprises in terms of sorting etc.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
Nov 13 '05 #2
OK. That was a lot of information. Thank you for the detail. So, are
you saying that it is ok to have a lookup field anywhere except in a
I am assuming that I should make the 'Tenant' field a text type and
place the SQL in the input form to populate the dropdown box. Or did I
miss the core of what you typed?

Nov 13 '05 #3
OK. That was a lot of information. Thank you for the detail. So, are
you saying that it is ok to have a lookup field anywhere except in a
I am assuming that I should make the 'Tenant' field a text type and
place the SQL in the input form to populate the dropdown box. Or did I
miss the core of what you typed?

Nov 13 '05 #4

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

Similar topics

by: Rob Allen | last post by:
I've got a lookup table for a lookup table and can't work out the SQL to do a search. Can anyone help? As an example, consider the following: Table 1 - books: fields: book_id, price, title, author data: 1, 2.99, 'Life', 'A. Slug' 2, 4.99, 'The Universe', 'A. Planet'
by: maxal | last post by:
have two fields, Title_Id table customers, I also have table named titles with title_id and titles In DataSet I have DataTable customers with fields title_id and title. In the form I have combox that I binded to field title_id in table customers. It works fine, field title_id is updated correctly. However, field title remains the same. I don't want to post data to database yet, so I just want field title to be updated according to...
by: David Allison | last post by:
Should Lookup fields be replaced by table normalisation? Then the Tables brought together in a Query for a Form to be based on? -- Dave Allison ~ Scotland
by: John Baker | last post by:
Hi: I have a general question about table access and look ups. Over simplifying things,and using a very theoretical example, lets say I have two tables; Client and Bill. The client table has a unique auto number (which is indexed) and the Client name, as well as address and other key data
by: Paul H | last post by:
Say I have a table called tblPeopleInfo, one of the fields in the table is called FavouriteFruit. The FavouriteFruit field is a lookup field and will contain Apples, Oranges, Grapes etc..The list can be added to by users of the database. What is the best way to construct this lookup? Should it lookup a Table/Query or a Value List? If it looks up to a Table/Query should the lookup table just have a single "text" field or should it have...
by: David | last post by:
Would someone please explain how I can activate the pull down lookup fields in a db grid. I have created a SQL database within ACCESS and converted to SQL using the upsize wizard. The database consits of 5 tables. 4 of these are options that the main table can select but I am unsure how to set up a column so that it looks up one of the other databases (like a db linked combo box).
by: John Smith | last post by:
I am trying to combine two lookup fields into one field for display on a form and a report. I understand the basics behind how to do this: I set the control source for a new text box control to = & The problem is, this gets me a number. You see, HabitID and ClassID are both lookup fields on the Control Source table to the form. So, if I have a field on the form named HabitID, it would be a combobox that looks up values from tblHabit....
by: Lance Williams | last post by:
How can you programatically tell if a field in a table is a lookup field? I am looping through the fields collection and I want to see if any fields are lookup fields. Is there a property, or something taht will tell me if the field is a lookup field. I need to do this at the table level and not at the form level because if a field is a lookup field then I do certain things in the code. BTW, lookup fields suck.
by: freetime | last post by:
Greetings, This is my first post so I will try to be brief and accurate. I have a report that uses 9 user selectable filters (Dates, Y/N's as Combo Boxes and 4 Multi-Select List Boxes. 2 of the Multi-Select Boxes acquire their data from other tables as Lookup's. All functions for the filters operate properly pulling the data needed. One of my options with the data is to send it to a Report that can be printed. The request...
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...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
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,...
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...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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.