Union query filtering out duplicate records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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;
 
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.
 
Back
Top