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
|
|||
|
|||
Optional field
Hi,
I'm building a db that stores information about field conditions at different locations. These attributes include landform, environmental setting, current land use, erosion, and vegetation. Of course the end user wants a "other" choice for all of these attributes to avoid having a list of choices a mile long. This by itself is not a problem, however, when other is selected, they want a text field to fill in what that "other" is. The attributes of the location are in 2 tables: one for setting, landform, and vegetation and the other table for the remaining two. The reason they had to be split was the user also records percentage of each setting/landform/vegetation that covers the area. Land use and erosion are simply presence/absence of the various types. The PK for each of these tables is a multifield of LocationID,Type, and Description (since each location can only have one record of each Description). I figured that having a blank field at the end of each of these tables for this information that is only used occasionally is not the best way to go but can't figure out a better way (been staring at this thing for too long). Any suggestions would be greatly appreciated. Thanks! |
#2
|
|||
|
|||
Optional field
An additional free-form text field where the user can enter an explanation
for the "other" choice makes good sense. Perhaps you could just call it a comment field and be done with it, i.e. they can add a comment regardless of their choice. Alternatively you could set up your lookup table (the choices in the combo) with a yes/no field to indicate whether a comment is expected (as it is in the case of Other), and give the user a warning if they choose this one but did not fill in a comment. -- 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. "Shaun" wrote in message news I'm building a db that stores information about field conditions at different locations. These attributes include landform, environmental setting, current land use, erosion, and vegetation. Of course the end user wants a "other" choice for all of these attributes to avoid having a list of choices a mile long. This by itself is not a problem, however, when other is selected, they want a text field to fill in what that "other" is. The attributes of the location are in 2 tables: one for setting, landform, and vegetation and the other table for the remaining two. The reason they had to be split was the user also records percentage of each setting/landform/vegetation that covers the area. Land use and erosion are simply presence/absence of the various types. The PK for each of these tables is a multifield of LocationID,Type, and Description (since each location can only have one record of each Description). I figured that having a blank field at the end of each of these tables for this information that is only used occasionally is not the best way to go but can't figure out a better way (been staring at this thing for too long). Any suggestions would be greatly appreciated. Thanks! |
#3
|
|||
|
|||
Optional field
So you're saying it would be ok to just add a comment field into the 2 main
tables? I was worried that being it will only be occasionally used, it wouldn't be the best design and that there might be a better way to do it. I was going to control the text box control on the form based on the after update event of the combo box with the choices in it, such that the text box would only be visible when "Other" was chosen. Thanks for the idea of the Yes/No in the lookup table, that might come in handy in a few other places. "Allen Browne" wrote: An additional free-form text field where the user can enter an explanation for the "other" choice makes good sense. Perhaps you could just call it a comment field and be done with it, i.e. they can add a comment regardless of their choice. Alternatively you could set up your lookup table (the choices in the combo) with a yes/no field to indicate whether a comment is expected (as it is in the case of Other), and give the user a warning if they choose this one but did not fill in a comment. -- 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. "Shaun" wrote in message news I'm building a db that stores information about field conditions at different locations. These attributes include landform, environmental setting, current land use, erosion, and vegetation. Of course the end user wants a "other" choice for all of these attributes to avoid having a list of choices a mile long. This by itself is not a problem, however, when other is selected, they want a text field to fill in what that "other" is. The attributes of the location are in 2 tables: one for setting, landform, and vegetation and the other table for the remaining two. The reason they had to be split was the user also records percentage of each setting/landform/vegetation that covers the area. Land use and erosion are simply presence/absence of the various types. The PK for each of these tables is a multifield of LocationID,Type, and Description (since each location can only have one record of each Description). I figured that having a blank field at the end of each of these tables for this information that is only used occasionally is not the best way to go but can't figure out a better way (been staring at this thing for too long). Any suggestions would be greatly appreciated. Thanks! |
#4
|
|||
|
|||
Optional field
Yes, that's correct, Shaun. Just go ahead and use the additional field.
As a general rule, you don't want heaps of rarely-used fields, so I understand what you are saying. But for this case (and lots of others), it makes sense to keep the design really simple, even though the field is not used most of the time. The fact that the field can be used as a free-form comment with any value indicates that it is not really unnormalized, i.e. there is not conflict/error if there is a comment beside a choose other than "Other." You will probably want to use the combo's Exit or LostFocus event to determine where to go next. AfterUpdate is too early (SetFocus appears not to work as Access afterwards moves to the next control in the tab order), and it would not have the desired effect when tabbing through an existing record. -- 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. "Shaun" wrote in message ... So you're saying it would be ok to just add a comment field into the 2 main tables? I was worried that being it will only be occasionally used, it wouldn't be the best design and that there might be a better way to do it. I was going to control the text box control on the form based on the after update event of the combo box with the choices in it, such that the text box would only be visible when "Other" was chosen. Thanks for the idea of the Yes/No in the lookup table, that might come in handy in a few other places. "Allen Browne" wrote: An additional free-form text field where the user can enter an explanation for the "other" choice makes good sense. Perhaps you could just call it a comment field and be done with it, i.e. they can add a comment regardless of their choice. Alternatively you could set up your lookup table (the choices in the combo) with a yes/no field to indicate whether a comment is expected (as it is in the case of Other), and give the user a warning if they choose this one but did not fill in a comment. "Shaun" wrote in message news I'm building a db that stores information about field conditions at different locations. These attributes include landform, environmental setting, current land use, erosion, and vegetation. Of course the end user wants a "other" choice for all of these attributes to avoid having a list of choices a mile long. This by itself is not a problem, however, when other is selected, they want a text field to fill in what that "other" is. The attributes of the location are in 2 tables: one for setting, landform, and vegetation and the other table for the remaining two. The reason they had to be split was the user also records percentage of each setting/landform/vegetation that covers the area. Land use and erosion are simply presence/absence of the various types. The PK for each of these tables is a multifield of LocationID,Type, and Description (since each location can only have one record of each Description). I figured that having a blank field at the end of each of these tables for this information that is only used occasionally is not the best way to go but can't figure out a better way (been staring at this thing for too long). Any suggestions would be greatly appreciated. Thanks! |
#5
|
|||
|
|||
Optional field
Thanks Allen!
"Allen Browne" wrote: Yes, that's correct, Shaun. Just go ahead and use the additional field. As a general rule, you don't want heaps of rarely-used fields, so I understand what you are saying. But for this case (and lots of others), it makes sense to keep the design really simple, even though the field is not used most of the time. The fact that the field can be used as a free-form comment with any value indicates that it is not really unnormalized, i.e. there is not conflict/error if there is a comment beside a choose other than "Other." You will probably want to use the combo's Exit or LostFocus event to determine where to go next. AfterUpdate is too early (SetFocus appears not to work as Access afterwards moves to the next control in the tab order), and it would not have the desired effect when tabbing through an existing record. -- 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. "Shaun" wrote in message ... So you're saying it would be ok to just add a comment field into the 2 main tables? I was worried that being it will only be occasionally used, it wouldn't be the best design and that there might be a better way to do it. I was going to control the text box control on the form based on the after update event of the combo box with the choices in it, such that the text box would only be visible when "Other" was chosen. Thanks for the idea of the Yes/No in the lookup table, that might come in handy in a few other places. "Allen Browne" wrote: An additional free-form text field where the user can enter an explanation for the "other" choice makes good sense. Perhaps you could just call it a comment field and be done with it, i.e. they can add a comment regardless of their choice. Alternatively you could set up your lookup table (the choices in the combo) with a yes/no field to indicate whether a comment is expected (as it is in the case of Other), and give the user a warning if they choose this one but did not fill in a comment. "Shaun" wrote in message news I'm building a db that stores information about field conditions at different locations. These attributes include landform, environmental setting, current land use, erosion, and vegetation. Of course the end user wants a "other" choice for all of these attributes to avoid having a list of choices a mile long. This by itself is not a problem, however, when other is selected, they want a text field to fill in what that "other" is. The attributes of the location are in 2 tables: one for setting, landform, and vegetation and the other table for the remaining two. The reason they had to be split was the user also records percentage of each setting/landform/vegetation that covers the area. Land use and erosion are simply presence/absence of the various types. The PK for each of these tables is a multifield of LocationID,Type, and Description (since each location can only have one record of each Description). I figured that having a blank field at the end of each of these tables for this information that is only used occasionally is not the best way to go but can't figure out a better way (been staring at this thing for too long). Any suggestions would be greatly appreciated. Thanks! |
Thread Tools | |
Display Modes | |
|
|