Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    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?

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    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):

    SELECT
    Table1.strText,
    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
    FROM Table1
    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.

    Cheers

    AliC

  3. The Following User Says Thank You to alifrog For This Useful Post:

    Murgatroyd (2012-12-13)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •