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 |
#11
|
|||
|
|||
why are look-up fields cautioned against?
On Wed, 19 Apr 2006 20:21:06 -0400, "Fred Boer"
wrote: 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 There are actually two cities named Los Alamos in New Mexico (one's off on a dirt road twenty miles from Las Vegas, oddly enough) so you can't even count on a two-field city/state index. I don't know if there IS a really good way, though the US Postal Service's ZIP code database (with its Preferred and Alternate City Names) may be the best bet... John W. Vinson[MVP] |
#12
|
|||
|
|||
why are look-up fields cautioned against?
Clearly there is potential murkiness with storing CityID by itself. If one
Los Alamos changes its name there is no way of editing the record in tblCity without every Los Alamos assuming the new name. The combo box from which City is selected could be a two-column combo box that includes the Zip code, but what happens is the municipality grows to the point where it has two or more Zip codes, or if a larger city has two or more Zip codes to start with, or if there is Zip code from, say, Las Vegas that has not yet been used? Since City is a single field I would be inclined to store the name rather than the ID, although a database that includes only local addresses may be handled differently from one that includes a nationwide listing in which some towns may appear only once. So many decisions... "John Vinson" wrote in message ... On Wed, 19 Apr 2006 20:21:06 -0400, "Fred Boer" wrote: 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 There are actually two cities named Los Alamos in New Mexico (one's off on a dirt road twenty miles from Las Vegas, oddly enough) so you can't even count on a two-field city/state index. I don't know if there IS a really good way, though the US Postal Service's ZIP code database (with its Preferred and Alternate City Names) may be the best bet... John W. Vinson[MVP] |
#13
|
|||
|
|||
why are look-up fields cautioned against?
Thanks to you both!
Fred "BruceM" wrote in message ... Clearly there is potential murkiness with storing CityID by itself. If one Los Alamos changes its name there is no way of editing the record in tblCity without every Los Alamos assuming the new name. The combo box from which City is selected could be a two-column combo box that includes the Zip code, but what happens is the municipality grows to the point where it has two or more Zip codes, or if a larger city has two or more Zip codes to start with, or if there is Zip code from, say, Las Vegas that has not yet been used? Since City is a single field I would be inclined to store the name rather than the ID, although a database that includes only local addresses may be handled differently from one that includes a nationwide listing in which some towns may appear only once. So many decisions... "John Vinson" wrote in message ... On Wed, 19 Apr 2006 20:21:06 -0400, "Fred Boer" wrote: 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 There are actually two cities named Los Alamos in New Mexico (one's off on a dirt road twenty miles from Las Vegas, oddly enough) so you can't even count on a two-field city/state index. I don't know if there IS a really good way, though the US Postal Service's ZIP code database (with its Preferred and Alternate City Names) may be the best bet... John W. Vinson[MVP] |
#14
|
|||
|
|||
why are look-up fields cautioned against?
On Thu, 20 Apr 2006 07:41:51 -0400, "BruceM"
wrote: Clearly there is potential murkiness with storing CityID by itself. If one Los Alamos changes its name there is no way of editing the record in tblCity without every Los Alamos assuming the new name. The combo box from which City is selected could be a two-column combo box that includes the Zip code, but what happens is the municipality grows to the point where it has two or more Zip codes, or if a larger city has two or more Zip codes to start with, or if there is Zip code from, say, Las Vegas that has not yet been used? Since City is a single field I would be inclined to store the name rather than the ID, although a database that includes only local addresses may be handled differently from one that includes a nationwide listing in which some towns may appear only once. So many decisions... What I've usually done is use a Zip table with fields for Zip, City, and (redundantly though it helps for filtering) State. I've got a generic Address subform which filters the City combo box on updating the Zip, and vice versa; if selecting a zip uniquely identifies a city (typical) it just fills it in, and if selecting a city uniquely identifies a zip (small towns), it just fills THAT in. John W. Vinson[MVP] |
#15
|
|||
|
|||
why are look-up fields cautioned against?
Dear Fred
It's been some time since your posting that I'm replying to here, but I just needed to tell you that your advice really gave me a big push upwards on my Access learning curve. I'm glad I asked the question and I'm continuing to absorb and digest what I what you've taught me here. Thanks very much! "Fred Boer" wrote: 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! |
#16
|
|||
|
|||
why are look-up fields cautioned against?
Happy to been of help, David! Good luck in your project!
All the best! Fred "dbnDavid" wrote in message ... Dear Fred It's been some time since your posting that I'm replying to here, but I just needed to tell you that your advice really gave me a big push upwards on my Access learning curve. I'm glad I asked the question and I'm continuing to absorb and digest what I what you've taught me here. Thanks very much! "Fred Boer" wrote: 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! |
|
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 |