Union query filtering out duplicate records

C

cpugh

What do I need to change so this query will not filter out duplicate records?
I know there is an ALL function but I cannot get the syntax to work.

TRANSFORM Sum([UNION - Gas Revenue and Other Customer Revenue].[Current
Value]) AS [SumOfCurrent Value]
SELECT [UNION - Gas Revenue and Other Customer Revenue].[District Code],
[UNION - Gas Revenue and Other Customer Revenue].Source, [UNION - Gas Revenue
and Other Customer Revenue].[District Name], [UNION - Gas Revenue and Other
Customer Revenue].[Report Date]
FROM [UNION - Gas Revenue and Other Customer Revenue]
GROUP BY [UNION - Gas Revenue and Other Customer Revenue].[District Code],
[UNION - Gas Revenue and Other Customer Revenue].Source, [UNION - Gas Revenue
and Other Customer Revenue].[District Name], [UNION - Gas Revenue and Other
Customer Revenue].[Report Date]
PIVOT [UNION - Gas Revenue and Other Customer Revenue].Category;
 
M

Mark Fitzpatrick

Technically this isn't a union as you are not using the SQL union predicate
to join two or more sql selects, instead this looks like OLTP for data
mining. Have you tried removing the PIVOT statement or one of the selections
in the Group By? It's probably the Group By as that will lump records with
those fields together and, since this is a query designed to display in a
pivot table, it expects to have some duplicated eliminated for displaying as
appropriate for a pivot table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top