Hello

My Report is grouped by Work Order #.

Each Work Order # has many different products.

In the Report's footer I want to sum up the total number of each product.
So if Work Order # 1 has two hats and Work Order # 2 has three hats, in the report's footer, Total Hats should say 5. (Note: There are many work orders and about 20 products)

I don't know how to accomplish this effeciently.

Currently, I made a generic function to make these calculations but it is too slow since this function is run 20 times (for 20 products). This method is very ineffecient since I keep reproducing almost identical queries 20 times (each time I run the function).

Below is my function: Note: we identify our products with a MOE code.

There must be a better way to do this no?

************************************************** **
Public Function MoeSum(MOENum As String) As Integer

Dim dbs As Database
Dim qd As DAO.QueryDef
Dim rcs As DAO.Recordset
Dim Total As Double

Set dbs = CurrentDb
Set qd = dbs.QueryDefs("DriversDaillyReceivingSheetMOESum")

qd![[Forms]![DailyDriversReceivingSheet]![Drivers]] = [Forms]! [DailyDriversReceivingSheet]![DRIVERS]

qd![[Forms]![DailyDriversReceivingSheet]![Date Scheduled]] = [Forms]![DailyDriversReceivingSheet]![DATE SCHEDULED]

qd![[Moe]] = MOENum ' THIS IS THE ONLY VARIABLE in the QUERY

Set rcs = qd.OpenRecordset

Total = 0

If rcs.EOF Then
MoeSum = 0
Exit Function
End If

rcs.MoveFirst

Do While Not rcs.EOF
Total = rcs![EXPECTED PACKAGES] + Total
rcs.MoveNext
Loop

MoeSum = Total
End Function

Use a Totals Query and display the result on your Report

If I use a totals query I still have to run the same query 20 times. I want to be able run the main query once. Maybe I can use a static variable that holds the main query and manipulate it?

Use a Total query to obtain the summation of all work orders and use it as the source for a sub report in the Main Report Footer
Len

Why don't you Group by Work Order Number and Product. Then Sum the quntity field?