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

Calculating Line Value Percentage on Total.

To calculate the percentage contribution of each value in a row based on the total of all rows, let’s work through an example.

Assume that Northwind Traders stores customer-wise, order-wise sales figures in a table. Management now wants a customer-wise sales summary and has further requested the following:
Top 10 Customers Only – Display only those customers with the highest sales values.

We can solve this easily with a Total Query.

Steps:

Import the Orders and Customers tables from the Northwind.mdb sample database.
- If you’re unsure where the sample database is stored on your machine, refer to the instructions on the Saving Data on Forms Not in Table page for location details.
Create a Total Query to sum the sales per customer.
Calculate each customer’s percentage by dividing their sales value by the total sales of all customers.
Apply a sort to display results in descending order and limit the output to the Top 10 customers.

With that, you’ll have a clear view of the top customers and their share of the total sales.

The Query's SQL Statement.

For our task, we only need the Orders table. The Customers table serves as the source for a combo box in the Orders table, allowing us to enter the CustomerID. If we omit the Customers table, an error message will appear when we use the Orders table in our query.

To proceed, open the SQL (Query) editing window in your project (without selecting any table or query). Then, copy the following SQL statement and paste it into the 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;

Switch the query from SQL View to Design View (View → Design View) to see the layout visually.
We have grouped the records by CustomerID, summed the Freight values for each customer, and renamed this column Sales.
The third column calculates the Total Freight Value for all records in the Orders table using the DSum() function. Note that in the Total row, we have used Expression instead of Sum.
To calculate the percentage of each customer’s sales against the total sales, we repeat the Sum() and DSum() functions in the formula:
```
css
Percentage: Sum([Freight]) / DSum("Freight", "Orders")
```
Even though we have already calculated Sales (customer-wise total) and Total (overall freight value) in earlier columns, we cannot simply use 'Sales / Total' In this query. Total Queries do not allow referencing calculated field names directly in the same query.
If you find the percentage formula too cumbersome, you can save this query without the Percentage column, then create a second query using the first one as its source. In the second query, you can simply write:
```
makefile
Percentage: Sales / Total
```
This time, it will work without issue.

Finally, to format the Percentage column, click anywhere in the column, open the Property Sheet (View → Properties), set Format to Percentage, and set Decimal Places to 2.
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.