Roll Up Query Data

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have a query that is the source for a report on construction budgets.
I want the data to be rolled up (which would be 32 rows). But I need the
pstStatusID field in the query becuase a filter searches that field before
opening the report. The status ID is "design" or "feasibility" or
"construction" etc. And I have a multiselect list box that allows the user
to choose which phases to get the budget report data for. So I need the
pstStatusID field, but having it in the query mutiplies the result rows.
I'll get the following:
Type-budBudgetCodeID-bcBudgetCodeDesc-Initial Budget-Revised Budget-Current
Budget
Hard Costs -105-100-Construction Contracts-500-0-500
Hard Costs -105-100-Construction Contracts-1500-0-1500

Instead of
Hard Costs -105-100-Construction Contracts-2000-0-2000

How do I work around that?

Here is the 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;

Thanks a million!!! Stacey
 
Tom, your reply makes perfect sense. And you are right with your assumptions
about my example. I forgot to include the status ID.

My problem is figuring out how to include the pstStatusID in the query so
that the list box can use it as a filter, but have the report roll up the
results like the WHERE statement did.

The list box will run ok and the report will give me the proper data it just
isn't rolled up like I would like.

Thanks for your help.
Stacey

Tom van Stiphout said:
On Mon, 30 Mar 2009 16:43:01 -0700, Stacey Crowhurst

Your example values do not match your query: the pstStatusID is not
given.
Speculating, I think the output would be:
Hard Costs-105-100-Construction Contracts-500-0-500-1
Hard Costs-105-100-Construction Contracts-1500-0-1500-2

So the query does exactly what you asked: group by pstStatusID, which
has an effect of showing the various pstStatusID values.
Perhaps you meant to make this a WHERE clause element. In the Total
row select Where.
I'm not sure how to easily set the Criteria row for this field to the
selected items in your listbox. You may need to google for that.

-Tom.
Microsoft Access MVP


Hi. I have a query that is the source for a report on construction budgets.
I want the data to be rolled up (which would be 32 rows). But I need the
pstStatusID field in the query becuase a filter searches that field before
opening the report. The status ID is "design" or "feasibility" or
"construction" etc. And I have a multiselect list box that allows the user
to choose which phases to get the budget report data for. So I need the
pstStatusID field, but having it in the query mutiplies the result rows.
I'll get the following:
Type-budBudgetCodeID-bcBudgetCodeDesc-Initial Budget-Revised Budget-Current
Budget
Hard Costs -105-100-Construction Contracts-500-0-500
Hard Costs -105-100-Construction Contracts-1500-0-1500

Instead of
Hard Costs -105-100-Construction Contracts-2000-0-2000

How do I work around that?

Here is the 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;

Thanks a million!!! Stacey
Click to expand...
Click to expand...
 
On Tue, 31 Mar 2009 09:10:11 -0700, Stacey Crowhurst

A quick-n-dirty way to do it is to get the list of selected IDs and
construct a where-clause with IN:
select ...
where pstStatusID IN (1,2,5,6)
You can do this by dynamically setting the SQL property of the
querydef object. You can NOT do this by having a parameter:
....where pstStatusID IN ([parStatusIDs]) 'This does NOT work
You can also replace your query with a sqlstatement that you create on
the fly.

Again, for more elegant solutions you may need to google a bit.

-Tom.
Microsoft Access MVP


Tom, your reply makes perfect sense. And you are right with your assumptions
about my example. I forgot to include the status ID.

My problem is figuring out how to include the pstStatusID in the query so
that the list box can use it as a filter, but have the report roll up the
results like the WHERE statement did.

The list box will run ok and the report will give me the proper data it just
isn't rolled up like I would like.

Thanks for your help.
Stacey

Tom van Stiphout said:
On Mon, 30 Mar 2009 16:43:01 -0700, Stacey Crowhurst

Your example values do not match your query: the pstStatusID is not
given.
Speculating, I think the output would be:
Hard Costs-105-100-Construction Contracts-500-0-500-1
Hard Costs-105-100-Construction Contracts-1500-0-1500-2

So the query does exactly what you asked: group by pstStatusID, which
has an effect of showing the various pstStatusID values.
Perhaps you meant to make this a WHERE clause element. In the Total
row select Where.
I'm not sure how to easily set the Criteria row for this field to the
selected items in your listbox. You may need to google for that.

-Tom.
Microsoft Access MVP


Hi. I have a query that is the source for a report on construction budgets.
I want the data to be rolled up (which would be 32 rows). But I need the
pstStatusID field in the query becuase a filter searches that field before
opening the report. The status ID is "design" or "feasibility" or
"construction" etc. And I have a multiselect list box that allows the user
to choose which phases to get the budget report data for. So I need the
pstStatusID field, but having it in the query mutiplies the result rows.
I'll get the following:
Type-budBudgetCodeID-bcBudgetCodeDesc-Initial Budget-Revised Budget-Current
Budget
Hard Costs -105-100-Construction Contracts-500-0-500
Hard Costs -105-100-Construction Contracts-1500-0-1500

Instead of
Hard Costs -105-100-Construction Contracts-2000-0-2000

How do I work around that?

Here is the 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;

Thanks a million!!! Stacey
Click to expand...
Click to expand...
Click to expand...
 
Back
Top