Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Percentage on Total Query

Query Problem

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

Let us look into an example. Northwind Traders 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 Values on Total Sales Value of all Customers and they need only the Top 10 Customer's 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.

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

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

Featured Post

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com pa...

Labels

Blog Archive

Recent Posts