wordpress statistics
  • Today is Wednesday, October 30, 2013

5 Responses to “Percentage on Total Query”

  1. nice article to can you send the demo version of this article to the following adress.

    gntlas@gmail.com

  2. Amiable post and this mail helped me alot in my college assignement. Gratefulness you on your information.

  3. Chris Buchanan says:

    Thanks for this, but it only works on Northwind.mdb. In the later versions, they’ve changed the field names. I’d be happy to send a working version of the mdb file if anyone wants it.

  4. ChrisJBuchanan says:

    My version of your solution is set out below, and works well.
    SELECT [Appointment Records].Result, Count([Appointment Records].ID) AS CountOfID, Sum([ID]/DSum(“ID”,”Appointment Records”)) AS Percentage
    FROM [Appointment Records]
    GROUP BY [Appointment Records].Result
    HAVING ((([Appointment Records].Result) Is Not Null))
    ORDER BY Count([Appointment Records].ID) DESC;

    However, my Results field has a number of blank fields, so the percentages (of the total ID field) are somewhat misleading. Is there a way of correcting this?

  5. a.p.r.pillai says:

    The DSum() function in the expression Sum([ID])/DSum(“ID”,”Appointment Records”) AS Percentage sums-up the ID values directly from the ‘Appointment Records’ Table, inclusive of the records with the Result field value Null cases.

    Try after modifying DSum(“ID”,”Appointment Records”) in the above expression as Sum([ID])/DSum(“ID”,”Appointment Records”,”Result > 0″) AS Percentage.

    In your SQL above, the expression have a parenthesis wrongly placed at the end. The syntax should be: Sum(x) / DSum(x,y,z) As NewName rather than Sum(x/Dsum(x,y,z)) As NewName.

Leave a Reply

You must be logged in to post a comment.