Results 1 to 2 of 2
2012-11-11, 19:32 #1
- Join Date
- Feb 2001
- Thanked 2 Times in 2 Posts
Summing columns and calculating percentages
I have a total query that includes a couple of fields to group sales orders by quantity and value: CountOfOrderNo and SumOf OrderValue. I need to show these as percentages of their totals. Curently, I am doing this in a report based on the query: [CountOfOrderNo]/Sum([CountOfOrderNo]) and [SumOfOrderValue]/Sum([SumOfOrderValue]), which works fine.
Is it possible to calculate the percentage fields in the query itself rather than in the report?
2012-11-26, 23:48 #2
- Join Date
- Aug 2002
- Adelaide, South Australia, Australia
- Thanked 5 Times in 5 Posts
You could use Domain functions, although the larger your dataset the slower this could become.
Using a very simple table (Table1) with three fields (ID, strText, dblNumber), I created a query using the following SQL (I have split up the lines for easier reading):
Count(Table1.ID) AS CountOfID,
Count([Table1].[ID])/DCount("[ID]","Table1") AS PCOfID,
Sum(Table1.dblNumber) AS SumOfdblNumber,
Sum([Table1].[dblNumber])/DSum("[dblNumber]","Table1") AS PCOfdblNumber
GROUP BY Table1.strText;
If you populate the table with a few numbers, you should be able to see the percentages displayed.
Of course, you can use cleaner names for the calculated fields, but I've left them fairly generic for easy interpretation.
Hope this helps.
The Following User Says Thank You to alifrog For This Useful Post: