Hi John,
You actually do have redundant fields in your table - Reading1, Reading2,
Reading3 . . . Reading5. In the spreadsheet world there is nothing wrong
with this approach. If your procedure changes and you are required to add 5
more readings you simply add 5 more columns. In Excel, adding columns is
trivial and in most cases you can create your formulas such that the new
cells are included in the range for the formula.
In a database, adding new fields is a bit less trivial especially when you
also have to find every formula (which you would need to do using the iifs)
and adjust it yourself. Instead, by normalizing your data you build in the
flexibility. Here's how your normalized table would look -
ReadingType*
ReadingDate* (Date&Time)
ReadingValue
The * fields would be the primary key fields. Then as long as you make sure
that ReadingDate records date & time (using the Now() function), you can
have an unlimited number of readings per day. Your query then would then use
the aggregate functions on the ReadingValue column.
To be more restrictive you would have two tables
ReadingSets
----------------
ReadingSetId *
ReadingType **
ReadingDate **
ReadingValues
------------------
ReadingSetId *
ReadingNum *
ReadingValue
Then by setting the field validation property of ReadingNum to restrict it
to values between 1 and 5 you can limit how many readings are taken on a
given day. This is just one of several other ways of controling how many
records are allowed in one set. In fact, this is the one I least prefer
since it would require a table design change to change the limit.
* field is the primary key, ** fields are part of a unique index
You can use a crosstab query to report the normalized data in spreadsheet or
matrix form.
Now, if you are still with me
. . . if this is just a tool that you are
going to use and no one else is ever going to see or touch then you *might*
want to use the original method with the nested iifs. Then again you might
also want to leave your data in a spreadsheet and then simply link to the
table with your Access database and build your reports off the linked table
(I think it would work but I haven't tried it to be sure).
When all is said and done you have to decide how durable and robust your
solution needs to be. In the long run, the normalized method is going to be
the best.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
John said:
Thanks Sandra, the nested iifs will solve my problem.
But it would be interesting to know if there is a better way of
designing the tables.
The application is to construct a quality control chart where you
periodically take a set of readings, usually 5, of some process
variable and calculate the mean and range, or the mean and standard
deviation, of each set. I usually do it in Excel but I want to make
better looking reports and Access seemed to be better at that. So
basically you only have a unique identifier for each set and the five
readings. There is no redundant information in the table so I can't
see any opportunity to normalize further.
Regards, John Duffus
Sandra Daigle said:
Hi John,
Typically when you are trying to use aggregate functions across
columns instead of down rows it is because your data is not
normalized. I'm sure that someone clever has come up with a way to
find the Max across columns (at the very least you could do it with
nested iifs) but in the meantime you might want to think about
whether your table design is optimal. Feel free to post more details
about the table/fields and I will be glad to help you look at ways
of normalizing the data (which will in turn allow you to use the
aggregate functions as they are intended).
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
John said:
Hello all,
I have a table that has 5 numeric fields, among others.
I would like to have 2 calculated fields in a query, the maximum
and average of the 5 fields for each row. The average is easy
because only arithmetic operations are involved but I can't see a
way of calculating the max.
(I have worked around it by copying the control values of a form
into an array, calculating the max in VBA and assigning the result
to an unbound control, but I'm curious about the query.)
Am I missing something or are there really no equivalent functions
to Avg(), Max() etc that work across a row instead of vertically as
it were, over records?
Any help appreciated.
John Duffus
