Jane:
Firstly do not use a multi-select list box for entering data unless you are
prepared to write a lot of code behind your form to insert data into a table/
read data from a table. Multi-select list boxes are really designed for
querying the database rather then entering data, e.g. you might want a report
of which days have experience all of a set of selected weather conditions,
for which a multi-select list box would be the right tool, but there are far
better and easier ways of entering the data. Examining two scenarios:
1. If you have a fixed set of weather conditions e.g. temperature,
precipitation, wind strength etc the Wayne's solution is fine, having one
column (field) for each in the WeatherLog (or whatever) table, along with
columns for the date, location etc. Each combo box should get its list from
another table:
WeatherConditions
….Condition (primary key)
….Category (foreign key referencing Categories table)
so the RowSource type for the Precipitation combo box would be:
SELECT Condition FROM WeatherConditions WHERE Category = "Precipitation"
ORDER BY Condition;
and that for temperature would be:
SELECT Condition FROM WeatherConditions WHERE Category = "Temerature" ORDER
BY Condition;
2. However, if you want to record a variable set of weather conditions per
day then a better solution would be to have two related tables which in turn
would reference other tables, e.g.
WeatherLog
….WeatherLogID (autonumber primary key)
….WetaherLogDate
….LocationID (foreign key referencing a Locations table)
….RecorderID (foreign key referencing a Recorders table)
WeatherLogDetails
….WeatherLogID (foreign key referencing the WeatherLogtable)
….Condition (foreign key referencing WeatherConditions table)
The primary key of the above table would be composite one of WeatherLogID
and Condition. What this table is really doing is modelling a many-to-many
relationship between WeatherLog and Conditions by resolving it into two
one-to-many relationships.
WeatherConditions
….Condition (primary key)
….Category (foreign key referencing Categories table)
Categories
….Category (primary key)
This last table would have values in the Category column for Temperature,
Precipitation, Wind Strength etc.
For data entry you'd use a form, in single form view, based on the
WeatherLog table, and within it a subform, in continuous form view, based on
the WeatherLogDetails table. To enter each condition to be recorded for the
day a new row would be inserted in the subform, selecting from a combo box
bound to the Condition column. The combo box's RowSource would be:
SELECT Category, Condition FROM WeatherConditions ORDER BY Category,
Condition;
its other properties would be:
BoundColumn: 2
ColumnCount: 2
Column Widths: 2.5cm;2.5cm
You'll need to experiment with the last to get the best fit. It you are
using inches rather than metric units Access will automatically convert the
above (2.5cm = 1 inch approx)
To show the category for the selected condition include an unbound text box
in the subform, with a ControlSource of:
=cboCondition.Column(0)
where cboCondition is the name of the combo box bound to the Condition column.
Finally one or two explanatory points:
1. You might be wondering why you need the WeatherConditions and categories
tables at all as you could simply use value lists as the RowSources of the
cmbo boxes. The reason is that the conditions and categories are data, and
it’s a fundamental rule of the databases relational model that data is stored
as values at column positions in rows in tables and in no other way. Value
lists should only be used for sets of values immutably fixed in the external
world, such as days of the week or months of the year.
2. You might also be thinking that the Categories table is surplus to
requirements as the category for each condition is in the Conditions table.
The reason is that it allows you to create a relationship between Categories
and Conditions and enforce referential integrity and cascade updates. The
former ensures that only valid categories can be entered in Conditions, the
latter means that if you change the name of a category, e.g. you might decide
to change Precipitation to Rainfall in the Categories table (rather
inaccurately of course). This would automatically update all the
Precipitation rows in Conditions to Rainfall.
Ken Sheridan
Stafford, England
