Details of Running Sum Totals

[manybeats]

I have a report with 4 subreports. Each grouping of subreports gives me a total and I have multiple groupings in the report. I want a detailed summary of all these group totals. E.g.
Group A Total = $1000
Group B Total = $1500

Each subreport is based on a query and each query has a total. For two of the queries, the total is based on a multiplication of two fields (e.g. hours x rate) and the other two queries are simply based on one field. Because of the differences, I can't put the raw data fields in a union query (which is where I believe my problem arises).

I made these 4 queries into a Union All query, which gave me a list of all the totals. I then used this union query as the recordsource for another subreport in my main report. When I go into the recordsource query and sum my total field, the query works perfectly. It sums all my totals by the proper groupings. Now, when I go back to view my report, it asks for a parameter value for the total field.

I can't seem to find any way around this and am confused as to why the query runs fine when I'm in the recordsource but not when I'm running the report.

The frustrating part is when I preview my report, all the totals I want are there under each group, and I just want to have them also summarized in a list at the front of the report. Seems like it should be so simple, but I'm baffled. Any suggestions???

 

[vbaInet]

I'm a bit confused there. You said that you can't use UNION in your second paragraph but then you went on to say (in paragraph three) that you got the UNION ALL working in a query but not in the report.

Can you show us some sample records and what you expect to get from them. You can copy and paste about 5 to 10 records into Excel and show us. Also paste the SQL statement of your queries (plus the Union All statement) here.

 

[manybeats]

I was worried my post wasn't entirely clear, so let's see if I can clarify somewhat:

The queries that the union query are based on have total fields (two of the queries have a calculated total and the other two simply have a total manually entered by the user). Because the queries forming the union query have to have the same number of output fields, I am unable to use the fields that feed data into the calculated total of the first two queries, I simply have the total field there. So the union query does work, but once it becomes the recordsource of my subreport, I think the subreport needs to see the fields feeding the data to the calculated/total field, as opposed to just the total field.

Clear as very muddy mud????

I've attached some records in Excell and here is the SQL of the union query. I copied and pasted the SQL from the queries directly into the union query.

SELECT tblProjects.ProjectPK, tblTimesheetHours.CELFK, tblCEL.CELNumber, tblCEL.CELDescription, tblCEL.ProjectActivityFK, tblProjectActivity.ProjectActivityDescription, [InvoicedHours]*[EmployeeProjectRate]+[InvoicedHours]*[EmployeeProjectRate]*[AdminFeePercentage] AS TotalWithAdminFee, tblTimesheetHours.InvoiceFK
FROM tblWorkActivity INNER JOIN ((tblProjectActivity INNER JOIN ((tblProjects INNER JOIN tblCEL ON tblProjects.[ProjectPK] = tblCEL.[ProjectFK]) INNER JOIN tblTimesheetHours ON tblCEL.CELPK = tblTimesheetHours.CELFK) ON tblProjectActivity.ProjectActivityPK = tblCEL.ProjectActivityFK) INNER JOIN (tblPersonnelDescription INNER JOIN ((tblEmployees INNER JOIN tblProjectPersonnel ON tblEmployees.EmployeePK = tblProjectPersonnel.EmployeeFK) INNER JOIN tblTimesheet ON tblEmployees.EmployeePK = tblTimesheet.EmployeeFK) ON tblPersonnelDescription.PersonnelDescriptionPK = tblProjectPersonnel.PersonnelDescriptionFK) ON (tblTimesheet.TimesheetPK = tblTimesheetHours.TimesheetFK) AND (tblProjects.ProjectPK = tblProjectPersonnel.ProjectFK)) ON tblWorkActivity.WorkActivityPK = tblTimesheetHours.WorkActivityFK
WHERE (((tblProjects.ProjectPK)=[Forms]![frmProjectParameter]![cboProjectParameter]) AND ((tblTimesheetHours.InvoiceFK) Is Null))
UNION ALL 
SELECT tblProjects.ProjectPK, tblExpenseReport.CELFK, tblCEL.CELNumber, tblCEL.CELDescription, tblCEL.ProjectActivityFK, tblProjectActivity.ProjectActivityDescription, tblExpenseReport.ExpenseAmount, tblExpenseReport.InvoiceFK
FROM tblProjects INNER JOIN (tblProjectActivity INNER JOIN (tblExpenseCode INNER JOIN (tblCEL INNER JOIN tblExpenseReport ON tblCEL.CELPK = tblExpenseReport.CELFK) ON tblExpenseCode.ExpenseCodePK = tblExpenseReport.ExpenseCodeFK) ON tblProjectActivity.ProjectActivityPK = tblCEL.ProjectActivityFK) ON tblProjects.ProjectPK = tblCEL.ProjectFK
WHERE (((tblProjects.ProjectPK)=[Forms]![frmProjectParameter]![cboProjectParameter]) AND ((tblExpenseReport.InvoiceFK) Is Null))
ORDER BY tblProjects.ProjectPK
UNION ALL
SELECT tblProjects.ProjectPK, tblEquipConsReport.CELFK, tblCEL.CELNumber, tblCEL.CELDescription, tblCEL.ProjectActivityFK, tblProjectActivity.ProjectActivityDescription, [EquipConsQuantity]*[EquipConsRate] AS TotalEC, tblEquipConsReport.InvoiceFK
FROM tblProjectActivity INNER JOIN (tblEquipConsCode INNER JOIN ((tblProjects INNER JOIN tblCEL ON tblProjects.[ProjectPK] = tblCEL.[ProjectFK]) INNER JOIN tblEquipConsReport ON tblCEL.CELPK = tblEquipConsReport.CELFK) ON tblEquipConsCode.EquipConsCodePK = tblEquipConsReport.EquipConsCodeFK) ON tblProjectActivity.ProjectActivityPK = tblCEL.ProjectActivityFK
WHERE (((tblProjects.ProjectPK)=[Forms]![frmProjectParameter]![cboProjectParameter]) AND ((tblEquipConsReport.InvoiceFK) Is Null))
ORDER BY tblProjects.ProjectPK
UNION ALL 
SELECT tblProjects.ProjectPK, tblContractorInvoices.CELFK, tblCEL.CELNumber, tblCEL.CELDescription, tblCEL.ProjectActivityFK, tblProjectActivity.ProjectActivityDescription, tblContractorInvoices.ConInvAmountLessGST, tblContractorInvoices.InvoiceFK
FROM tblProjects INNER JOIN (tblProjectActivity INNER JOIN (tblContractors INNER JOIN (tblCEL INNER JOIN tblContractorInvoices ON tblCEL.CELPK = tblContractorInvoices.CELFK) ON tblContractors.ContractorPK = tblContractorInvoices.ContractorFK) ON tblProjectActivity.ProjectActivityPK = tblCEL.ProjectActivityFK) ON tblProjects.ProjectPK = tblCEL.ProjectFK
WHERE (((tblProjects.ProjectPK)=[Forms]![frmProjectParameter]![cboProjectParameter]) AND ((tblContractorInvoices.InvoiceFK) Is Null));

 

[vbaInet]

I don't see what the problem is. Set the Record Source of your report to the query, i.e. not the entire SELECT statement from above but the actual query name that contains the SQL statement. Then perform your Sum on the field. A Union query will take the field names of the first SQL statement in the union so ensure you use that field name in the Sum() function.

 

[manybeats]

Thanks again for your help vbaInet. I think the problem was a cloudy brain and perhaps too little caffeine. In the control source of my sum field, I had the wrong name. Somehow I put "TotalWithAdminFee" instead of "SumOfTotalWithAdminFee". Access even notified me that something was up with a little error message on my report in design view, but I didn't understand what it meant. In hindsight it makes perfect sense!

 

[vbaInet]

Yes, when the error comes up it means the field name doesn't exist. It's a good habit to always pick the field from the drop down then add any extra calculations you want to it.

You're welcome.