Getting an error when trying to run this union query

[xcmuchip]

select date, [N/S], productcode, sum([#CS]) as SumCases from espnorth

UNION select date, [N/S], productcode, sum([#cs]) as SumCases from espsouth
group by date, [n/s], productcode
ORDER BY date, productcode;

Error is "You tried to execute a query that doesn't include the specified expression 'date' as part of an aggregate function." These are pre-existing databases, so the names of the fields are not my idea. If I take the sum function out, the error disappears.

Why am I getting this error? Thanks for your help.

 

[tlbroadbent]

Add brackets around the name of the date column.

UNION
SELECT
[date], [N/S],
productcode, sum([#cs]) as SumCases
FROM espsouth
GROUP BY [date], [n/s], productcode
ORDER BY [date], productcode;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[xcmuchip]

I've tried that already, thanks. Still get the same error.

 

[tlbroadbent]

Either include the Group By in each select statement

select
date, [N/S], productcode,
sum([#CS]) as SumCases
from espnorth
group by date, [n/s], productcode

UNION

select
date, [N/S], productcode,
sum([#CS]) as SumCases
from espsouth
group by date, [n/s], productcode

ORDER BY date, productcode;

Or use the Union query as a sub-query as follows.

Select
[Date], [N/S], productcode,
sum([#CS]) as SumCases
From
(Select date, [N/S], productcode, [#CS]
From espnorth
UNION ALL
Select date, [N/S], productcode, [#CS]
From espsouth) As qry

Group By date, [n/s], productcode
ORDER BY date, productcode;

I recommend using the ALL predicate to prevent Access from sorting the results of the UNION query eliminating duplicates. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.