Sum of a Calculation in a report

[gscma1]

I have created a database whereby employee wages are generated, however, is it possible that on the form which produces the Gross and Net totals of the wages - these being calculations themselves - a total field which adds up the Gross wages for all employees and the Net wages for all employees can be produced?

I am struggling and have tried the =sum function however i think it may be different since i'm requiring to total calculations.

Please help!

 

[dhookom]

You can't sum a control. Sum the calculation expression.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

I thought i was summing the calculation expression. I know i might sound a little stupid but could you please tell me how to do this?

Many thanks

 

[dhookom]

You haven't told us anything about [blue]"these being calculations themselves"[/blue]. What are your calculation expressions?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

Sorry, my report is a Wage Sheet, which works out employees gross wage from their hours and their wage rate.

I have a Gross Total which is a calculation of Hours and Wage Rate divided by 8 - =[Hours]*[WageRate]/8

There are approximately 40 employees therefore this gross total is worked out for each. at the end of the report, I want to put a gross total in my report footer, to add up the total of every employees gross for that particular week.

This report also works out the net wage for each employee, the expression for this =[Gross]/100*82, i would also like a total for all Net Wages at the bottom of this report.

any help with this would be appreciated because i am struggling, and this is the last thing i need to do.

 

[dhookom]

Did you try
=Sum([Hours]*[WageRate]/8)
and assuming the above is the [Gross]
=Sum([Hours]*[WageRate]/8)/100*82

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

Thank you

 

[hunarch]

qscma1,
I found my answer to this question at

http://support.microsoft.com/?kbid=208850

I created a module that works fine.

 

[hunarch]

I have totals calculated from a module function as per

http://support.microsoft.com/?kbid=208850taed

It works fine - except for one thing: the results when printed are different. Moreover, the results vary from that correctly displayed in the report depending upon whether I print from the open report, the closed report, or when I print to Adobe! Has anyone else encountered this problem and derived a solution?

 

[dhookom]

You need to figure out how to calculate your sums without using any code. You may have extremely complex calculations but I have created 100s of complicated reports without ever using code in the reports to calculate aggregates.

You haven't provided any code or specifications so we can't be of much assistance.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hunarch]

Duane,
Thanks for your reply. I have provided what I hope is sufficient info. below. My apologies if it is still inadequate. I only used the module because, of the three options suggested in

http://support.microsoft.com/?kbid=208850taed

, it was the only one that I could get to work. However, I am not wedded to this method if there is a simpler way.

I looked at another thread on Tek-tips

http://www.tek-tips.com/viewthread.cfm?qid=1225285&page=1

that suggested that settings in Event Procedure in VBA (Print() or OnFormat()) were necessary. But I can't get my mind around those recommendations.

RodH

Module Code:
------------------------------------------------
Option Explicit
Dim Tot2 As Double ' Used for Group2 total.
Function CalcProduct1(R As Report)
Dim tmpAmount As Double
tmpAmount = R![CostA1] * R![CountOfAction]
' Remember the total for the group.
Tot2 = Tot2 + tmpAmount
' Remember the total for the report.
CalcProduct1 = tmpAmount
End Function
Function InitVars()
Tot2 = 0
End Function
Function GetGroup2Total2()
GetGroup2Total2 = Tot2
' Reset the variable to 0 for next group.
Tot2 = 0
End Function
--------------------------------------------------
Report "On Open" is set to InitVars()
"CountofAction" is the result of Count(*) in Group1Header, Running Sum = "No"

 

[hunarch]

Duane,
I should have added, the reason for choosing the module method was that my "CountofAction" function is a result of another module that adds a quantity to the Count(*) function. In other words, "CountofAction" is not a simple aggregate function.
Rod H.

 

[hunarch]

I think my question comes down to this:

Assuming that the use of a module is an appopriate way of achieving my footer total named

[Group2Total2]

with control source

 =GetGroup2Total2()

, what code do I need to insert into the report's [EventProcedure] on Format so that the correct result shown in Print Preview is also correctly shown when printed?

Incidentally, the "CountofAction" function is the result of an IIf function in the Data Control Source function, not from another module, but I do't believe that is very relevant to the key question above.

Rod H.

 

[dhookom]

You have told us how you have attempted to resolve a calculation but haven't provided any specifications stating what you have (record source) and what you need to calculate.

I would not spend any time writing code unless I knew it was required. Your need may require code but I can't support this without understanding your data and your requirements.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hunarch]

Problem now solved in either of two ways:
1. Non-programatically by saving reports to Snapshot files;
2. Programmatically by modifying the database design, motivated by Duane's comments, and eliminating one of my modules.

Thanks

Rod H.