question

RJF-4684 avatar image
0 Votes"
RJF-4684 asked thedbguy commented

Access Crosstab Query

I have the following crosstab query in Access. Is there a way to calculate the percentages for each Order Type by Fiscal Year (i.e., 2018, 2019, etc.)?

Example: Order Type "18" for Fiscal Year 2018 = 21,973.55. This would be 23.2% of 2018 Total or (21,973.55/94,699.85).

95421-crosstab.jpg


Thanks

office-access-dev
crosstab.jpg (90.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered thedbguy commented

Hi. Where did the value 94,699.85 come from? I don't see it in the crosstab image.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello,

That is the sum total of the 2018 column. It should be displayed at the bottom.

Thanks

0 Votes 0 ·

Hi. Sorry, I missed that while viewing the image on my phone earlier. Have you considered creating another query based on your crosstab query to do the percentage calculation?

0 Votes 0 ·
KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered KenSheridan-7466 commented

In a conventional aggregating query that would be done by dividing the SUM per order type per accounting year by the result of a correlated subquery which returns the SUM for the accounting year currently returned by the outer query. However, I don't believe that it is possible to do this in the TRANSFORM clause of a crosstab query.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the response. Woukd ut be possible ke to sum the data with a crosstalk? I suppose sum the year 2018 for each Order Type.

0 Votes 0 ·

Isn't that what your current query already does? The SUM operator would be called in the TRANSFORM clause, the query would be GROUPed BY order type, and PIVOTed by accounting year.

0 Votes 0 ·