Totals from 3 queries in one report

[Guest]

I have very basic Access abilities -- and unfortunately do not use it
regularly.

I created three queries because I wanted to total each query using different
selection criteria.

After struggling with not so nested ifs and recalcitrant parameters, I have
each of the three queries producing several different column totals based on
the criteria specific to that query. [e.g., one query might identify the
number of people who have been members of an organization for over 10 years
(etc.) and would show the total assets that they have as a group accumulated
to date. A second query might show comparable output for a group who meet
different criteria.]

Now, unfortunately, I am at the stage of pulling (only) the totals into one
report. This too appears to be working but I have so far only pulled labels
and grand totals from one query. How do I pull the grand totals from the
other query onto this one report? Any suggestions would be very much
appreciated!!

 

[James A. Fortune]

I have very basic Access abilities -- and unfortunately do not use it
regularly.

I created three queries because I wanted to total each query using different
selection criteria.

After struggling with not so nested ifs and recalcitrant parameters, I have
each of the three queries producing several different column totals based on
the criteria specific to that query. [e.g., one query might identify the
number of people who have been members of an organization for over 10 years
(etc.) and would show the total assets that they have as a group accumulated
to date. A second query might show comparable output for a group who meet
different criteria.]

Now, unfortunately, I am at the stage of pulling (only) the totals into one
report. This too appears to be working but I have so far only pulled labels
and grand totals from one query. How do I pull the grand totals from the
other query onto this one report? Any suggestions would be very much
appreciated!!

Here's one way to get the information from the other groupings into a
report:

'Begin Module Code
Public Function ReturnSQLResult(strSQL As String) As Variant
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount <> 0 Then
MyRS.MoveFirst
ReturnSQLResult = MyRS(0)
Else
ReturnSQLResult = Null
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function
'End Module Code

Then you can do something like:

Report Fields:
TotalGroup1.ControlSource
=ReturnSQLResult("SELECT Sum(Amount) FROM MyTable WHERE MyFlag = -1;")

TotalGroup2.ControlSource
=ReturnSQLResult("SELECT Sum(Amount) FROM MyTable WHERE Department =
'Accounting';")

TotalGroup3.ControlSource
=ReturnSQLResult("SELECT Sum(Amount) FROM MyTable WHERE InvoiceDate IS
NOT Null;")

TotalGroup4.ControlSource
=ReturnSQLResult("SELECT Count(*) FROM MyTable WHERE InvoiceDate IS NOT
Null;")

Make sure that the '=' sign is at the beginning of the ControlSource.
Subreports might be another way to get unrelated or regrouped data onto
the report.

James A. Fortune
(e-mail address removed)

China is the world's leading producer of honey:
http://confectionerynews.com/news/ng.asp?id=53255&n=wh28&ec=#emailcode

 

[Guest]

James, thank you so much for your detailed response. Given my very limited
Access experience, the sample code is a real gift!
Dianne

I have very basic Access abilities -- and unfortunately do not use it
regularly.

I created three queries because I wanted to total each query using different
selection criteria.

After struggling with not so nested ifs and recalcitrant parameters, I have
each of the three queries producing several different column totals based on
the criteria specific to that query. [e.g., one query might identify the
number of people who have been members of an organization for over 10 years
(etc.) and would show the total assets that they have as a group accumulated
to date. A second query might show comparable output for a group who meet
different criteria.]

Now, unfortunately, I am at the stage of pulling (only) the totals into one
report. This too appears to be working but I have so far only pulled labels
and grand totals from one query. How do I pull the grand totals from the
other query onto this one report? Any suggestions would be very much
appreciated!!

Here's one way to get the information from the other groupings into a
report:

'Begin Module Code
Public Function ReturnSQLResult(strSQL As String) As Variant
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount <> 0 Then
MyRS.MoveFirst
ReturnSQLResult = MyRS(0)
Else
ReturnSQLResult = Null
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function
'End Module Code

Then you can do something like:

Report Fields:
TotalGroup1.ControlSource
=ReturnSQLResult("SELECT Sum(Amount) FROM MyTable WHERE MyFlag = -1;")

TotalGroup2.ControlSource
=ReturnSQLResult("SELECT Sum(Amount) FROM MyTable WHERE Department =
'Accounting';")

TotalGroup3.ControlSource
=ReturnSQLResult("SELECT Sum(Amount) FROM MyTable WHERE InvoiceDate IS
NOT Null;")

TotalGroup4.ControlSource
=ReturnSQLResult("SELECT Count(*) FROM MyTable WHERE InvoiceDate IS NOT
Null;")

Make sure that the '=' sign is at the beginning of the ControlSource.
Subreports might be another way to get unrelated or regrouped data onto
the report.

James A. Fortune
(e-mail address removed)

China is the world's leading producer of honey:
http://confectionerynews.com/news/ng.asp?id=53255&n=wh28&ec=#emailcode