Multi Record Form

[E.Q.]

I have a crew that creates a MS-Excel spreadsheet to report each day showing
the status alarms that appear on the process control computer trends for 57
sewage lift stations. I'm trying to show that perhaps a database would
provide a lot more flexibility. (Their report provides a per day status but
they would have to call up all reports if someone wanted a report for an
individual lift station. Also, their report is usually about 50 lines of
"OK" with only a few stations reporting any problems.)
I have extracted a month's worth of data and created some reports that I
think would be more meaningful. Now I'm wondering about how to make the data
entry form.
I normalized as best I could, meaning that each record contains the status
for one lift station.
The database consist of two tables. One table has the lift station data
(In-Plant ID No, trend number, lift station name, address, phone or radio
number, boolean if generator, boolean if QF reset.) The other table has the
status report (autoID,date, In-Plant ID No., status). The tables are linked
with the in-plant ID number.
The ideal form would have the common date stamp at the top with a "row" for
each lift station consisting of a control showing the name of the station and
another the status (defaulted to "OK").
How would I go about creating such a form?
Also, do I need to normalize further since I'm repeating the date of for
each status record?
Any info will be appreciated.
E.Q.

 

[Ken Sheridan]

I'd suggest using an unbound form with a list box listing the stations, a
text box to enter the date (or a combo box based on a table of dates – you
can easily produce the latter by serially filling down a column in Excel and
importing the worksheet into Access as a table) and a subform bound to the
status reports tables. In the subform set the DefaultValue property of the
control bound to the Status field to "OK".

The RowSource for the list box would be something like:

SELECT [In-Plant ID Number], [Station Name]
FROM [Stations]
ORDER BY [Station Name];

Set its BoundColumn property to 1, its ColumnCount property to 2 and its
ColumnWidths property to 0cm;8cm, or rough equivalent in inches, but the
first dimension must be zero to hide the ID number (assuming you don't need
to se it in the list).

The LinkMaster Fields property of the subform control (i.e. of the control
in the main form which houses the subform) would be the name of the list box
and of the date text box (or combo box if you use one), e.g.

[lstStations];[txtDate]

The LinkChildFields property would be the names of the corresponding fields
from the status table, e.g.

[In-Plant ID Number];[Date]

BTW Date is not a good name for a field as it can be confused with the built
in Date function. Something more specific like ReportDate would be better.

When you select a station from the list box and enter (or select if using a
combo box) a date in the main form's unbound controls the subform will, if no
status data for that station/date has yet been entered, show a new record
with the In-Plant ID Number and date values form the main form already in
place, and the default value of "OK" in the status field. If the status
record has previously been entered for the date it would show that record;
the status could be changed if necessary.

One problem arising from having a default value for status is that the user
won't have to enter any data in the subform unless they wish to select a
different status. This means that the new subform record won't be
automatically saved as no edit is initiated until some data is entered. To
cater for this I'd suggest putting a 'Confirm' button in the subform which
sets the value of the Status field to itself. What this does is 'Dirty' the
subform, i.e. initiate an edit, so after clicking this button the new record
will be automatically saved when the user moves off it in any way. The code
for the button's Click event procedure would simply be:

Me.[Status] = Me.[Status]

As regards any further normalization, this is not necessary as the Status
table is well normalized already. The repetition of the date is fine because
this is in fact part of a candidate key of the table, this being the
composite one of the In-Plant ID Number and Date fields in combination. You
have given the table a surrogate numeric primary key, AutoID, but you could
equally well omit that and define the In-Plant ID Number and Date fields as
the composite key. You don't need to do this, but if you don't it is
essential that you create a unique index on these two fields jointly, as
otherwise more than one record per station per date could be entered into the
table.

The only situation in which you would need to normalize further by
decomposing the table would be if there were a non-key field in the table
which was not functionally determined by the whole of the candidate key, i.e.
if there were a field functionally determined solely by the date. This is
not the case, so the table is in fine fettle.

I'm a little wary of the propriety of the Boolean [if generator] and [if QF
reset] fields in the Stations table, however, though not being familiar with
the technology probably wrongly so. If these represent two alternative
values of a single attribute type of the Stations entity type then having two
columns would be what's known in the jargon as 'encoding data as column
headings'. A fundamental principle of the database relational model is that
data is only stored as values at column positions in tables. So, if these
columns represent alternative values of one attribute type, rather than
distinct attribute types each with possible values TRUE or FALSE, then they
should be represented by rows in a related table. I raise the point more out
of illustration of the principle, however, as I suspect they are probably
fine.

Ken Sheridan
Stafford, England