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 said:
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.
