Issue Grouping in Access Union Query

Question

I built a Union query in Access that takes results from two separate queries and joins them together. It's working fine but the results are not being grouped by Sales, Cost, and Profit, where I am trying to sum on those three fields.

Here is my code:

SELECT 

Store,
count( [MASTER CREDIT MEMO QUERY].[Count]) as Count,
[Sales Code],
Name,
Sum( [MASTER CREDIT MEMO QUERY].[Sales]) as Sales,
Sum( [MASTER CREDIT MEMO QUERY].[Cost]) as Cost,
Sum( [MASTER CREDIT MEMO QUERY].[Profit]) as Profit

FROM [MASTER CREDIT MEMO QUERY]

GROUP by

Store,
[Sales Code],
Name

UNION SELECT 

Store,
count([MASTER SALES INVOICE QUERY].[Count]) as Count,
[Sales Code],
Name,
Sum([MASTER SALES INVOICE QUERY].[Sales]) as Sales,
Sum([MASTER SALES INVOICE QUERY].[Cost]) as Cost,
Sum([MASTER SALES INVOICE QUERY].[Profit]) as Profit

FROM [MASTER SALES INVOICE QUERY]

GROUP BY

Store,
[Sales Code],
Name
ORDER BY Sales DESC;

Can anyone help me get the grouping to work?

start by taking out the ORDER BY statement and see what that does. If it runs properly then try setting up the query a little differently, like this:https://stackoverflow.com/a/3394454/8112776 — ashleedawg, Mar 16 '18 at 21:24

Alex Zen · Accepted Answer · 2018-03-19T14:30:27.450

1

You are grouping the separate selects in the union, rather than the result of the union itself. You should put the group by outside of the whole query, as well as the group functions.

Please try this one (I'm not familiar with ms-access syntax, but it should work):

SELECT [Store],
       count([Count]) as [Count],
       [Sales Code],
       Name,
       Sum( [Sales]) as Sales,
       Sum( [Cost]) as Cost,
       Sum( [Profit]) as Profit
  FROM (SELECT Store, [Count], Sales, Cost, Profit, Name, [Sales Code]
          FROM [MASTER CREDIT MEMO QUERY]
         UNION
        SELECT Store, [Count], Sales, Cost, Profit, Name, [Sales Code]
          FROM [MASTER SALES INVOICE QUERY]) t
 GROUP BY Store, [Sales Code], Name
 ORDER BY Sales DESC

edited Mar 19 '18 at 14:30

answered Mar 16 '18 at 21:32

Alex Zen

906
7
9

Thanks, Alex. Unfortunately, it didn't like the syntax. I got an error message: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." I'm wondering if I can maybe create a subquery that I can can then join to another query. – jbtheanalyst Mar 19 '18 at 14:15
I updated the query. I think "Count" is a reserved word, so I put it between brackets. Please try it again – Alex Zen Mar 19 '18 at 14:31

score 0 · Answer 2 · answered Mar 19 '18 at 15:06

Alex - thanks for your help. Your solution didn't work exactly, but you got me 95% of the way. I took your code and modified it after trying to figure out exactly what it was doing.

Here is the solution, in case you wanted to know.

Thanks so much for your assistance.

SELECT TBL1.Store, Count(TBL1.Count) AS [Count], TBL1.[Sales Code], TBL1.Name, Sum(TBL1.Sales) AS Sales, Sum(TBL1.Cost) AS Cost, Sum(TBL1.Profit) AS Profit
FROM (SELECT 

Store, 
Count, 
[MASTER CREDIT MEMO QUERY].Sales, 
Cost, 
Profit, 
Name, 
[Sales Code]

FROM [MASTER CREDIT MEMO QUERY]

UNION 

SELECT 

Store, 
Count, 
[MASTER SALES INVOICE QUERY].Sales, 
Cost, 
Profit, 
Name, 
[Sales Code]

FROM [MASTER SALES INVOICE QUERY])  AS TBL1
GROUP BY TBL1.Store, TBL1.[Sales Code], TBL1.Name
ORDER BY TBL1.Name;

Issue Grouping in Access Union Query

2 Answers2