Agregate detail fields

[Raul Sousa]

I have a database with the hours each teacher works.
So I have a query with these fields; name, class, monthly
hours and value.
Monthly hours is a calculate field, equals to weekly hours
times number of weeks on that month. Value is monthly
hours times a value.
I have a report to show this information with name header
and class, monthly hours and value on the details area.

My problem is that when a class has lessons more than once
a week, the report has as many lines as weekly days. For
example, it may appear like this:

Name: John

Class: Hours Value
A 5 10
A 4 8
B 9 18
C 10 20

I want it to joint the class, like:

Name: John

Class: Hours Value
A 9 18
B 9 18
C 10 20

I don’t know how to do that. Is it possible, what should I
do?

 

[Jeff Boyce]

Raul

You could use a Totals query, grouping by Name and by Class, summing for
hours and values. By the way, Access treats (at least) "Name" and "Value"
as reserved words -- change the name of these fields to something that won't
confuse both Access and you.

Another observation -- if "Value" = Hours * something, why are you storing
both Hours and Value? Do you always use the same "Something" as a factor?
If so, just store Hours, and do whatever calculation you need in the query,
form or report. That way, there's no issue with synchronization among the
three (hours, something, value), and you're not storing redundant data.

Good luck

Jeff Boyce
<Access MVP>