AVG calculation in reports

[Doug]

I give up! As the only one dumb enough at our small company to delve into
Access, I opened my mouth too wide this time. I have reduced my problem
database to the minimum below.

I have 2 tables related by job#. Table one has "JOB#" and "REVENUE" fields.
Table two has "START TIME", "END TIME" and "ACTION".

All job#'s have one "REVENUE" record but job#'s will have multiple records
to record time for each Job#. I compute the total time in a query.

Problem is when I run a report, the average revenue per job number is
correct but when I do an average OVERALL, the formula looks into each job
number and divides the total by that higher figure instead of the number of
job#'s.


Revenue Time Used Action
JOB# 1234 $100.00 15 Blast
1234 $100.00 12 Del.
1234 $100.00 3 Blast
1234 $100.00 42 Snooze


SUM 400.00 72 (4 detail records)
AVG 100.00 18



JOB# 4321 50.00 12 Tape
4321 50.00 12 Blast

SUM 100.00 24 (2 detail
records) This is in the job# Footer
AVG 50.00 12

TOTAL SUM 500.00 96 (6 detail records)
AVG 99.33 16


What I need is the average to be on 2 detail records (each job#) Like below.

TOTAL SUM 150.00 96 (2 detail records)
AVG 75.00 48

Am I placing the SUM and AVG in the wrong place in the report? Should I be
doing these calculations in a query? There's a beer in it if you can help
with a solution i can either find in a specific book or if you are kind
enough to describe the solution in layman's terms. Beer's limited to the
first 12 correct answers.

Thank You,

Doug
(e-mail address removed)

 

[Les]

Doug,
The sum and avg are working correctly. Unfortunately,
it's not how you want them to work. This might work for
you:
1) add a field on your job # footer, with a value of 1.
(fld1)
2) on your report footer (sum and avg of all job #'s),
create a field to sum the sum field from your job # footer
(fld2), another field to sum the field with value of 1
(fld3). This should give you the money total, and number
of job #'s. Divide fld2 by fld3.

-----Original Message-----
I give up! As the only one dumb enough at our small company to delve into
Access, I opened my mouth too wide this time. I have reduced my problem
database to the minimum below.

I have 2 tables related by job#. Table one has "JOB#" and "REVENUE" fields.
Table two has "START TIME", "END TIME" and "ACTION".

All job#'s have one "REVENUE" record but job#'s will have multiple records
to record time for each Job#. I compute the total time in a query.

Problem is when I run a report, the average revenue per job number is
correct but when I do an average OVERALL, the formula looks into each job
number and divides the total by that higher figure instead of the number of
job#'s.


Revenue Time Used Action
JOB# 1234 $100.00 15 Blast
1234 $100.00 12 Del.
1234 $100.00 3 Blast
1234 $100.00 42 Snooze


SUM 400.00 72 (4 detail records)
AVG 100.00 18



JOB# 4321 50.00 12 Tape
4321 50.00 12 Blast

SUM 100.00

24 (2 detail