Percentage on Total Query ~ LEARN MS-ACCESS TIPS AND TRICKS

Calculating Line Value Percentage on Total.

How do we calculate the percentage of individual value on each row based on the Total Value of all rows?

Let us look into an example. Northwind Traders Customer-wise Order-wise Sales Figures are kept in a Table. Management would like to know the Customer-wise Sales Summary Figures. The Management has given further instructions that they would like to know the Percentage of each Customer's Sales Values on the Total Sales Value of all Customers and they need only the Top 10 Customers' with the highest Sales Values.

We will prepare a Total Query and we are through with the Problem. Import the Tables Orders and Customers from the NorthWind.mdb sample Database. If you are not sure about the location of the sample database on your machine visit the Page Saving Data on Forms not in Table for location references.

The Query SQL

We need only the Orders Table now. Customers Table is being used as the Source Table for a Combo box in the Orders Table to enter CustomerID into the Table. If we don't bring in the Customer Table also then an error message will pop up when we use the Orders Table in our Query.

Open a new Query in your Project. Copy and paste the following SQL String into the Query's SQL editing window:

SELECT TOP 10 Orders.CustomerID,
   Sum(Orders.Freight) AS Sales,
   DSum("freight","orders") AS Total,
   Sum([freight]/DSum("freight","orders")) AS Percentage
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY Sum(Orders.Freight) DESC;

Change the Query View into the normal design view (View -> Design View) to have a better look at the Column-wise structure.
We have grouped the Values on CustomerIDs, summing up the Customer-wise Freight Values, and changed the Column Name: Sales, for example-purposes.
The third column finds the Freight Value Total of all the records in the Orders Table with the DSum() Function. Notice that we have used the word Expression on the Total Row instead of Sum.
We have to Repeat the Sum() and DSum() Functions in the next Column formula -Percentage: Sum([Freight])/DSum("Freight", "Orders") ' to calculate the Percentage of Sales on Total Sales Value in the Table. Even though we have calculated the Customer-wise Freight Values and renamed the field as Sales and Total Freight Values as Total in the second and third columns respectively, these two names we cannot use in the Percentage calculating expression in the form Sales/Total. The Total Query will not allow this. If you find the percentage calculating formula too complicated then you save this Query first without the percentage column. Open another Query that uses the earlier one as a source and write the formula Percentage: Sales/Total for the new column and here you will not have any problem.
To format the calculated column with Percentage, click anywhere on the Column, display the Property Sheet (View -> Properties.) and select Percentage in the Format Property and enter 2 in the Decimal Places Property.

Listing the Top 10 highest Percentage Records.
To limit the output of the Query to 10 Records with the highest Sales values we have to change the Top Values Property of the Query. Click on an empty area somewhere near the Table above.
Display the Property Sheet (View -> Properties), if you have closed it.
Change the Property Top Values to the desired value. Currently, it will be 10, because we have mentioned SELECT TOP 10 in the SQL String that you have pasted into the SQL Window.
Open the Query in Normal Datasheet View and check the Result. We have already set the Sorting order to Descending under the Sales Column so that the Highest Sales Value and Percentage will be at the top Row.

Earlier Post Link References:

Vishnu VikramFebruary 13, 2010 at 8:43 PM
nice article to can you send the demo version of this article to the following adress.

gntlas@gmail.com
Wordpress ThemesAugust 10, 2010 at 8:41 AM
Amiable post and this mail helped me alot in my college assignement. Gratefulness you on your information.
Chris BuchananAugust 30, 2011 at 4:19 AM
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.
ChrisJBuchananAugust 30, 2011 at 5:11 AM
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?
a.p.r.pillaiAugust 30, 2011 at 5:56 AM
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.