Crosstab Query

Question

Hello,

I have the following Crosstab query which generates the information below...

TRANSFORM (Nz(Count([qryWOData1].[Order Number]),0)) AS [CountOfOrder Number]
SELECT qryWOData1.GroupSub, qryWOData1.MCMPYear, qryWOData1.MCMPMonth, Count(qryWOData1.[Order Number]) AS [Total Of Order Number]
FROM qryWOData1
WHERE (((qryWOData1.[Main Work Center])="CABNTMKR" Or (qryWOData1.[Main Work Center])="CARPENTY" Or (qryWOData1.[Main Work Center])="ELECTRIC" Or (qryWOData1.[Main Work Center])="ELECTRON" Or (qryWOData1.[Main Work Center])="ELEVSHOP" Or (qryWOData1.[Main Work Center])="GENLABOR" Or (qryWOData1.[Main Work Center])="HEAT" Or (qryWOData1.[Main Work Center])="LOCKSHOP" Or (qryWOData1.[Main Work Center])="MASONRY" Or (qryWOData1.[Main Work Center])="PAINTBTH" Or (qryWOData1.[Main Work Center])="PAINTING" Or (qryWOData1.[Main Work Center])="PICCREW" Or (qryWOData1.[Main Work Center])="PLUMBING" Or (qryWOData1.[Main Work Center])="REFRIGER" Or (qryWOData1.[Main Work Center])="ROOFING" Or (qryWOData1.[Main Work Center])="SHEETMTL" Or (qryWOData1.[Main Work Center])="TOOLCRIB" Or (qryWOData1.[Main Work Center])="WELDING"))
GROUP BY qryWOData1.GroupSub, qryWOData1.MCMPYear, qryWOData1.MCMPMonth
ORDER BY qryWOData1.MCMPYear
PIVOT qryWOData1.OnTime;

I would like to obtain the data identified in the red border utilizing a query. Do I need to create additional queries to obtain this result?

Answer 1

You will need to create a crosstab query which returns COUNT of On Time rows per Main Work Center per month, in which the return value of the TRANSFORM clause is divided by the return value of a subquery correlated with the outer query on Main Work Center, MCMPYear, and MCMPMonth, returning COUNT of all rows, i.e. not restricted to On Time rows. This will return the ratio as a fractional value, which can be shown as percentages by calling the FORMAT function in the TRANSFORM clause.

The following is an example which returns the percentage of sales per SaleType per month for the first half of 2014:

TRANSFORM FORMAT(COUNT(*)
    /(SELECT COUNT(*) FROM Sales AS S2 
      WHERE FORMAT(S2.[SaleDate],"yyyy-mm")
                  =FORMAT(S1.[SaleDate],"yyyy-mm")),"Percent")
SELECT SalesType, FORMAT(SUM(Value),"Currency") AS TotalSales
FROM Sales AS S1
GROUP BY SalesType
PIVOT FORMAT([SaleDate],"yyyy-mm") 
IN("2014-01","2014-02","2014-03","2014-04","2014-05","2014-06");