This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 1%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERF%3C/text%3E%3C/svg%3E)
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).
Thanks
Hi. Where did the value 94,699.85 come from? I don't see it in the crosstab image.
Hello,
That is the sum total of the 2018 column. It should be displayed at the bottom.
Thanks
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?
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.
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.
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.