how do i Calculate totals in a report?

[Eric6]

hi,

I'm having problems calculating my totals in a report i'm building....

i'm using the Sum() method but i doesn't give me the right results

I have a large query contaning the information for the form
(i use it as my control source)
I then group the data from inside the report.
(ps: the query's info cannot be grouped)

my problem is, when i try to calculated the sum for my groups, i get the sum of each and every record instead of the sum per group


for example:
this is the info returned from my query

employeeName   month    pay
John           April    500
John           May      500 
John           June     500
Bob            May      250
Bob            June     250
Jill           April    400
Jill           May      400
Jill           June     400

in my report, i group by employee so i have something like this

John           April    500
               May      500 
               June     500

the problem is,
my Sum([pay]) in the footer of my employee group
would return 3200 instead of 1500

every sum does that (adds every record in my query)
i also tried DSum() but it doesn't work
it still adds every record in the query instead of the information under its group in the report...

i have another wierd problem
if i change the name of a control and refer to it using a method (like sum())
i get a pop up inputbox asking for the value of the control
as soon as the control name and control source are different, i get the pop up
i dont understand why...

 

[Eric6]

The Sum() method calculates the sum for each record in my query, even when the field that uses sum is in the "footer" of a group...

i dont know how to fix it so that it only adds the values in its group

any help would be extremely appreciated!
i have to get this to work

thanks in advance

Eric

 

[ittech73]

A very simple solution to your ?, why don't you just sum them up in the query or is there a reason you want it done in the report only?

 

[Eric6]

that would still cause the same problem,
it would sum up every record
instead of a specific group (which is what i need)

Eric

 

[ittech73]

If you where only looking at the name and the amount. Using sum in the query would work. That would be grouping it out by the names, but your looking at another factor month. With month in there access can't group cause there is no common factor in the month only the employee's names. This make the problem a little more tricky. Does the month need to be seen at the record level or could the report be looked at by the month? This could be a way of looking at the report for a solution. Let me know and let me think about the problem some more. If you need to have the month at the record level.

 

[Guest_imported]

Hi
In the header of report, just have a query which selects the employee name.

Now in your sum() query , you can add the line
where employee.name = ReportName!name

This way it will always calculate report for each employee.

I hope this will work !!