CODEWITHSUNDEEP
sqlms-accessaggregate-functions
Margi
Margi

Reputation: 3

Query does not include the specified expression as part of an aggregate function in UNION query

I am doing a Union Query to add together the results of two separate queries that give me data from two different fiscal periods, to get a rolling 12 months number. I get the message "Your query does not include the specified expression "Report_Header" as part of an aggregate function". I have read that the field needs to be included in a GROUP BY statement at the end, but when I add the field from either query or with both queries as shown below I still get the message. Help? I'm not a programmer, I'm an Access user, so I need to simple please :).

SELECT [JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].Report_Header,
       Sum([JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].SumOfCASES) AS CASES,
       Sum([JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].SumOfPurchases) AS PURCHASES
FROM [JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB]

UNION ALL

SELECT [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].Report_Header,
       Sum([JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].SumOfCASES) AS CASES,
       Sum([JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].SumOfPurchases) AS PURCHASES
FROM [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2]
GROUP BY [JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].Report_Header,
         [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].Report_Header

Thanks!

Upvotes: 0

Views: 278

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can aggregate both subqueries:

SELECT [JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].Report_Header,
       Sum([JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].SumOfCASES) AS CASES,
       Sum([JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].SumOfPurchases) AS PURCHASES
FROM [JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB]
GROUP BY [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB].Report_Header

UNION ALL

SELECT [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].Report_Header,
       Sum([JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].SumOfCASES) AS CASES,
       Sum([JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].SumOfPurchases) AS PURCHASES
FROM [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2]
GROUP BY [JOIN_IB_FREIGHT&PURCHASES_Rolling12_SUB2].Report_Header;

This may be what you want. But, it will not combine information under the same header from both tables. For that, the simplest method is probably a view.

Upvotes: 0

Mike Deluca
Mike Deluca

Reputation: 1210

Place GROUP BY [JOIN_IB_FREIGHT&PURCHASES_ROLLING12_SUB].Report_Header under the first query instead of the second.

Upvotes: 0

Related Questions