UNION ALL PIVOT SQL query

[Jon von der Heyden]

Hi All

I have a table for collecting results for different KPI's. When it comes to consolidating / aggregating the table into summaries, some KPI's should be summed (e.g. volume of corporate cards) and some KPI's should be averaged (e.g. average spend on corporate card). Therefore I have a field in the table that indicates which function to use for each record.

I would like to create a crosstab query, but I believe in order to have the average and sum results aggregate correctly I first need to select the 'Sum' records and then select the 'Average' records. Once the aggregate functions have been applied then I should be able to UNION ALL the results, right?

I come up with this:

TRANSFORM Sum(KPI_TBL.RESULT) AS SumOfRESULT
SELECT KPI_TBL.MAJOR_COMMODITY, KPI_TBL.KPI_NAME, KPI_TBL.KPI_DESCRIPTION, Sum(KPI_TBL.RESULT) AS TOTAL
FROM KPI_TBL
WHERE (((KPI_TBL.Period) = #5/1/2011#) And ((KPI_TBL.RECORD_TYPE) = 0) And ((KPI_TBL.COUNTRY) = "GB") And ((KPI_TBL.Function) = "Sum"))
GROUP BY KPI_TBL.MAJOR_COMMODITY, KPI_TBL.KPI_NAME, KPI_TBL.KPI_DESCRIPTION
ORDER BY KPI_TBL.MAJOR_COMMODITY, KPI_TBL.KPI_NAME
PIVOT KPI_TBL.SECTOR
UNION ALL
TRANSFORM Avg(KPI_TBL.RESULT) AS AvgOfRESULT
SELECT KPI_TBL.MAJOR_COMMODITY, KPI_TBL.KPI_NAME, KPI_TBL.KPI_DESCRIPTION, Avg(KPI_TBL.RESULT) AS TOTAL
FROM KPI_TBL
WHERE (((KPI_TBL.Period) = #5/1/2011#) And ((KPI_TBL.RECORD_TYPE) = 0) And ((KPI_TBL.COUNTRY) = "GB") And ((KPI_TBL.Function) = "Average"))
GROUP BY KPI_TBL.MAJOR_COMMODITY, KPI_TBL.KPI_NAME, KPI_TBL.KPI_DESCRIPTION
ORDER BY KPI_TBL.MAJOR_COMMODITY, KPI_TBL.KPI_NAME
PIVOT KPI_TBL.SECTOR;

I get 'Syntax error (missing operator) in query expression 'KPI_TBL.SECTOR UNION ALL....''

Am I asking it to do too much? The intention here is actually to use the SQL in ADO from excel and return a recordset. If needs be I could run two queries and join the recordsets, but if what I am asking to be done is possible then I would rather get it right.

Thanks.

 

[Norie]

Do the UNION then do the pivot/crosstab from that.

 

[Jon von der Heyden]

Sorry but I don't get how? There needs to be two transforms as one has to Average KPI_TBL.RESULT and the other has to Sum KPI_TBL.RESULT.

 

[xenou]

I can run this type of query when they are saved as separate queries:

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2

The same fails when I use the actual SQL from Query1 and Query2.

I think Access can't handle this as raw sql - maybe something to do with how it executes the "Transform" instruction at the top. If you still have problems - create a third query (!):

SELECT * FROM Query3

Where Query3 is the union of Query1 and Query2.

I tend to save a lot of Queries with Access, that I might run as nested queries in SQL Server - Access seems to just perform better with saved queries.

 

[Jon von der Heyden]

Thanks for confirming the same trouble your end XEN; I was worried I just wasn't getting the syntax right. So since I'll be doing this from Excel with ADO, because the intention is to write the results back to a range (CopyFromRecordset), I may as well run the two queries separately and just write the results of the 2nd directly below the 1st. At least I know now it can't be done in one hit.