Access Query or SQL Question

Question

Hello,

I have the following query which generates the results below...

TRANSFORM Count(qryWOData1.[Order Number]) AS [CountOfOrder Number]
SELECT qryWOData1.[Main Work Center], 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") AND ((qryWOData1.[MCMP Date])>=#7/1/2021#))
GROUP BY qryWOData1.[Main Work Center], qryWOData1.[Main Work Center]
PIVOT Format([MCMP Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

What I am trying to identify is a way to group the groups identified with the colored numbers.

I could create a query that fills a field based on the Main Work center value (e.g., CABNTMKR, CARPENTY, LOCKSHOP, SHEETMTL, WELDING = 1, etc.,) but didn't know if there was a more efficient method?

Answer 1

It appears you have a single row for each center. Therefore add a column containing the group number for each center. Then you can group by on that column to produce your rollup.

Main Work Center GroupNumber Jul Aug
-------------------------------------
CABNTMKR              1
CARPENTRY              1

SELECT GroupNumber, SUM(Jul) As Jul, SUM(Aug) As Aug
FROM qryWOData1
...
GROUP BY GroupNumber

If you need this grouping in other places then create a separate table to store the center and group numbers. Then join the tables together to get the group numbers for your grouped results when you need them.