Group Totals and Report Totals

[Oscar]

I have an occupancy field that is calculated on a monthly basis from
figures that are given on a weekly basis. Our directors are awarded
bonuses based on their center's occupancy on a monthly basis but are
only paid on a quarterly basis.
So I have a report that is sorted by week ending date and grouped by
month. So in the group footer for each month it calculates the
occupancy for that month and i created an IIF statement to use the
occupancy calculation and tell me how much of a bonus they get for that
month. This field is [MONTHOCC] and appears below every month's data
as it should.
However, the report spans 3 months (for a quarter) and I need it to add
all three [MONTHOCC] for me in the report footer to tell me the total
amount each director should get paid in bonuses for the quarter.
In the report footer I've tried creating a text box with the data
=SUM([MONTHLYOCC]) and it does not work.
Any help is greatly appreciated!
Thanks,
Oscar Brown

 

[lewie]

I have an occupancy field that is calculated on a monthly basis from
figures that are given on a weekly basis. Our directors are awarded
bonuses based on their center's occupancy on a monthly basis but are
only paid on a quarterly basis.
So I have a report that is sorted by week ending date and grouped by
month. So in the group footer for each month it calculates the
occupancy for that month and i created an IIF statement to use the
occupancy calculation and tell me how much of a bonus they get for that
month. This field is [MONTHOCC] and appears below every month's data
as it should.
However, the report spans 3 months (for a quarter) and I need it to add
all three [MONTHOCC] for me in the report footer to tell me the total
amount each director should get paid in bonuses for the quarter.
In the report footer I've tried creating a text box with the data
=SUM([MONTHLYOCC]) and it does not work.
Any help is greatly appreciated!
Thanks,
Oscar Brown

 

[lewie]

lewie wrote:
The way I did this was to make a summary query that would have the
months from jan to march in criteria of course you could make it a
parameter so you could enter the month span. then run the query and you
should get the 3 month total. then make a function that runs this query
docmd.runquery below is a sample that uses fields on a report as
parameters in the query
Function fill_controlYTDTotal()

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter

' Before running this procedure, open
' frmInfo and enter a value, like "Berlin",
' into the City text box, then tab off of the text box.
' You must move the focus out of the City text box in
' order for this to work.
On Error GoTo err:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
'cmd.CommandText = "qryCustCity" ' name of query
cmd.CommandText = "[ytd budget table sum]"
' If you use adCmdStoredProc,
' this won't work
cmd.CommandType = adCmdTable
' This next statement is actually optional
' If you leave it out, ADO does it anyway.
cmd.Parameters.Refresh
' Loop through the parameters
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm

' And populate the recordset
Set rst = cmd.Execute
'Do Until rst.EOF
' Debug.Print rst.Fields(0).Value
' Reports![final report1]!ytdmonthlytotal = rst.Fields(0).Value
' rst.MoveNext
' Loop
' [Reports]![final report1]![up_dept_code]
If IsNull(rst.Fields(0).Value) Then
fill_controlYTDTotal = 0
Else
fill_controlYTDTotal = rst.Fields(0).Value
End If
Exit Function
err:
MsgBox "error is" + err.Description
Exit Function
rst.Close
Set rst = Nothing
Set cmd = Nothing
End Function

or if it is a parameter query there is a little extra required to run
the query. then when you have the function running the query set the
return value to the result.
function.name = result.
then lastly set the recordsource of the control on the report to
=function.name.
anyway to summarize
1. you make a query to calculate the result
2. create a function to call that query
3. set the recordsoucre of the control to the function.
anyway once you do it once and get the bugs worked out it came in handy
to me to use for ytd values and such when 2 tables weren't linked and i
couldn't use the total field or for a sum of a calculated field.
Lewie

I have an occupancy field that is calculated on a monthly basis from
figures that are given on a weekly basis. Our directors are awarded
bonuses based on their center's occupancy on a monthly basis but are
only paid on a quarterly basis.
So I have a report that is sorted by week ending date and grouped by
month. So in the group footer for each month it calculates the
occupancy for that month and i created an IIF statement to use the
occupancy calculation and tell me how much of a bonus they get for that
month. This field is [MONTHOCC] and appears below every month's data
as it should.
However, the report spans 3 months (for a quarter) and I need it to add
all three [MONTHOCC] for me in the report footer to tell me the total
amount each director should get paid in bonuses for the quarter.
In the report footer I've tried creating a text box with the data
=SUM([MONTHLYOCC]) and it does not work.
Any help is greatly appreciated!
Thanks,
Oscar Brown