473,606 Members | 2,141 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Lookup fields - saving space

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 an AutoNumber field plus the text field so
that the Lookup field in the original table only looks up an integer thus
saving space?

Thanks,

Paul

Nov 13 '05 #1
1 1893
Use a table for for the lookup values. Most databases have lots of these
little lookup tables in them.

You can use the text value as the primary key: no need for an autonumber.
Although this could use up a few extra bytes, there is really no
justification for using an artificial key value for most of these lookup
tables.

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

"Paul H" <no****@nospam. com> wrote in message
news:Yx******** *********@newsf e6-win.ntli.net...
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 an AutoNumber field plus the text field so
that the Lookup field in the original table only looks up an integer thus
saving space?

Thanks,

Paul

Nov 13 '05 #2

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

Similar topics

4
6395
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'
3
2910
by: my-wings | last post by:
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...
1
3346
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...
3
10626
by: google | last post by:
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...
2
2388
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.
0
1257
by: =?Utf-8?B?RU1hbm5pbmc=?= | last post by:
(I originally posted this to the data access newsgroup but received no replies) I've got an Access 2003 mdb that I'm converting to VB.Net. I'm having trouble with getting the main data source to update after updates to a lookup table. There are 2 tables with a one-to-many relationship in the mdb. VB.Net recognizes this relationship according to it's dataset designer diagram. Currently I have 2 forms: a main form for data-entry and a...
7
2578
by: samdev | last post by:
I have set up a table with a few fields that are set to required....all work fine except the fields that are Lookup from another table. What am I forgetting to set? Thanks!!
2
2104
by: ApexData | last post by:
How do I use a combo-box to do a lookup on my name table “T-NAME”, and then place the values found in the lookup table (ie LNAME, FNAME, MI) into to a row in my existing continuous forms table "T-SICK" ? I will have the combo-box lookup available in each row of my continuous form bound to "T-SICK" to make this happen. Thanks
3
2832
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one dataset to data in a second dataset, using a common key. I will first describe the problem in words and then I will show my code, which has most of the solution done already. I have built an ASP.NET that queries an Index Server and returns a...
0
8009
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...
0
7939
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,...
0
8428
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8078
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
8299
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
6753
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
3919
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...
0
3964
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2442
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

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.