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 |
#21
|
|||
|
|||
Lookup Evils
When you say you "can create a relationship between tblMeasurementUnits and
tblHorizontalSurveyData and enforce referential integrity," what exactly do you mean? I can guess that the relationship is between CoordinateUnit and Unit, but to what end? Some sample data may help. Are you using a compound PK in the first two tables? I would guess that StationID has something to do with a Station table you haven't mentioned. If so, it should be a foreign key in tblWellCasingDetails and in tblHorizontalSurveyData, not the PK. If WellCasingDetails are details for a particular station, does that mean there could be any number of details for a station. That is, is the situation one station many well casing details? Remember that a PK uniquely identifies the record. It could be a single field or a combination of fields. If it is a single field it could be something arbitrary such as autonumber, or it could be a unique numbering system your company uses. Invoice number and quote numbers are typical examples of the latter. If it is a combination of field, you must be very careful to assure the combination is truly unique. You have provided information about the tables, but it may help to back up and describe something of the real-world situation behind the database. "Jessi" wrote in message ... Thank you for the reply Evi. I thought I would provide a more concrete example. tblWellCasingDetails StationID (pk) ComponentID (pk) ComponentDescription InnerDiameter InnerDiameterUnit TopZoneDepth BottomZoneDepth DepthUnit DepthReference Comments tblHorizontalSurveyData StationID (pk) SurveyDate SurveyGeneration (pk) CurrentSurvey SurveyCompany XCoordinate YCoordinate CoordinateUnit CoordinateSystem HorizontalDatum Notes tblMeasurementUnits Unit (pk) UnitFullName MeasurementSystem MeasurementType The first two tables above both contain fields which require an input for the "Unit". In fact, tbl:WellCasingDetails has two fields which require an input for the "Unit" (InnerDiameterUnit and DepthUnit). The table tbl:MeasurementUnits is meant to serve as a look up table for the two tables above and several others. I can create a relationship between tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential integrity. However, I run into a problem when I try to create a relationship from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to the tbl:MeasurementUnits. I can create the relationships, but cannot enforce referential integrity. Any suggestions? Jessi "Evi" wrote: .. Putting the units in another table is an excellent idea. It is indeed good design to create a relationship. However, if you add a field to an existing table, you may need to run a series of Update Queries to get you started before you join up the tables and 'Enforce Referential Integrity' You may even need to update some of the records by hand (using your combo box in a form of course) before you can join your tables in the Relationships Window. (I've even had to resort to calling one of the items in my 'lookup' table 'Unknown' because the unit that the inputter used could have been almost anything. But this just highlighted the necessity for the table) You can add a 'lookup' table more than once to the relationship window or in a query grid so it can join to more than one table but I have a sinking feeling that you may have a design problem when you say that you have many tables in which there is a Unit field - but then maybe I'm being unnecessarily gloomy. Evi "Jessi" wrote in message ... My question is related to the whole discussion of "lookup evils". I now understand the logic of only including the user interface (the lookup) in the form rather than directly in the table. If you have many tables in which there is a "Unit" field. The unit field stores a measurement unit (ft, cm, m, L, gallon, etc.). As nearly every table in the database contains a "Unit" field I wanted to create a lookup table in which to store the appropriate units so that users are forced to enter the unit in a standard form. I see that I can do this by using a combo box at the form level. But, is it necessary or good design to create a relationship between the table data is being entered into and the input table? I apologize if this question isn't worded very well, I'm still learning the terminology. Jessi "BruceM" wrote: Thanks for pointing that out. I did a little more experimenting, and found no problems in sorting by the lookup field when it uses a value list. I expect it would be the same if I used a one-column row source query. However, if I'm going to construct a combo box anyhow I'll just keep doing it on a form. I see no use for a user interface in a table. "Pat Hartman(MVP)" wrote in message ... Everything the referenced link says is correct but there really are cases where the Lookups won't cause any harm. Let me try to separate the times when a lookup causes a problem and when it doesn't. Lookup not OK: 1. you have a table of values and that table has a primary key that is different from the value you want to display. For example, you have a table of department names and each department has an ID. Adding a lookup at the table level will cause problems with both nested queries and VBA code due to the confusion between the departmentID and the departmentName. Lookup OK: 1. You have a table of values and the primary key is the value you want to display. An example might be a State code table. This won't work if you want to display the StateName. In that case the key field would be different from the display field. 2. You have a small list of values and the value you display is the value you want to save. An example might be Male, Female, Unknown. If the value you want to save is a code - M, F, U then you should not use a table level lookup. Keep in mind that combos and listboxes work fine with text values but you need numeric values for option groups so if you want to use an option group on a form, you'll have to work around the numeric/text conversion problem with code. In ALL cases, lookups on Forms do not cause problems. If you are not using lookup value tables, you need to be very careful to keep multiple instances of combos in sync. You will keep a validation rule at the table level to ensure that bad data cannot be entered and use combos on forms to provide pick lists but you will be responsible for making the same changes to the table field's validation rule property and all combos based on that list. The next version of Access will take care of this little problem of keeping the value list and the combos in sync but the current and older versions do not. That is why most professionals rely on a table. I happen to use a common table for all my simple lookups. It is essentially a table within a table. I have forms and reports and a table that I add to all my applications. "HelenJ" wrote in message ... I have been reading all about the evils of lookups in tables, however could someone please explain how you avoid using them - is the answer to have many tiny tables with potentially only 2 fields (assuming it is necessary to have a PK in every table) and often only 2 records. I am just starting the design of a new database and I seem to have several fields which have 2 or sometimes 3 options and it seems a lot to create a table for this. Many thanks Helen |
#22
|
|||
|
|||
Lookup Evils
Yes, some background information about the real world situation would be
useful. I'm a PhD student in hydrogeology and as a result I conduct groundwater research. Most of the data I collect from my research comes from boreholes (holes drilled in the ground) and later from the wells installed in those boreholes. Each borehole is a station (so yes, there is a tblStationDetails). Each borehole/well is uniquely named. I've been working on learning how to design/build a database for the site data (which spans over 20 years) because historically the data has been stored/manipulated in excel spreadsheets (big nightmare). I would like other students and consultants, most of whom will have limited to no experience with databases and won't necessarily be familiar with all the different types of data, to be able to input data into the database and extract data from it while maintaining he integrity of the data. I collect many different types of data from each borehole. Two types of data collected for each borehole are the survey coordinates for the borehole (tblHorizontalSurveyData ) and details about the steel casing installed in each borehole (tblWellCasingDetails). The survey coordinates for the borehole can be measured in several different units depending on who did the survey (typically in feet or meters). The details regarding the steel casing installed in each borehole include the top and bottom depth of each piece of casing (can be multiple pieces in each borehole) and the diameter of the casing. The depths are typically measured in several different units (feet or meters) and the diameters can be given in several different units (inches or centimeters). So, I want to make sure that users entering this data have to choose from a list of units rather than entering the unit into a text box where they can potentially use many different forms (abbreviated form, full form, etc.). Its important that the form for the unit for each piece of data be consistent because subsequent queries will convert the data into the units desired for different types of reports (when we report to the site owner we use imperial units and when we write for scientific journals we use metric units). I gathered from the previous discussion that building the constraint that users pick from a list of possible unit values is best done at the form level rather than the table level. The question that I had was is it necessary/good design to create a relationship between the unit field in the tblMeasurementUnits (pk) and to the unit field in the tblHorizontalSurveyData (fk), for example. Because each piece of data (record) in many different tables has an associated unit many relationships would need to be created between each table and the tblMeasurementUnits. Or if its acceptable to implement that constaint in the combo box on a form by choosing "limit to list" in the combo box properties menu. Or, by choosing "limit to list" in the combo box properties menue is Access actually creating that relationship for me? I'll try to provide some answers to your questions about the primary keys. For the tblWellCasingDetails the primary key is a compound key composed of the StationID field and the ComponentID field. This is necessary because the component ID identifies a type of component (steel casing, PVC casing, etc) and as such will be repeated for many stations. The same is true for the tblHorizontalSurveyData (compound primary key made up of the StationID field and the SurveyGeneration field). The tblMeasurementUnits has a simple primary key of the field Units. I'm fairly certain the relationship between the Units field in the tblMeasurementUnits and all the other tables is one to many. The Units field from the tblMeasurementUnits then shows up in the other tables as a foreign key. I hope this clarifies things. I'm still learning the vocabulary. I'm primarily interested in good design princple and whether that includes creating the relationships I described above to force the user to enter particular values stored in a table or whether that can just be set as a property of the combo box in the input form or if they end up being the same thing? Thank you for your help and time. "BruceM" wrote: When you say you "can create a relationship between tblMeasurementUnits and tblHorizontalSurveyData and enforce referential integrity," what exactly do you mean? I can guess that the relationship is between CoordinateUnit and Unit, but to what end? Some sample data may help. Are you using a compound PK in the first two tables? I would guess that StationID has something to do with a Station table you haven't mentioned. If so, it should be a foreign key in tblWellCasingDetails and in tblHorizontalSurveyData, not the PK. If WellCasingDetails are details for a particular station, does that mean there could be any number of details for a station. That is, is the situation one station many well casing details? Remember that a PK uniquely identifies the record. It could be a single field or a combination of fields. If it is a single field it could be something arbitrary such as autonumber, or it could be a unique numbering system your company uses. Invoice number and quote numbers are typical examples of the latter. If it is a combination of field, you must be very careful to assure the combination is truly unique. You have provided information about the tables, but it may help to back up and describe something of the real-world situation behind the database. "Jessi" wrote in message ... Thank you for the reply Evi. I thought I would provide a more concrete example. tblWellCasingDetails StationID (pk) ComponentID (pk) ComponentDescription InnerDiameter InnerDiameterUnit TopZoneDepth BottomZoneDepth DepthUnit DepthReference Comments tblHorizontalSurveyData StationID (pk) SurveyDate SurveyGeneration (pk) CurrentSurvey SurveyCompany XCoordinate YCoordinate CoordinateUnit CoordinateSystem HorizontalDatum Notes tblMeasurementUnits Unit (pk) UnitFullName MeasurementSystem MeasurementType The first two tables above both contain fields which require an input for the "Unit". In fact, tbl:WellCasingDetails has two fields which require an input for the "Unit" (InnerDiameterUnit and DepthUnit). The table tbl:MeasurementUnits is meant to serve as a look up table for the two tables above and several others. I can create a relationship between tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential integrity. However, I run into a problem when I try to create a relationship from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to the tbl:MeasurementUnits. I can create the relationships, but cannot enforce referential integrity. Any suggestions? Jessi "Evi" wrote: .. Putting the units in another table is an excellent idea. It is indeed good design to create a relationship. However, if you add a field to an existing table, you may need to run a series of Update Queries to get you started before you join up the tables and 'Enforce Referential Integrity' You may even need to update some of the records by hand (using your combo box in a form of course) before you can join your tables in the Relationships Window. (I've even had to resort to calling one of the items in my 'lookup' table 'Unknown' because the unit that the inputter used could have been almost anything. But this just highlighted the necessity for the table) You can add a 'lookup' table more than once to the relationship window or in a query grid so it can join to more than one table but I have a sinking feeling that you may have a design problem when you say that you have many tables in which there is a Unit field - but then maybe I'm being unnecessarily gloomy. Evi "Jessi" wrote in message ... My question is related to the whole discussion of "lookup evils". I now understand the logic of only including the user interface (the lookup) in the form rather than directly in the table. If you have many tables in which there is a "Unit" field. The unit field stores a measurement unit (ft, cm, m, L, gallon, etc.). As nearly every table in the database contains a "Unit" field I wanted to create a lookup table in which to store the appropriate units so that users are forced to enter the unit in a standard form. I see that I can do this by using a combo box at the form level. But, is it necessary or good design to create a relationship between the table data is being entered into and the input table? I apologize if this question isn't worded very well, I'm still learning the terminology. Jessi "BruceM" wrote: Thanks for pointing that out. I did a little more experimenting, and found no problems in sorting by the lookup field when it uses a value list. I expect it would be the same if I used a one-column row source query. However, if I'm going to construct a combo box anyhow I'll just keep doing it on a form. I see no use for a user interface in a table. "Pat Hartman(MVP)" wrote in message ... Everything the referenced link says is correct but there really are cases where the Lookups won't cause any harm. Let me try to separate the times when a lookup causes a problem and when it doesn't. Lookup not OK: 1. you have a table of values and that table has a primary key that is different from the value you want to display. For example, you have a table of department names and each department has an ID. Adding a lookup at the table level will cause problems with both nested queries and VBA code due to the confusion between the departmentID and the departmentName. Lookup OK: 1. You have a table of values and the primary key is the value you want to display. An example might be a State code table. This won't work if you want to display the StateName. In that case the key field would be different from the display field. 2. You have a small list of values and the value you display is the value you want to save. An example might be Male, Female, Unknown. If the value you want to save is a code - M, F, U then you should not use a table level lookup. Keep in mind that combos and listboxes work fine with text values but you need numeric values for option groups so if you want to use an option group on a form, you'll have to work around the numeric/text conversion problem with code. In ALL cases, lookups on Forms do not cause problems. If you are not using lookup value tables, you need to be very careful to keep multiple instances of combos in sync. You will keep a validation rule at the table level to ensure that bad data cannot be entered and use combos on forms to provide pick lists but you will be responsible for making the same changes to the table field's validation rule property and all combos based on that list. The next version of Access will take care of this little problem of keeping the value list and the combos in sync but the current and older versions do not. That is why most professionals rely on a table. I happen to use a common table for all my simple lookups. It is essentially a table within a table. I have forms and reports and a table that I add to all my applications. "HelenJ" wrote in message ... I have been reading all about the evils of lookups in tables, however could someone please explain how you avoid using them - is the answer to have many tiny tables with potentially only 2 fields (assuming it is necessary to have a PK in every table) and often only 2 records. I am just starting the design of a new database and I seem to have several fields which have 2 or sometimes 3 options and it seems a lot to create a table for this. Many thanks Helen |
#23
|
|||
|
|||
Lookup Evils
As I understand, the top level of the structure is the station. The table
may be something such as: tblStation StationID (primary key; could be the unique name) Location BoreholeWell (Borehole or well? Could be Y/N, I suppose.) BoreholeDate WellDate SurveyDate Surveyor etc. You mention tblStationDetails, but I don't see how that fits in, if indeed it is different from the tblStation I have suggested. You mention survey coordinates. Since you have used the plural, there must be more than one. Assuming there is one survey, and that a survey has a variable number of coordinates, you would need something like: tblHorizontalSurveyData SurveyID (primary key) StationID (foreign key; linked to StationID in tblStation) Coordinate CoordinateUnit (meters, feet, etc.) etc. I don't know how a coordinate is determined. You may need several pieces of information (x, y, and z axis, or something). If there can be several surveys, each survey needs its own record: tblSurvey SurveyID (PK) SurveyDate Surveyor etc. Note that SurveyDate and Surveyor would be removed from tblStation in this scenario. tblHorizontalSurveyData would be related to this table rather than to tblStation: tblHorizontalSurveyData HSDataID (primary key) SurveyID (foreign key; linked to SurveyID in tblSurvey) Coordinate CoordinateUnit (meters, feet, etc.) etc. tblWellCasingDetails may be something such as: tblWellCasingDetails CasingDetailsID (PK) StationID (FK to tblStation) SectionNumber TopDepth BottomDepth DepthUnit (feet, meters, etc.) Diameter DiameterUnit (inches, centimeters) It could be that there is a need for another level, similar to the second choice I suggested for the Survey. You may be overthinking the problem with measurement units. I don't think you need relationships to that table. If you have a Units table at all I expect it would be a lookup table (not to be confused with a lookup field). However, I would think a value list as the combo box row source may be all you need. If the row source type is Value List, the Row Source may be: inches;centimeters When it comes time to generate a report, you could have something like this as a field in the record source query: ConvertToCentimeter: IIf([DiameterUnit] = "inches",[Diameter] * 2.54,[Diameter]) and ConvertToInches: IIf([DiameterUnit] = "centimeters",[Diameter] * ..39,[Diameter]) If you have a large number of units (inches, millimeters, centimeters, microns, cubits, etc.) you could use a lookup table instead of a value list. In that case Limit To List would be adequate. In any case you could have something like this as the After Update event of the combo box: If Nz(Me.cboUnit,"") = "" Then MsgBox "You need to select a unit" Me.txtDiameter.Enabled = False Else Me.txtDiameter.Enabled = Ture End If txtDiameter is the text box bound to Diameter. "Jessi" wrote in message ... Yes, some background information about the real world situation would be useful. I'm a PhD student in hydrogeology and as a result I conduct groundwater research. Most of the data I collect from my research comes from boreholes (holes drilled in the ground) and later from the wells installed in those boreholes. Each borehole is a station (so yes, there is a tblStationDetails). Each borehole/well is uniquely named. I've been working on learning how to design/build a database for the site data (which spans over 20 years) because historically the data has been stored/manipulated in excel spreadsheets (big nightmare). I would like other students and consultants, most of whom will have limited to no experience with databases and won't necessarily be familiar with all the different types of data, to be able to input data into the database and extract data from it while maintaining he integrity of the data. I collect many different types of data from each borehole. Two types of data collected for each borehole are the survey coordinates for the borehole (tblHorizontalSurveyData ) and details about the steel casing installed in each borehole (tblWellCasingDetails). The survey coordinates for the borehole can be measured in several different units depending on who did the survey (typically in feet or meters). The details regarding the steel casing installed in each borehole include the top and bottom depth of each piece of casing (can be multiple pieces in each borehole) and the diameter of the casing. The depths are typically measured in several different units (feet or meters) and the diameters can be given in several different units (inches or centimeters). So, I want to make sure that users entering this data have to choose from a list of units rather than entering the unit into a text box where they can potentially use many different forms (abbreviated form, full form, etc.). Its important that the form for the unit for each piece of data be consistent because subsequent queries will convert the data into the units desired for different types of reports (when we report to the site owner we use imperial units and when we write for scientific journals we use metric units). I gathered from the previous discussion that building the constraint that users pick from a list of possible unit values is best done at the form level rather than the table level. The question that I had was is it necessary/good design to create a relationship between the unit field in the tblMeasurementUnits (pk) and to the unit field in the tblHorizontalSurveyData (fk), for example. Because each piece of data (record) in many different tables has an associated unit many relationships would need to be created between each table and the tblMeasurementUnits. Or if its acceptable to implement that constaint in the combo box on a form by choosing "limit to list" in the combo box properties menu. Or, by choosing "limit to list" in the combo box properties menue is Access actually creating that relationship for me? I'll try to provide some answers to your questions about the primary keys. For the tblWellCasingDetails the primary key is a compound key composed of the StationID field and the ComponentID field. This is necessary because the component ID identifies a type of component (steel casing, PVC casing, etc) and as such will be repeated for many stations. The same is true for the tblHorizontalSurveyData (compound primary key made up of the StationID field and the SurveyGeneration field). The tblMeasurementUnits has a simple primary key of the field Units. I'm fairly certain the relationship between the Units field in the tblMeasurementUnits and all the other tables is one to many. The Units field from the tblMeasurementUnits then shows up in the other tables as a foreign key. I hope this clarifies things. I'm still learning the vocabulary. I'm primarily interested in good design princple and whether that includes creating the relationships I described above to force the user to enter particular values stored in a table or whether that can just be set as a property of the combo box in the input form or if they end up being the same thing? Thank you for your help and time. "BruceM" wrote: When you say you "can create a relationship between tblMeasurementUnits and tblHorizontalSurveyData and enforce referential integrity," what exactly do you mean? I can guess that the relationship is between CoordinateUnit and Unit, but to what end? Some sample data may help. Are you using a compound PK in the first two tables? I would guess that StationID has something to do with a Station table you haven't mentioned. If so, it should be a foreign key in tblWellCasingDetails and in tblHorizontalSurveyData, not the PK. If WellCasingDetails are details for a particular station, does that mean there could be any number of details for a station. That is, is the situation one station many well casing details? Remember that a PK uniquely identifies the record. It could be a single field or a combination of fields. If it is a single field it could be something arbitrary such as autonumber, or it could be a unique numbering system your company uses. Invoice number and quote numbers are typical examples of the latter. If it is a combination of field, you must be very careful to assure the combination is truly unique. You have provided information about the tables, but it may help to back up and describe something of the real-world situation behind the database. "Jessi" wrote in message ... Thank you for the reply Evi. I thought I would provide a more concrete example. tblWellCasingDetails StationID (pk) ComponentID (pk) ComponentDescription InnerDiameter InnerDiameterUnit TopZoneDepth BottomZoneDepth DepthUnit DepthReference Comments tblHorizontalSurveyData StationID (pk) SurveyDate SurveyGeneration (pk) CurrentSurvey SurveyCompany XCoordinate YCoordinate CoordinateUnit CoordinateSystem HorizontalDatum Notes tblMeasurementUnits Unit (pk) UnitFullName MeasurementSystem MeasurementType The first two tables above both contain fields which require an input for the "Unit". In fact, tbl:WellCasingDetails has two fields which require an input for the "Unit" (InnerDiameterUnit and DepthUnit). The table tbl:MeasurementUnits is meant to serve as a look up table for the two tables above and several others. I can create a relationship between tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential integrity. However, I run into a problem when I try to create a relationship from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to the tbl:MeasurementUnits. I can create the relationships, but cannot enforce referential integrity. Any suggestions? Jessi "Evi" wrote: .. Putting the units in another table is an excellent idea. It is indeed good design to create a relationship. However, if you add a field to an existing table, you may need to run a series of Update Queries to get you started before you join up the tables and 'Enforce Referential Integrity' You may even need to update some of the records by hand (using your combo box in a form of course) before you can join your tables in the Relationships Window. (I've even had to resort to calling one of the items in my 'lookup' table 'Unknown' because the unit that the inputter used could have been almost anything. But this just highlighted the necessity for the table) You can add a 'lookup' table more than once to the relationship window or in a query grid so it can join to more than one table but I have a sinking feeling that you may have a design problem when you say that you have many tables in which there is a Unit field - but then maybe I'm being unnecessarily gloomy. Evi "Jessi" wrote in message ... My question is related to the whole discussion of "lookup evils". I now understand the logic of only including the user interface (the lookup) in the form rather than directly in the table. If you have many tables in which there is a "Unit" field. The unit field stores a measurement unit (ft, cm, m, L, gallon, etc.). As nearly every table in the database contains a "Unit" field I wanted to create a lookup table in which to store the appropriate units so that users are forced to enter the unit in a standard form. I see that I can do this by using a combo box at the form level. But, is it necessary or good design to create a relationship between the table data is being entered into and the input table? I apologize if this question isn't worded very well, I'm still learning the terminology. Jessi "BruceM" wrote: Thanks for pointing that out. I did a little more experimenting, and found no problems in sorting by the lookup field when it uses a value list. I expect it would be the same if I used a one-column row source query. However, if I'm going to construct a combo box anyhow I'll just keep doing it on a form. I see no use for a user interface in a table. "Pat Hartman(MVP)" wrote in message ... Everything the referenced link says is correct but there really are cases where the Lookups won't cause any harm. Let me try to separate the times when a lookup causes a problem and when it doesn't. Lookup not OK: 1. you have a table of values and that table has a primary key that is different from the value you want to display. For example, you have a table of department names and each department has an ID. Adding a lookup at the table level will cause problems with both nested queries and VBA code due to the confusion between the departmentID and the departmentName. Lookup OK: 1. You have a table of values and the primary key is the value you want to display. An example might be a State code table. This won't work if you want to display the StateName. In that case the key field would be different from the display field. 2. You have a small list of values and the value you display is the value you want to save. An example might be Male, Female, Unknown. If the value you want to save is a code - M, F, U then you should not use a table level lookup. Keep in mind that combos and listboxes work fine with text values but you need numeric values for option groups so if you want to use an option group on a form, you'll have to work around the numeric/text conversion problem with code. In ALL cases, lookups on Forms do not cause problems. If you are not using lookup value tables, you need to be very careful to keep multiple instances of combos in sync. You will keep a validation rule at the table level to ensure that bad data cannot be entered and use combos on forms to provide pick lists but you will be responsible for making the same changes to the table field's validation rule property and all combos based on that list. The next version of Access will take care of this little problem of keeping the value list and the combos in sync but the current and older versions do not. That is why most professionals rely on a table. I happen to use a common table for all my simple lookups. It is essentially a table within a table. I have forms and reports and a table that I add to all my applications. "HelenJ" wrote in message ... I have been reading all about the evils of lookups in tables, however could someone please explain how you avoid using them - is the answer to have many tiny tables with potentially only 2 fields (assuming it is necessary to have a PK in every table) and often only 2 records. I am just starting the design of a new database and I seem to have several fields which have 2 or sometimes 3 options and it seems a lot to create a table for this. Many thanks Helen |
#24
|
|||
|
|||
Lookup Evils
I think I've got my situation worked out/thought through now thanks to
everyones comments/suggestions. I learn a great deal from all of the postings and I'm sure other questions will come up in the future as I continue to work with Access. Thanks again. Jessi "BruceM" wrote: As I understand, the top level of the structure is the station. The table may be something such as: tblStation StationID (primary key; could be the unique name) Location BoreholeWell (Borehole or well? Could be Y/N, I suppose.) BoreholeDate WellDate SurveyDate Surveyor etc. You mention tblStationDetails, but I don't see how that fits in, if indeed it is different from the tblStation I have suggested. You mention survey coordinates. Since you have used the plural, there must be more than one. Assuming there is one survey, and that a survey has a variable number of coordinates, you would need something like: tblHorizontalSurveyData SurveyID (primary key) StationID (foreign key; linked to StationID in tblStation) Coordinate CoordinateUnit (meters, feet, etc.) etc. I don't know how a coordinate is determined. You may need several pieces of information (x, y, and z axis, or something). If there can be several surveys, each survey needs its own record: tblSurvey SurveyID (PK) SurveyDate Surveyor etc. Note that SurveyDate and Surveyor would be removed from tblStation in this scenario. tblHorizontalSurveyData would be related to this table rather than to tblStation: tblHorizontalSurveyData HSDataID (primary key) SurveyID (foreign key; linked to SurveyID in tblSurvey) Coordinate CoordinateUnit (meters, feet, etc.) etc. tblWellCasingDetails may be something such as: tblWellCasingDetails CasingDetailsID (PK) StationID (FK to tblStation) SectionNumber TopDepth BottomDepth DepthUnit (feet, meters, etc.) Diameter DiameterUnit (inches, centimeters) It could be that there is a need for another level, similar to the second choice I suggested for the Survey. You may be overthinking the problem with measurement units. I don't think you need relationships to that table. If you have a Units table at all I expect it would be a lookup table (not to be confused with a lookup field). However, I would think a value list as the combo box row source may be all you need. If the row source type is Value List, the Row Source may be: inches;centimeters When it comes time to generate a report, you could have something like this as a field in the record source query: ConvertToCentimeter: IIf([DiameterUnit] = "inches",[Diameter] * 2.54,[Diameter]) and ConvertToInches: IIf([DiameterUnit] = "centimeters",[Diameter] * ..39,[Diameter]) If you have a large number of units (inches, millimeters, centimeters, microns, cubits, etc.) you could use a lookup table instead of a value list. In that case Limit To List would be adequate. In any case you could have something like this as the After Update event of the combo box: If Nz(Me.cboUnit,"") = "" Then MsgBox "You need to select a unit" Me.txtDiameter.Enabled = False Else Me.txtDiameter.Enabled = Ture End If txtDiameter is the text box bound to Diameter. "Jessi" wrote in message ... Yes, some background information about the real world situation would be useful. I'm a PhD student in hydrogeology and as a result I conduct groundwater research. Most of the data I collect from my research comes from boreholes (holes drilled in the ground) and later from the wells installed in those boreholes. Each borehole is a station (so yes, there is a tblStationDetails). Each borehole/well is uniquely named. I've been working on learning how to design/build a database for the site data (which spans over 20 years) because historically the data has been stored/manipulated in excel spreadsheets (big nightmare). I would like other students and consultants, most of whom will have limited to no experience with databases and won't necessarily be familiar with all the different types of data, to be able to input data into the database and extract data from it while maintaining he integrity of the data. I collect many different types of data from each borehole. Two types of data collected for each borehole are the survey coordinates for the borehole (tblHorizontalSurveyData ) and details about the steel casing installed in each borehole (tblWellCasingDetails). The survey coordinates for the borehole can be measured in several different units depending on who did the survey (typically in feet or meters). The details regarding the steel casing installed in each borehole include the top and bottom depth of each piece of casing (can be multiple pieces in each borehole) and the diameter of the casing. The depths are typically measured in several different units (feet or meters) and the diameters can be given in several different units (inches or centimeters). So, I want to make sure that users entering this data have to choose from a list of units rather than entering the unit into a text box where they can potentially use many different forms (abbreviated form, full form, etc.). Its important that the form for the unit for each piece of data be consistent because subsequent queries will convert the data into the units desired for different types of reports (when we report to the site owner we use imperial units and when we write for scientific journals we use metric units). I gathered from the previous discussion that building the constraint that users pick from a list of possible unit values is best done at the form level rather than the table level. The question that I had was is it necessary/good design to create a relationship between the unit field in the tblMeasurementUnits (pk) and to the unit field in the tblHorizontalSurveyData (fk), for example. Because each piece of data (record) in many different tables has an associated unit many relationships would need to be created between each table and the tblMeasurementUnits. Or if its acceptable to implement that constaint in the combo box on a form by choosing "limit to list" in the combo box properties menu. Or, by choosing "limit to list" in the combo box properties menue is Access actually creating that relationship for me? I'll try to provide some answers to your questions about the primary keys. For the tblWellCasingDetails the primary key is a compound key composed of the StationID field and the ComponentID field. This is necessary because the component ID identifies a type of component (steel casing, PVC casing, etc) and as such will be repeated for many stations. The same is true for the tblHorizontalSurveyData (compound primary key made up of the StationID field and the SurveyGeneration field). The tblMeasurementUnits has a simple primary key of the field Units. I'm fairly certain the relationship between the Units field in the tblMeasurementUnits and all the other tables is one to many. The Units field from the tblMeasurementUnits then shows up in the other tables as a foreign key. I hope this clarifies things. I'm still learning the vocabulary. I'm primarily interested in good design princple and whether that includes creating the relationships I described above to force the user to enter particular values stored in a table or whether that can just be set as a property of the combo box in the input form or if they end up being the same thing? Thank you for your help and time. "BruceM" wrote: When you say you "can create a relationship between tblMeasurementUnits and tblHorizontalSurveyData and enforce referential integrity," what exactly do you mean? I can guess that the relationship is between CoordinateUnit and Unit, but to what end? Some sample data may help. Are you using a compound PK in the first two tables? I would guess that StationID has something to do with a Station table you haven't mentioned. If so, it should be a foreign key in tblWellCasingDetails and in tblHorizontalSurveyData, not the PK. If WellCasingDetails are details for a particular station, does that mean there could be any number of details for a station. That is, is the situation one station many well casing details? Remember that a PK uniquely identifies the record. It could be a single field or a combination of fields. If it is a single field it could be something arbitrary such as autonumber, or it could be a unique numbering system your company uses. Invoice number and quote numbers are typical examples of the latter. If it is a combination of field, you must be very careful to assure the combination is truly unique. You have provided information about the tables, but it may help to back up and describe something of the real-world situation behind the database. "Jessi" wrote in message ... Thank you for the reply Evi. I thought I would provide a more concrete example. tblWellCasingDetails StationID (pk) ComponentID (pk) ComponentDescription InnerDiameter InnerDiameterUnit TopZoneDepth BottomZoneDepth DepthUnit DepthReference Comments tblHorizontalSurveyData StationID (pk) SurveyDate SurveyGeneration (pk) CurrentSurvey SurveyCompany XCoordinate YCoordinate CoordinateUnit CoordinateSystem HorizontalDatum Notes tblMeasurementUnits Unit (pk) UnitFullName MeasurementSystem MeasurementType The first two tables above both contain fields which require an input for the "Unit". In fact, tbl:WellCasingDetails has two fields which require an input for the "Unit" (InnerDiameterUnit and DepthUnit). The table tbl:MeasurementUnits is meant to serve as a look up table for the two tables above and several others. I can create a relationship between tbl:MeasurementUnits and tbl:HorizontalSurveyData and enforce referential integrity. However, I run into a problem when I try to create a relationship from InnerDiameterUnit and from DepthUnit in the tbl:WellCasingDetails to the tbl:MeasurementUnits. I can create the relationships, but cannot enforce referential integrity. Any suggestions? Jessi "Evi" wrote: .. Putting the units in another table is an excellent idea. It is indeed good design to create a relationship. However, if you add a field to an existing table, you may need to run a series of Update Queries to get you started before you join up the tables and 'Enforce Referential Integrity' You may even need to update some of the records by hand (using your combo box in a form of course) before you can join your tables in the Relationships Window. (I've even had to resort to calling one of the |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
MVPs - The Evils of the Lookup Field | legere864 | Using Forms | 8 | January 25th, 2006 02:41 PM |
Lookup tables | Wind54Surfer | General Discussion | 15 | August 11th, 2005 12:54 AM |
lookup vs combo box | Scubaman | General Discussion | 4 | June 7th, 2005 02:02 AM |
"Evils of Lookup FIelds"? | el zorro | Database Design | 3 | November 16th, 2004 01:38 PM |
Lookup fields - what's wrong with them? | NATHAN SANDERS | New Users | 2 | May 4th, 2004 10:51 AM |