Can I code a calc on a report

[dab1477]

I am trying to do the flowing when I run a report that displays a Group format:

I've included a sample page of my report - attached

I want to alter the Grand Total Sum calculation to show
Sum of SHIPPING (both shifts) / Grand Total of manhours

I.E. I ONLY want the Sum of Shipping - NOT all Dept sums as Calculated in Summary data - divided by the Grand Total of ALL dept manhours.

I have the following code:

Option Compare Database

Dim HPOCAlc As Integer

If Group_of_Dept_or_loc = "Shipping" Then
HPOCAlc = Sum((Total_Good_Order) / Total_Manhours_Grand_Total_Sum)
End If
End Sub

As a newbie, I'm missing something. Unsure of what. I also know this could be done via query, but was unable to get that to work, and then would not necessarily understand how to get to a report, which is why I chose to do the calc directly on the report.

Open to suggestions. Thanks,
dab1477

 

[PWise]

how about

=Sum (iif(Group_of_Dept_or_loc = "Shipping",(Total_Good_Order) / Total_Manhours_Grand_Total_Sum),0))

 

[dab1477]

I placed the formula in the Control source of the calculated box and it returns the error: "The expression you entered has the wrong number of arguments".

If this was to be VBA code, I assume it replaces my HPOCALC as noted above, I get #name error. My code looks like this:

Option Compare Database

Dim HPOCAlc As Integer

hpocalc=Sum (iif(Group_of_Dept_or_loc = "Shipping",(Total_Good_Order) / Total_Manhours_Grand_Total_Sum),0))end
End If
End Sub

Thanks for you patience.

 

[dhookom]

I doubt you need any code. If you provide some sample records with field names and desired output, I expect someone could provide the answer in one reply.

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

there is an error here

=Sum (iif(Group_of_Dept_or_loc = "Shipping",(Total_Good_Order) / Total_Manhours_Grand_Total_Sum[COLOR=red])[/color],0))

try

=Sum (iif(Group_of_Dept_or_loc = "Shipping",(Total_Good_Order) / Total_Manhours_Grand_Total_Sum,0))

 

[dab1477]

Almost. The expression now wants an input for Dept or Loc, for Total Shipped Sum, and for Grand Total Manhours. I wanted the calc to occur using values on the form, without me inputting those same values.

Thanks for your patience.

 

[dhookom]

Try:

= Sum(Abs([Group_of_Dept_or_loc] = "Shipping") * [Total_Good_Order])/Sum([Total Man Hours Field Name])

This assumes you have these fields in your report's record source:
- Group_of_Dept_or_loc
- Total_Good_Order
- Total Man Hours Field Name
If these aren't your field names, then substitute the correct names.

Make sure this control is not in a Page Footer or Header section.

Duane
Hook'D on Access
MS Access MVP

 

[dab1477]

OK... I was calculating in a footer! I should know better. Thanks.

But the issues still exists. I moved the text box with the formula into DETAIL section of the report. When I run the report, it wants inputs for Shipping, Total pcs, etc - basically an input for each of the output heading noted in the report. I want a simple calculation for ONLY ONE Dept or Group (Shipping) and I want it to calculate as (Shipping 1st shift + Shipping 2nd Shift)/Grand total of man-hours for all dept. In my DATE Footer, the report is formatted to provide the SUM of Total pcs, Total Rjts, & Total Man-hours - but does so for all of 4 depts, and not each singularly. I believe I need CODE to say:

Dim Total Good Orders as Integer1
Dim Grand Total Man-hours as Integer2

IF Dept = Shipping, Sum=(Integer2/Integer1)

Two things: The above code is not correct and I don't know what declaration to use to have correct code run during report generation.

I'm sure this can be done, I just don't know how to start. The given expression "= Sum(Abs([Group_of_Dept_or_loc] = "Shipping") * [Total_Good_Order])/Sum([Total Man Hours Field Name])" does not work in the Properites/Control source field of my designated unbound text box. When I run this expression, it asks for input of Dept, Total Good Pcs, and Total Man-hours. I don't know these factors until AFTER the report is generated, therefore I can't provide inputs.

Help! Thanks. I did provide a download as noted above in my initial post. I hope this helps.

 

[dab1477]

I've tried this code! No luck, I have an unbound textbox with this code within the DETAIL section of the report. It returns nothing, a blank.

I think the AfterUpdate() declaration is incorrect. What declaration is required when I initially run the report?

Private Sub HPOCalc_AfterUpdate()
Dim Total_Manhours As Integer
Dim Total_Good_Orders As Integer
If Group_Of_Dept_or_Location = "Shipping" Then
HPOCalc = ([Sum_Of_Total_Manhours] / [Total_Good_Orders])
End If

End Sub

Straying out of my element. Thanks for direction given.

dab1477

 

[dhookom]

I would totally stay away from any code in your report that attempts to perform any type of aggregation.

Any aggregation calculation should be in a GROUP or REPORT header or footer section.

When I am not on a work PC I can attempt to download your file to review.

Duane
Hook'D on Access
MS Access MVP

 

[dab1477]

TY dhookum

 

[dhookom]

The download doesn't provide much useful information. I would rather have an Access file with the required tables, reports, queries, etc that has been compacted and zipped.

Duane
Hook'D on Access
MS Access MVP

 

[dab1477]

Dhookum,
I've zipped and uploaded the database. I am trying to alter the "DailyProductionSummary" report. I've removed all my tests in order to get back to square one. I envision the report have a box that calculates Hours Per Order as HPO = Grand Total Manhours/Shipping Total Good Orders when I run the report. The issue seems to be that I want to use a Detail Total Good Orders and the Grand Total Total Manhours. They don't want to play together!
I look forward to a possible solution. Thanks.

dab1477

 

[dhookom]

You don't have the value "Shipping" in the record source of the report. You have the numeric value of 3. You should add the DeptLocationList table to your reports record source so you have the actual values like Shipping. Ideally, you should do the same with the shift table.

If you choose to use lookup fields in tables you need to understand them. My recommendation is to never use them since you end up creating a mess.

SELECT [Data table].Date, [Data table].Shift, [Data table].[Dept or Location], [Data table].[Total Good Orders], [Data table].[Total Reject or Scrap], [Data table].[Total Manhours], DeptLocationList.[Inspection Location]
FROM DeptLocationList RIGHT JOIN [Data table] ON DeptLocationList.ID = [Data table].[Dept or Location]

You can then use an expression like:

=Sum(Abs([Inspection Location]="shipping")*[Total Good Orders])

You may need to adjust the sorting and grouping.

Also, don't use Date as a field name since it is the name of a function.

Duane
Hook'D on Access
MS Access MVP

 

[dab1477]

Thanks Dhookum. I now see that I need to create a second report for just HPO calc. I appreciate your time and effort.