Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Percentage on Total Query

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.

  1. 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;

  2. Change the Query View into the normal design view (View -> Design View) to have a better look at the Column-wise structure.

  3. We have grouped the Values on CustomerIDs, summing up the Customer-wise Freight Values, and changed the Column Name: Sales, for example-purposes.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. Display the Property Sheet (View -> Properties), if you have closed it.

  9. 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.

  10. 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:

Share:

5 comments:

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

    gntlas@gmail.com

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

    ReplyDelete
  3. 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.

    ReplyDelete
  4. 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?

    ReplyDelete
  5. 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.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code