If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
why are look-up fields cautioned against?
I've seen it recommended in various postings and database websites that you
should avoid having look-up fields in your tables. I'd like to understand this better. Why do experts advise against them, and how can you create relationships between tables without using a look-up field? |
#2
|
|||
|
|||
why are look-up fields cautioned against?
Dear dbnDavid:
Before going on, please be aware that the problem with "look up fields" is their use in table datasheets - *not* with looking up data in separate tables... The following link explains the problems: http://www.mvps.org/access/lookupfields.htm Here's a quote from a post by expert John Vinson: That's because the Lookup field type is very limited, misleading, misdesigned, and all but useless. I'd recommend that it NEVER be used, period. To get values from another table, there are two techniques that are better (IMHO) than the Lookup tripe. One would be to use a Query linking the two tables - if you're creating a Report, this is a good choice. The other, better for data entry and browsing, is to create a Form based on your table and use combo boxes on the Form. These should be based on your "lookup" tables, using the numeric ID as the bound column but the meaningful name as the first nonzero width value - you'll see the name, the computer will see the ID, and you'll both be happy. To get one combo dependent on another combo (on a Form, it won't work on a table datasheet), you need to base the second combo on a query which uses the first combo as a criterion. Create and save a Query as the RowSource for Combo2, using =Forms![NameOfYourForm]![Combo1] as a criterion. Then, in the AfterUpdate event of Combo1, click the ... icon, select Code Builder, and put one line of code between the Sub and End Sub lines that Access will give you: Private Sub Combo1_AfterUpdate() Me!Combo2.Requery End Sub (changing the combo names to those in your form, of course). John W. Vinson[MVP] "dbnDavid" wrote in message news I've seen it recommended in various postings and database websites that you should avoid having look-up fields in your tables. I'd like to understand this better. Why do experts advise against them, and how can you create relationships between tables without using a look-up field? |
#3
|
|||
|
|||
why are look-up fields cautioned against?
Fred did a great job of answering your question except for the question
regarding creating relationships. For that, you use the relationship builder. (Tools, Relationships). Before doing that; however, you need to address your table structures so each table has a Primary Key and an Child tables have, in addition to their own Primary Key, a Foreign Key that is used in the relationship. For example, you have an order table and an order detail table. The order table contains information about the order and the order detail table contains 1 or more records for each order that describes information about what was ordered: tblOrder ORDER_ID (AutoNumber - Primary Key) ORDER_NUMBER ORDER_DATE CUSTOMER_ID (Long Integer - Foreign Key to customer table to get customer info) tblOrderDetail ODR_DTL_ID (AutoNumber - Primary Key) ORDER_ID (Long Interger - Foreign Key to Order table to associate this line to the correct order) PRODUCT_ID (Long Integer - Foreign Key to Locate product ordered) ODR_QTY ODR_PRICE Then in the Relationship builder you associate the ORDER_ID of the Order table with ORDER_ID in the Order Detail table. You also associate the CUSTOMER_ID of the customer table to the CUSTOMER_ID of the order table and the PRODUCT_ID of the Inventory table to the PRODUCT_ID of the order detail table. "dbnDavid" wrote: I've seen it recommended in various postings and database websites that you should avoid having look-up fields in your tables. I'd like to understand this better. Why do experts advise against them, and how can you create relationships between tables without using a look-up field? |
#4
|
|||
|
|||
why are look-up fields cautioned against?
Thank very much Fred and Klatuu. I'm continuing to digest the info, but it
has certainly clarified things for me. The short message I get is that lookup fields do have to be present in a table as a necessary part of table relationships, but that's all you ask them to do! |
#5
|
|||
|
|||
why are look-up fields cautioned against?
Sorry, that is not what they are for. Here is a quote from Access Help that
may clarify their use for you: A Lookup field provides a list of values that you can choose from when you are entering data. This makes data entry easier and ensures the consistency of the data in that field. A Lookup field can get its list of values from a table or query, or from a fixed set of values that you have specified. "dbnDavid" wrote: Thank very much Fred and Klatuu. I'm continuing to digest the info, but it has certainly clarified things for me. The short message I get is that lookup fields do have to be present in a table as a necessary part of table relationships, but that's all you ask them to do! |
#6
|
|||
|
|||
why are look-up fields cautioned against?
"dbnDavid" wrote in message
... The short message I get is that lookup fields do have to be present in a table as a necessary part of table relationships, but that's all you ask them to do! Not at all. I always have relationships in my databases, and I've never had a lookup field in any of them. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
#7
|
|||
|
|||
why are look-up fields cautioned against?
Dear David:
At the risk of making it look like you are being mobbed... No. Lookup fields are not necessary. Looking up data in related tables *is*, but *not* using lookup fields. When I started with Access it took several tries before one of the experts here actually got through to me on this... Maybe an example might help? (Apologies if this seems simplistic...) Suppose you have a database listing customers. They live all over the world. Every customer address includes a city. . tblCustomer CustomerID Name City Aha! You realize that you should have a separate table for city names! So you create a table for cities, maybe something like this: tblCities CityID CityName So, you want to change the customer database, right? tblCustomer CustomerID Name Ci... Hold it... you think.. shouldn't that be a lookup field? I mean, I want to "look up" that information in the "cities" table, right? At this point, you need to stop. Don't create a lookup field in the "customer" table. Create a field called "CityID". Make it the "number" datatype. Now, close and save the new table design. Go to ToolsRelationships (or click on the button). Add both tables to the design form. Click on the "CityID" field in tblCustomer and drag it on top of the "CityID" field in tblCities. A relationship window opens up. Check the enforce referential integrity checkbox, the click on "Create". You have created the necessary relationship. Now... what next? Well, you *don't* use the datasheet to do data entry. The next step is to create a form. (Use the wizard...). Then open this form in design view. Click on the combobox button in the toolbox. Drag a combobox on the form. The combobox wizard will run. Choose the option to look up the values in another table. Follow the wizard. Congrats! You have a combobox which presents a list of cities to choose from. The combobox is bound to the CityID field in tblCustomer. So you can "look up" the data in a related table, but you don't have a "lookup field" in the Customer table... HTH Fred P.S. BTW, thanks Klaatu for picking up my fumble earlier in the thread. P.P.S. Of couse tblCities doesn't necessarily need a CityID field (Primary key could be CityName)... P.P.P.S. And, of course, one could also use a form/subform.. P.P.P.P.S. We'll be here when you try to figure out exactly how the combobox works! "dbnDavid" wrote in message ... Thank very much Fred and Klatuu. I'm continuing to digest the info, but it has certainly clarified things for me. The short message I get is that lookup fields do have to be present in a table as a necessary part of table relationships, but that's all you ask them to do! |
#8
|
|||
|
|||
why are look-up fields cautioned against?
P.S. BTW, thanks Klaatu for picking up my fumble earlier in the thread.
Dang it...sorry, that should have been "Klatuu"... Ferd |
#9
|
|||
|
|||
why are look-up fields cautioned against?
On Wed, 19 Apr 2006 18:49:15 -0400, "Fred Boer"
wrote: P.P.S. Of couse tblCities doesn't necessarily need a CityID field (Primary key could be CityName)... ummm... Las Vegas is a big and strange city in Nevada. Las Vegas is a medium-sized pleasant city in New Mexico. Las Vegas is a small town in California. Las Vegas is... g Names don't usually make good primary keys! John W. Vinson[MVP] |
#10
|
|||
|
|||
why are look-up fields cautioned against?
Dear John:
Sigh. Thanks for keeping me on the straight and narrow. Could I explore this a bit more, though? What's the best way to cope with this? I have a city table which I use to list the place of publication for books. I also have a Province/State table listing, well, province and state names (used for Patron address information, actually)... Until now, I've just ignored the fact that different places might share the same name, but your correction has made me think. Should I do something like this if I wanted to be more accurate? (And I suppose that country name data would possibly be involved, but let's limit it to provinces and U.S. States...) tblCity CityID CityName ProvinceOrStateID tblProvinceOrState ProvinceOrStateID ProvinceOrStateName Thanks! Fred "John Vinson" wrote in message ... On Wed, 19 Apr 2006 18:49:15 -0400, "Fred Boer" wrote: P.P.S. Of couse tblCities doesn't necessarily need a CityID field (Primary key could be CityName)... ummm... Las Vegas is a big and strange city in Nevada. Las Vegas is a medium-sized pleasant city in New Mexico. Las Vegas is a small town in California. Las Vegas is... g Names don't usually make good primary keys! John W. Vinson[MVP] |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Invisible Multiple Fields in PageHeaders (Word 2003) | Roberto Villa Real | Mailmerge | 4 | September 24th, 2005 10:53 PM |
improving performance by indexing query criteria fields | Paul James | General Discussion | 20 | February 16th, 2005 07:55 PM |
improving performance by indexing query criteria fields | Paul James | Running & Setting Up Queries | 20 | February 16th, 2005 07:55 PM |
Can't Add Fields to Form | Jeff Miller | Using Forms | 4 | January 12th, 2005 03:42 AM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |