Calculations

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I'm trying to calculate a simple average using 5 numbers.
The only problem is that I can't store the data into the
table, which I def. need. This will eventually become a
bigger and bigger table. Thanks.
 
Mike,

One of the general principles with databases is... If you can
calculate it, then calculate it whenever you need it, and storing it
is a bad idea.

What do you mean by the table becoming bigger and bigger? You mean
the number of records will increase? And, are the 5 numbers you are
working with in different fields or in different records? Can you
post back with some more details and examples of your data and table
structure, and someone will be able to offer some good, more specific
advice.

- Steve Schapel, Microsoft Access MVP
 
Well, the table with the five numbers is just a testing
ground for my bigger database. I use it just to test
different ideas and codes to see what happens. Well the
real table is actually a 13 X 5 field table. Each number
is call essentially sXtY. X meaning the sample number, and
Y the test number. In all, there are 13 samples with 5
tests. Well I created a macro to run the averages of each
reading under 1 sample, hence only 5 numbers. Well... The
macro I'm trying to create for the final average, which is
the averages of the averages, is too long for the
expression builder to be created. So thats why I want to
add the values into the table and grab them from the table
and throw them into a report for whatever.
The report is based off of a query. When the report is
opened, a pop comes up and certain critera can be entered
to search the query based on what was entered, like a
product code or a date range. Well... the query that this
is based off of, will not contain the sXtY ranges and
averages, because the report will prompt for them, unless
there is an easier way of doing it.
 
Mike,

Yes, there is a much easier way of doing it. From your description, I
only have an incomplete picture of your requirements. But really,
what you have done is more like a spreadsheet or something like that.
This sort of design is not really suitable for a database. I strongly
recommend that you create tables that correctly reflect the nature of
the data and the relationships between the different data entities.
What that means is something like this (just a couple of ideas to give
you the general concept... I would need more details before I could
give more specific help):

Table: Samples
SampleNumber
SampleName

Table: Tests
TestNumber
NameOfTest

Table: SampleTests
SampleTestID
SampleNumber
TestNumber
TestResult

etc.

In other words, you end up with your main data table having a *record*
for each Test for each Sample, not a *field*. This is the database
approach. If you do it like this, your data entry, averages, other
statistical analyses, reports, and all other aspects of data
management, will be much simpler.

- Steve Schapel, Microsoft Access MVP
 
HI:

Off topic here, but did you know that averaging averages is a no no? For
instance lets look at sampling tree heights by species group and we have 10
plots. Some plots don' have all the species we want to sample within them.
For example only 6 out of 10 plots have softwood. If you Average the
heights of the softwood over all 10 plots you'll end up with the average
height being shorter than the true average You have to base the average
height of softwood on just the 6 plots that contain softwood.

Food for thought :)
 
Back
Top