Creating a Rollup report. Three seperate queries in one report?

[hcuk]

I am trying to create a report that would pull form a table three times. I've attached an example report.



I will explain the data in picture. The number in the "Increased BOE" row are COUNTS that are pulled from the same table with different project Status's (i.e. Completed, In Progress, Everything combined)
The second row, is a SUM of the column "OpEx Savings in $", again with different project status's.

I'm wondering if these values first need to be inserted into a new table or I can somehow create a query that would show all three.

Thanks in advance,
Richard

[June7]

Could try a UNION query, something like:

SELECT [Completed], [Progress], [Total] FROM queryA
UN ION SELECT [Completed], [Progress], [Total] FROM queryB;

Must type into SQL View of query builder.

[hcuk]

Could try a UNION query, something like:

SELECT [Completed], [Progress], [Total] FROM queryA
UN ION SELECT [Completed], [Progress], [Total] FROM queryB;

Must type into SQL View of query builder.

Hi June7,

This is what I typed into the SQL view,

SELECT Sum([Opportunity - Full List].[OPEX Savings Per Day]) AS CompletedOpexTotal
FROM [Opportunity - Full List] where [Opportunity - Full List].[Status]='Completed'
UNION
SELECT Sum([Opportunity - Full List].[OPEX Savings Per Day]) AS InProgressTotal
FROM [Opportunity - Full List] where [Opportunity - Full List].[Status]='In Progress'
UNION
SELECT Sum([Opportunity - Full List].[OPEX Savings Per Day]) AS Total2015
FROM [Opportunity - Full List]
;

When I run this query, only the first Union shows up (CompletedOpexTotal). I'm only testing for the SUM's of these columns.

[June7]

Wait, I thought you had 2 queries with 3 fields. I am confused.

Show example raw data from table and example of desired output.

[Rawb]

You could use either a Crosstab Query or the DCount() and DSum() functions.

[hcuk]

Wait, I thought you had 2 queries with 3 fields. I am confused.

Show example raw data from table and example of desired output.

This is the test data that I will use as an example. So I would like a report that would show the SUM of "OPEX Savings Per Day" with the Status='Complete' or 'In Progress', then SUM these two totals. In the same report I would like to show a COUNT of how many projects have a status of 'Complete' and a status of 'In Progress', then SUM that count. I hope this clarifies it.

You could use either a Crosstab Query or the DCount() and DSum() functions.

I have never used these functions or queries before.

[June7]

A CROSSTAB requires 3 fields for RowHeader, ColumnHeader, Value. Since [Dollar Increase Per Day] and [OPEX Savings Per Day] are in separate fields, this is not a normalized structure and CROSSTAB cannot be used with this table as is. Getting those fields into normalized structure will require UNION. Is there some unique record identifier field (ID, AcctNum, ?)?

SELECT ID, "Increased_BOE" AS Category, [Dollar Increase Per Day] As Data, Status FROM table
UN ION SELECT ID, "OPEX_Savings", [OPEX Savings Per Day], Status FROM table;

Now that query can be the source for a CROSSTAB or an aggregate query or a report that uses Grouping & Sorting features with aggregate calcs in header/footer sections. Report would allow display of detail records as well as summary calcs.

[hcuk]

A CROSSTAB requires 3 fields for RowHeader, ColumnHeader, Value. Since [Dollar Increase Per Day] and [OPEX Savings Per Day] are in separate fields, this is not a normalized structure and CROSSTAB cannot be used with this table as is. Getting those fields into normalized structure will require UNION. Is there some unique record identifier field (ID, AcctNum, ?)?

SELECT ID, "Increased_BOE" AS Category, [Dollar Increase Per Day] As Data, Status FROM table
UN ION SELECT ID, "OPEX_Savings", [OPEX Savings Per Day], Status FROM table;

Now that query can be the source for a CROSSTAB or an aggregate query or a report that uses Grouping & Sorting features with aggregate calcs in header/footer sections. Report would allow display of detail records as well as summary calcs.

Sorry not sure if you understand what I am trying to do here. haha.

I want a report to be able to show a column with the TOTAL/SUM of projects that have a "Completed" and another column that shows the TOTAL/SUM of projects that have a "In Progress' status. Finally another column that would show the total of the 2 columns. So in the end the report should like like the image that I posted in the first post.

Thanks for your help by the way June7!

[June7]

I am trying to get your data manipulated to the output you show in the first post.

It's not clear where the data for the Increased BOE row comes from.

Did you follow the instructions in my previous post?

[hcuk]

I am trying to get your data manipulated to the output you show in the first post.

It's not clear where the data for the Increased BOE row comes from.

Did you follow the instructions in my previous post?

Hi,

Sorry for the delayed response. I've included full table columns, there is an ID which is an autonumber. All the data is entered by the user which i've also included a screenshot of.



This table is called "Opportunity - Full List". I've tried using a UNION query to get the desired output, but it .

SELECT Sum([Opportunity - Full List].[OPEX Savings Per Day]) AS CompletedOpexTotalFROM [Opportunity - Full List] where [Opportunity - Full List].[Status]='Completed'
UNION
SELECT Sum([Opportunity - Full List].[OPEX Savings Per Day]) AS InProgressTotal
FROM [Opportunity - Full List] where [Opportunity - Full List].[Status]='In Progress'
UNION
SELECT Sum([Opportunity - Full List].[OPEX Savings Per Day]) AS Total2015
FROM [Opportunity - Full List]
UNION
SELECT SUM([Opportunity - Full List].[Gas BOE Per Day] + [Opportunity - Full List].[Oil BOE Per Day]) AS BOETotal
FROM [Opportunity - Full List] where [Opportunity - Full List].[Status]='In Progress'
UNION
SELECT SUM([Opportunity - Full List].[Gas BOE Per Day] + [Opportunity - Full List].[Oil BOE Per Day]) AS BOETotal
FROM [Opportunity - Full List] where [Opportunity - Full List].[Status]='Completed'
UNION
SELECT SUM([Opportunity - Full List].[Gas BOE Per Day] + [Opportunity - Full List].[Oil BOE Per Day]) AS BOETotal
FROM [Opportunity - Full List]
;

As you can see from the above screenshot. The output doesn't match up with the desired output. Please advise.

Thank you,
Richard

[Rawb]

The mismatch of your Union Query comes from the fact that you're sorting the results by value (the down arrow next to "CompletedOpexTotal"). It looks like all of the correct results are there.

• 952 is the correct result for the first Query in the UNION.
• 30 is the correct result for the second
• 982 is the correct result for the third
• 0 is the correct result for the fourth
• 7 is the correct result for the fifth
• 7 is the correct result for the last query*

* Unless you're using grouping, this should show up as a separate row in the results (so there should be two rows with the number 7, not just one). This is the only issue I can see with the UNION query offhand.

[hcuk]

The mismatch of your Union Query comes from the fact that you're sorting the results by value (the down arrow next to "CompletedOpexTotal"). It looks like all of the correct results are there.

• 952 is the correct result for the first Query in the UNION.
• 30 is the correct result for the second
• 982 is the correct result for the third
• 0 is the correct result for the fourth
• 7 is the correct result for the fifth
• 7 is the correct result for the last query*

* Unless you're using grouping, this should show up as a separate row in the results (so there should be two rows with the number 7, not just one). This is the only issue I can see with the UNION query offhand.

I may have solved this issue. What I did:

1. Created a seperate query for each desired result.
2. Created a rollup query that included the 6 queries I created in the previous step.
3. Created a report that had the rollup query as the data source.

I believe this should work for what I am trying to do. I will get the user to test this and give an update.

Thank you!