Additional Calculations in Crosstab Query

Flinn, Randal J 281 Reputation points
2022-05-04T12:09:37.867+00:00

I have the following Crosstab query.

TRANSFORM Sum(qryBldg6Hrs.[Total quantity]) AS [SumOfTotal quantity]
SELECT qryBldg6Hrs.[Cost element name], qryBldg6Hrs.[Fiscal Year], Sum(qryBldg6Hrs.[Total quantity]) AS [Total Of Total quantity]
FROM qryBldg6Hrs
GROUP BY qryBldg6Hrs.[Cost element name], qryBldg6Hrs.[Fiscal Year]
PIVOT qryBldg6Hrs.[Order Type];

Where A, B, C, D, E and F are [Order Types], I would like to add two columns (Reactive & Not Reactive) that calculate subtotals based on the following...

For each FY value:
Reactive = Order Types A+B+F
Not Reactive = Order Types C+D+E

How do I add that request in the Crosstab query?

198748-image.png

Microsoft 365 and Office Access Development
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.