Calc Percents

[JoeBProducts]

Hi All! I am stuck. I need to calculate percents from a Countof Column in a query. I don't know how to: 1. Sum to get the total. 2. Calculate the percent. I have a few different catagories in one query, and would like to develop % for each one. Should I split them up before calculating?

 

[Russell Hauf]

Hi Joe, and welcome.

Can you give an example of your data, and of what your current query is returning?

Thank you,

Russell

 

[JoeBProducts]

Type State MCO Term CountOfTerm
NB 4B 19 003 1263
NB 4B 19 006 8412
NB 4B 19 012 24087
NB 4B 19 999 3
NB 4P 21 012 530
RB 4B 19 003 2235
RB 4B 19 006 4362
RB 4B 19 012 3037
RB 4P 21 012 690





Thank you for you help. In the above chart, I have the counts for each Term by MCO, by Business Type. I would like to develop percentages for each.
Example:

BUS MCO Term %
NB 19 3 25%
NB 19 6 25%
NB 19 12 50%

RB 19 3 25%
RB 19 6 25%
RB 19 12 50%

NB 21 12 100%
RB 21 12 100%

I have many queries to do this for. Should I be splitting the business up in different Queries first?

Thanks!

 

[Russell Hauf]

Ok, sorry I haven't gotten back to you - I've been a bit busy.

Select your first 4 columns (I'll call them like you did at the bottom of your example: BUS, MCO, TERM, CNT. Ok, I made up CNT, but you get the idea) in a new query. Click on the Totals button (the sigma), or go to View-Totals.

"Group By" should come up under each field. Leave them be. In the 5th column, add something like this (I've named your table tbl020703):

PCT: FORMAT(([CNT]/(SELECT SUM([CNT]) FROM tbl020703 AS X WHERE X.BUS=tbl020703.BUS AND X.MCO=tbl020703.MCO)),"0.00%")

The as X part is just an alias for tbl020703. Change your table and field names as necessary (but not X).

HTH,

Russell