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
|
|||
|
|||
Lookup fields - what's wrong with them?
Hi,
I found the following commandment on the www.mvps.org/access site. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One. Can some wise person please educate me in what is wrong with lookup fields and what am I meant to use in their place? thanks Nathan |
#2
|
|||
|
|||
Lookup fields - what's wrong with them?
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! So, I going state that as long as they work for you, the more power to you! 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 desing level is the the thing to advoid. 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 builder 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). 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 remove!). ! 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 lean the corect skills for other sql database systems you will use. -- Albert D. Kallal (MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
#3
|
|||
|
|||
Lookup fields - what's wrong with them?
Albert,
Thanks for that explaination. Nathan "Albert D. Kallal" wrote in message ... 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! So, I going state that as long as they work for you, the more power to you! 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 desing level is the the thing to advoid. 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 builder 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). 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 remove!). ! 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 lean the corect skills for other sql database systems you will use. -- Albert D. Kallal (MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
Thread Tools | |
Display Modes | |
|
|