averages in a report?

[mejia.j88]

hi guys,

i have a table which includes part number, and depending on the part, up to three measurements. my table looks something like this:
Part_Number Measurement1 Measurement2 Measurement3
ZR50SG 0.0905 0.091 0.095
.
.
.
1N6500 0.03 0.032

etc.

i want to create a report that will show the part number and it's average measurements.



the problem: i'm not sure how to write a program or SQL or what that will average the measurements for me. some parts will have up to 100 entries, where others only about 20.

another thing i'd like to do is be able to select a date range and see the averages there.

any help will be greatly appreciated. thank you.

[hertfordkc]

for readings, where each reading references a part number.
Once you have that organization, you can easily create a query which calculates averages by part number regardless of the number of readings.

[mejia.j88]

hi hertfordkc,

i have both of those tables set, but how would such a query work?
do i have to make a relationship between the two with part number as their commonality?

[hertfordkc]

with a primary key, which would be used in the measurements table to tie back to the part number Then you can define the relationship between the two tables.

You say that you have the two tables set. If you created a table that has rows with each row containing the part number and a single measurement, then you don't really need the part number in a separate table. Using two tables and a primary key as the link is normally a much better design. However, unless you are working on something that will turn into a really big database (maybe thousands of parts and hundreds of thousands of measurements) it probably isn't worth your time to redo the tables.

Either way, you can use the query wizard to satisfy your requirements.
The following links show how to set up the type of query you'll need.
allenbrowne.com/ser-67.html

office.microsoft.com/.../access.../make-summary-data-easier-to-read-...