Average calculations

[Guest]

I am having a hard time with calculating an average. Here is what I am trying
to do. This is for a lab report. I have 5 tables, each named "sample
1"...."sample 5". In each table, I have many fields. Each field is used to
enter a result from a certain test. For example, we have a granulation test
to determine the size of the product. After the test we enter our findings in
the granulation field for each sample. In my report, I have an average
column. Each of the fields in the sample tables are listed on my report. I
need to create an calculation that totals the values for the 'granulation'
results and divides them by 5 to give the the average.
How & where can I do this?
Thanks,
Youssif

 

[John Vinson]

I am having a hard time with calculating an average. Here is what I am trying
to do. This is for a lab report. I have 5 tables, each named "sample
1"...."sample 5".

This is almost certainly part of the problem. Storing data in
tablenames is essentially NEVER a good idea!

Much better would be to have ONE table, with a SampleNumber field;
you'ld have five times as many records in the table, but you can use a
query to select the results for any single sample, or you can use a
very straightforward Totals query to average the results across the
five (or three, or seventeen...) samples.

In each table, I have many fields. Each field is used to
enter a result from a certain test. For example, we have a granulation test
to determine the size of the product. After the test we enter our findings in
the granulation field for each sample. In my report, I have an average
column. Each of the fields in the sample tables are listed on my report. I
need to create an calculation that totals the values for the 'granulation'
results and divides them by 5 to give the the average.
How & where can I do this?

If you can't change the table structure, try creating a UNION query
stringing the five tables together. See UNION in the online help or
post back if this isn't clear.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,

Thanks for your message. I think that the database is a bit more complex
then what I orginally mentioned. We have a lot of info that needs to be
included. Here is what we have. We work in spices and herbs. When we prepare
an order, for example, of basil, we have a lot info. When we prepare a lot,
we have not only our ordering info (po #, batch #, contract #, dates,
quantities, etc), but we have production info as well as our quality lab test
info. For each order we take 5 samples. In each sample we have 32 fields
(test) that we need to enter data in. If I were to create 1 table for all 5
samples, this would give me 160 fields. This is why we have 5 separate
tables. Besides the Quality lab info, Production has 15 fields to enter data
in, as well as the exporting/sales department has about 15 fields to enter.

Each time we export 1 container, we must enter in all the info for each lot
shipped. I will try to group the samples into one table and see how that
works, but there has to be a simpler way to take the info from each sample
table and calculate an average in the report. I will also try to follow the
union query suggestion that you mentioned. If there are any more ideas, I
would also appreciate it.

Youssif

 

[John Vinson]

John,

Thanks for your message. I think that the database is a bit more complex
then what I orginally mentioned. We have a lot of info that needs to be
included. Here is what we have. We work in spices and herbs. When we prepare
an order, for example, of basil, we have a lot info. When we prepare a lot,
we have not only our ordering info (po #, batch #, contract #, dates,
quantities, etc), but we have production info as well as our quality lab test
info. For each order we take 5 samples. In each sample we have 32 fields
(test) that we need to enter data in. If I were to create 1 table for all 5
samples, this would give me 160 fields. This is why we have 5 separate
tables. Besides the Quality lab info, Production has 15 fields to enter data
in, as well as the exporting/sales department has about 15 fields to enter.

You're missing my point.

If you have five samples, with 32 fields per sample, then you need
five RECORDS in a test table, with 32 fields each. If you're assuming
that each sample needs a single record in the Test table you're on the
wrong track; you have a One (sample) to Many (test) relationship! Use
a table with one record per test.

Each time we export 1 container, we must enter in all the info for each lot
shipped. I will try to group the samples into one table and see how that
works, but there has to be a simpler way to take the info from each sample
table and calculate an average in the report. I will also try to follow the
union query suggestion that you mentioned. If there are any more ideas, I
would also appreciate it.

Study normalization.

You need a table of Products (i.e., a table in which there would be a
single record for Basil, or for each variety of Basil that you
market). This table would be related one-to-many to a second table of
Batches, in which each Batch record has a link to the Product and a
primary key identifying which batch it is. There's be another table of
QAResults, again related one-to-many to Batch - one batch might have
five (or fifteen!) results. There'd be another table of Contracts,
related many to many to Batches.

I think you've fallen into the very common misconception that you must
have all of the needed information in a single record in order to do
anything with it. NOTHING could be further from the truth!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps