Summing Expressions That Contain Aggregate Functions

[Varda]

I am using MS Access 2010.

I have a report with two sections: OrgID and below it RoomID.

The RoomID header contains three fields:

1. SquareFoot
2. TotalOffices: =Count([Rooms])
3. TotalSF: =[SquareFoot]*[TotalOffices]

In the OrgID footer, I would like to have a field that sums up all the TotalSF.

I have tried everything suggested on the internet, and it still won't work. It either gives me the last value of the group, or it sums up something obscure that doesn't make sense.

I know that it's problematic to sum up a function of Count, but there has to be a way to do it, no?

I've tried the following variations without success:
1. =[SquareFoot]*[TotalOffices]
2. =Sum([SquareFoot]*[TotalOffices])
3. =Count([SquareFoot]*[TotalOffices])


4. =Sum([SquareFoot]*Count([Rooms])
5. =Sum([TotalSF])

I also played around with the running sum and I'm getting a running sum over each OrgID, but it's not desireable. I just want a sum of the Total SF per each OrgID.

What am I missing?

[ItsMe]

You can do it all in VBA or you can create a separate query object that it is a totals query. In the new query, include a field that allows a join and a field for the count you want. Join your new query object to the query that your report is based on.

Now bind a text box control to the new Count field. Your TotalSF calc will be an expression in an unbound control that incorporates the .Value property of the control bound to Count.

[Varda]

I was hoping to avoid the query solution. I thought there would be a way to do it directly in the report. I'll try the query method and see if I can figure it out. If anyone else has other suggestions, I'd love to hear it.

[Varda]

I managed to get the correct query, but I'm still having problems. When I go into the report's query and join my new query with it, I get a circular reference.

Is there a simple way to refer a bound field on a report to a field in a query without the query being the source of the report?

[ItsMe]

..Is there a simple way to refer a bound field on a report to a field in a query without the query being the source of the report?...

That is not stated quite right but, no, you can not.

Controls are used in Objects like forms and reports. You can bind a control to a field that is defined in a recordset. It is best to think of fields as something in a table (within a query too) and controls as something in a form or report.

If a control is not bound to a field, then it is an unbound control and you can define its control source or other properties via VBA or an expression using the properties sheet.

Long story short, you can create VBA that will look at a recordset that is not associated to the Report's recordset and display information (from the unrelated recordset) in an unbound control.

Another solution is a Subreport. If, in fact, a subquery creates a circular reference, a subreport should break the cycle in a similar way writing VBA to display info in unbound controls would. The difference being Subreports will need to be placed in a specific area within your report. I do not have a lot of experience with subreports.

[Varda]

Oh, the subreports I'm VERY familiar with. The only concern I have now that this would be a subreport within a subreport, so I hope it works for me.

Can you please post how to use the VBA so it looks at a recordset that is not associated to the Report's recordset in an unbound control?

So if my report's name is rptOffices, the query with the Totals is qryTotalSumSF, and the field in the query that I need to be reflected on my report is called TotalSumSF, how would I ues VBA on the report to refer to this field?

[ItsMe]

I would use DAO to open a recordset and retrieve the data I wanted to store the data in variables.

Here is an example that retrieves a variable and stores it in a string.

Dim strBill As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT BOL.Bill_of_Lading_Number, BOL.Job_Number FROM BOL WHERE BOL.Job_Number = " & Application.TempVars("varsJobCont").Value)
If rst.EOF = False Then
strBill = rst![Bill_of_Lading_Number]
End If
rst.Close
Set rst = Nothing
Set dbs = Nothing

You could run this code in the Report's load event or somewhere else, like a form that opens the report. The idea is you store the data in a variable. The variable could be declared as globally available or it could be private to the report. You can also pass data to the report using open args in a Docmd.Openreport statement.

Once the report starts to open, the On Format event for the proper section in your report will grab the available variable and assign the value to your unbound control.

[Varda]

I managed to get the subreport to work in my report. The question is - between your code and my solution with the subreport - which one is a "faster" solution for the database to run? Meaning, if I have to run this report, does it require more fire power to run my subsubreport, or more to run your VBA in the On Format event?

[ItsMe]

Because of sorting and grouping the subreport may run slower. It should be relatively even. In a perfect world the determining factor for speed would be the structure of your tables and how you build your queries. DAO is an object like a report is an object. Creating a recordset with a Report is just like creating a recordset directly with DAO.