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

Query Problem

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

Let us look into an example. NorthWind Trader's Customer-wise Order-wise Sales Figures are kept in a Table. The 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 Value on Total Sales Value of all Customers and need only the Top 10 Customers with 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.

We need only the Orders Table now. Customers Table is being used as 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 messages will popup 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 to Sales, for example purposes.

The third column finds the Freight Value Total of all the records in the Orders Table with the DSum() Function. Note 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 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 using the earlier one as 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 to show with Percentage symbol, 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.

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.