Group by Rollup

[Stacey Crowhurst]

Hi. My query "qryInitialRevisedCurrentBudgetCombined" is the basis for my
report "rptBudgetsCombined". The query SQL is below. I created a form that
enables the user to select one or more project status's (pstStatusID). For
example they could select feasibility and construction. Then they use a
command button to open the "rptBudgetsCombined" report, which sums the budget
data for all projects fitting the status criteria.

My problem is that I need the pstStatusID field in the query for the filter
to work. However, having it in the query creates 'duplicate' rows on my
report. The report looks like the following:

Budget Code & Desc Initial Budget Revised Budget Current Budget
1002 AV Systems $500,000 $0 $500,000
1002 AV Systems $150,000 $50,000 $200,000
(showing me a row for each pstStatusID)
I tried to troubleshoot online to find a solution that would present the
data as follows:
Budget Code & Desc Initial Budget Revised Budget Current Budget
1002 AV Systems $650,000 $50,000 $700,000

I think the Group by Rollup function would do the trick but I don't know
that you can use it with Access 2003 SP3. Any help on the rollup function or
other ideas is greatly appreciated. THANKS!

SQL-
SELECT qryInitialRevisedCurrentBudget.Type,
qryInitialRevisedCurrentBudget.budBudgetCodeID,
qryInitialRevisedCurrentBudget.bcBudgetCodeDesc,
Sum(qryInitialRevisedCurrentBudget.InitialBudget) AS [Initial Budget],
Sum(qryInitialRevisedCurrentBudget.RevisedBudget) AS [Revised Budget],
Sum(qryInitialRevisedCurrentBudget.CurrentBudget) AS [Current Budget],
qryInitialRevisedCurrentBudget.pstStatusID
FROM qryInitialRevisedCurrentBudget
GROUP BY qryInitialRevisedCurrentBudget.Type,
qryInitialRevisedCurrentBudget.budBudgetCodeID,
qryInitialRevisedCurrentBudget.bcBudgetCodeDesc,
qryInitialRevisedCurrentBudget.pstStatusID;

 

[Rob Parker]

Hi Stacey,

I'm not sure exactly what you mean by "... the Group by Rollup function"; is
that something from Access 2007? Nevertheless, here's some suggestions
which should help, and which will work in A2003.

The basic reason that your query is returning multiple records for the same
Budget Code & Desc is you have the pstStatusID field included in the GROUP
BY clause. The normal way to fix that is to change the expression in the
Totals row to Where (and include the criteria for that field) so that your
query becomes something like:

SELECT ... (omitting the pstStatusID field)
FROM ....
WHERE qryInitialRevisedCurrentBudget.pstStatusID = 1
GROUP BY ... (omitting the pstStatusID field)

To do this you need to include the criteria you wish to filter by in the
query - you can't leave the criteria row empty or the SQL view of the query
will not include the WHERE clause.

You don't say exactly how your report is being filtered (I'm assuming that
you build a Where parameter for the DoCmd.OpenReport statement in code,
based on the selection(s) on the form - in which case you must, as you say,
have the pstStatusID field in your query), so I'm sure what the easiest
means of getting around the problem will be; but there's a couple of methods
you could use.

The simplest solution is probably to leave the query as is, and do the
"roll-up" in your report. Add a Group based on "Budget Code & Desc", and
place that field in either the group header or footer. Add textboxes for
three budget fields, and set their control sources to expressions such as
"=Sum([Initial Budget])". Set the detail section's Visible property to No,
and you're done.

Alternatively, you could build the Where parameter (as I'm assuming you're
doing at the moment) and combine it with two other strings to generate the
SQL for the query with the filter in place, and redefine the query -
something like:

Dim Qry As DAO.QueryDef
Dim strSQL1 As String
Dim strSQL2 As String
Dim strWhere As String

strSQL1 = "SELECT ... FROM ..."
strSQL2 = " GROUP BY ..."
strWhere = 'however you're currently doing this - ensure there is a space
before the WHERE keyword for the later concatenation to work

Set Qry = CurrentDb.QueryDefs("qryInitialRevisedCurrentBudgetCombined")
Qry.SQL = strSQL1 & strWhere & strSQL2
Qry.Close
Set Qry = Nothing

HTH,

Rob

Hi. My query "qryInitialRevisedCurrentBudgetCombined" is the basis
for my report "rptBudgetsCombined". The query SQL is below. I
created a form that enables the user to select one or more project
status's (pstStatusID). For example they could select feasibility
and construction. Then they use a command button to open the
"rptBudgetsCombined" report, which sums the budget data for all
projects fitting the status criteria.

My problem is that I need the pstStatusID field in the query for the
filter to work. However, having it in the query creates 'duplicate'
rows on my report. The report looks like the following:

Budget Code & Desc Initial Budget Revised Budget Current Budget
1002 AV Systems $500,000 $0
$500,000 1002 AV Systems $150,000 $50,000
$200,000 (showing me a row for each pstStatusID)
I tried to troubleshoot online to find a solution that would present
the data as follows:
Budget Code & Desc Initial Budget Revised Budget Current Budget
1002 AV Systems $650,000 $50,000
$700,000

I think the Group by Rollup function would do the trick but I don't
know that you can use it with Access 2003 SP3. Any help on the
rollup function or other ideas is greatly appreciated. THANKS!

SQL-
SELECT qryInitialRevisedCurrentBudget.Type,
qryInitialRevisedCurrentBudget.budBudgetCodeID,
qryInitialRevisedCurrentBudget.bcBudgetCodeDesc,
Sum(qryInitialRevisedCurrentBudget.InitialBudget) AS [Initial Budget],
Sum(qryInitialRevisedCurrentBudget.RevisedBudget) AS [Revised Budget],
Sum(qryInitialRevisedCurrentBudget.CurrentBudget) AS [Current Budget],
qryInitialRevisedCurrentBudget.pstStatusID
FROM qryInitialRevisedCurrentBudget
GROUP BY qryInitialRevisedCurrentBudget.Type,
qryInitialRevisedCurrentBudget.budBudgetCodeID,
qryInitialRevisedCurrentBudget.bcBudgetCodeDesc,
qryInitialRevisedCurrentBudget.pstStatusID;

 

[Stacey Crowhurst]

Thank you Rob. I didn't realize I could do the roll up in the report design.
It works perfect now!!!

Hi Stacey,

I'm not sure exactly what you mean by "... the Group by Rollup function"; is
that something from Access 2007? Nevertheless, here's some suggestions
which should help, and which will work in A2003.

The basic reason that your query is returning multiple records for the same
Budget Code & Desc is you have the pstStatusID field included in the GROUP
BY clause. The normal way to fix that is to change the expression in the
Totals row to Where (and include the criteria for that field) so that your
query becomes something like:

SELECT ... (omitting the pstStatusID field)
FROM ....
WHERE qryInitialRevisedCurrentBudget.pstStatusID = 1
GROUP BY ... (omitting the pstStatusID field)

To do this you need to include the criteria you wish to filter by in the
query - you can't leave the criteria row empty or the SQL view of the query
will not include the WHERE clause.

You don't say exactly how your report is being filtered (I'm assuming that
you build a Where parameter for the DoCmd.OpenReport statement in code,
based on the selection(s) on the form - in which case you must, as you say,
have the pstStatusID field in your query), so I'm sure what the easiest
means of getting around the problem will be; but there's a couple of methods
you could use.

The simplest solution is probably to leave the query as is, and do the
"roll-up" in your report. Add a Group based on "Budget Code & Desc", and
place that field in either the group header or footer. Add textboxes for
three budget fields, and set their control sources to expressions such as
"=Sum([Initial Budget])". Set the detail section's Visible property to No,
and you're done.

Alternatively, you could build the Where parameter (as I'm assuming you're
doing at the moment) and combine it with two other strings to generate the
SQL for the query with the filter in place, and redefine the query -
something like:

Dim Qry As DAO.QueryDef
Dim strSQL1 As String
Dim strSQL2 As String
Dim strWhere As String

strSQL1 = "SELECT ... FROM ..."
strSQL2 = " GROUP BY ..."
strWhere = 'however you're currently doing this - ensure there is a space
before the WHERE keyword for the later concatenation to work

Set Qry = CurrentDb.QueryDefs("qryInitialRevisedCurrentBudgetCombined")
Qry.SQL = strSQL1 & strWhere & strSQL2
Qry.Close
Set Qry = Nothing

HTH,

Rob

Hi. My query "qryInitialRevisedCurrentBudgetCombined" is the basis
for my report "rptBudgetsCombined". The query SQL is below. I
created a form that enables the user to select one or more project
status's (pstStatusID). For example they could select feasibility
and construction. Then they use a command button to open the
"rptBudgetsCombined" report, which sums the budget data for all
projects fitting the status criteria.

My problem is that I need the pstStatusID field in the query for the
filter to work. However, having it in the query creates 'duplicate'
rows on my report. The report looks like the following:

Budget Code & Desc Initial Budget Revised Budget Current Budget
1002 AV Systems $500,000 $0
$500,000 1002 AV Systems $150,000 $50,000
$200,000 (showing me a row for each pstStatusID)
I tried to troubleshoot online to find a solution that would present
the data as follows:
Budget Code & Desc Initial Budget Revised Budget Current Budget
1002 AV Systems $650,000 $50,000
$700,000

I think the Group by Rollup function would do the trick but I don't
know that you can use it with Access 2003 SP3. Any help on the
rollup function or other ideas is greatly appreciated. THANKS!

SQL-
SELECT qryInitialRevisedCurrentBudget.Type,
qryInitialRevisedCurrentBudget.budBudgetCodeID,
qryInitialRevisedCurrentBudget.bcBudgetCodeDesc,
Sum(qryInitialRevisedCurrentBudget.InitialBudget) AS [Initial Budget],
Sum(qryInitialRevisedCurrentBudget.RevisedBudget) AS [Revised Budget],
Sum(qryInitialRevisedCurrentBudget.CurrentBudget) AS [Current Budget],
qryInitialRevisedCurrentBudget.pstStatusID
FROM qryInitialRevisedCurrentBudget
GROUP BY qryInitialRevisedCurrentBudget.Type,
qryInitialRevisedCurrentBudget.budBudgetCodeID,
qryInitialRevisedCurrentBudget.bcBudgetCodeDesc,
qryInitialRevisedCurrentBudget.pstStatusID;