Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 4th, 2003, 08:22 PM
Registered User
 
Join Date: Dec 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Lookup Tables

I have two tables News & Zones. In the table news I have a lookup field called NewsZone that refrences the table Zones like this:

SELECT ZoneNum, ZoneDesc FROM zones

Except I only want the ZoneNum in the News table, The ZoneDesc it a text description that should appear in the select box to make it easy to select a zone.

Another words how do you build a lookup table to show the text description but when selected return the corresponding id number?


 
Old December 5th, 2003, 09:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Continue to use both columns in your dropdown list; however, keep the Bound Column property set to 1. Next change the Column Count property to 2. Set the Column Widths property to, for example, 1" and 3". Then set the list width to 4" (i.e. 1 + 3). When you click on the item, the dropdown will show both columns, however, it will only use column 1 for data.

Note that this structure in a table (putting dropdowns in it) is poo-pooed by a lot of people. Choosing from dropdowns best done in forms, not tables.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old December 5th, 2003, 11:02 AM
Registered User
 
Join Date: Dec 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Big Thanks SerranoG

I got it to work but I will admit I have herd this rumor before do you know what the story is? Possibly slower performance or poor data structure.

It would be easy to use a form control and map the txt desc to an id #.

mossimo


 
Old December 5th, 2003, 11:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by mossimo
I got it to work but I will admit I have herd this rumor before do you know what the story is? Possibly slower performance or poor data structure.
It has to do with data integrity and structure. If you're the only person working on this database and table then it's perfectly all right to do it this way. You're the only person dealing with the data.

However, the instant you share this with other people, it becomes a poor practice because users should never edit data directly in a table. They should use a form. Dropdowns and the like are created and structured in the form. The table's purpose is just to hold the data... period. Therefore, you don't put dropdown elements in your table.

I can see the allure of making dropdowns in tables because whenever you create a form based on this table and bring in that field, it will automatically turn into a dropdown with the proper rowsource. However, some folks say it's even bad structure to base a form on a table or query and that you should always use unbound forms. It's all a matter of standard practices and uses.




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old December 5th, 2003, 11:27 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Slower performance,bad structure, inability to export the data to a SQL Server. It is not a feature of access it is a "creature". It was meant to be used only for the non developers, basically MS Office Users. Just like the switchboard (hate it too, there is another thread on that one). Use dropdowns on forms only. Also do not use subdata sheets in tables.



Sal





Similar Threads
Thread Thread Starter Forum Replies Last Post
LookUp and Hyperlink frankjuel Excel VBA 1 January 12th, 2007 03:03 AM
lookup using VB karebear VB How-To 1 August 2nd, 2006 04:32 PM
lookup function Vince_421 Access VBA 14 May 19th, 2006 07:27 AM
Using lookup tables in Access JohnD Access 4 September 18th, 2004 05:39 AM
File name lookup acdsky Classic ASP Basics 3 November 22nd, 2003 11:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.