Results 1 to 3 of 3
  1. #1
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Calculate percentages within a query

    Hi,

    I'm trying to calculate the percentage that each calculated line item in a query represents of the total of all calculated items and I cannot figure out how to calculate the denominator without error. If it helps, here is how I'm calculating the individual line items:



    SELECT DISTINCTROW [Sector Distribution].Category, Sum([Sector Distribution].WeightedExposure) AS SumOfWeightedExposure
    FROM [Sector Distribution], [Sector Denominator]
    GROUP BY [Sector Distribution].Category;

    I now simply wish to sum all of the "WeightedExposure" and then divide each WeightedExposure by the total of all WeightedExposure. I would think this would be relatively easy to do but I'm a beginner who started working with Access less than two weeks ago. Any help would be greatly appreciated. Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make 2 queries,
    Q1, get the totals of the ID.
    Q2, join Q1 and your data table, sum the parts, and calc Pct.
    id, sum(field), sum(field)/Q1.TotalOfFld

  3. #3
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Thank you. So I've done that...had to use more than two actually...but now I have a final query that brings in numerators, which I can see are correct by line item, and denominator on each line, which I can also see is correct, but when I try dividing one by the other, I just get blanks in that final column. Here is the current SQL:

    SELECT [Sector Numerator].Category, [Sector Numerator].SumOfWeightedExposure, [Sector Denominator].SumOfWeightedExposure, [Sector Numerator]![SumOfWeightedExposure]/[Sector Denominator]![SumOfWeightedExposure] AS Sector
    FROM (Sector INNER JOIN [Sector Numerator] ON Sector.Category = [Sector Numerator].Category) INNER JOIN [Sector Denominator] ON Sector.Period = [Sector Denominator].Period
    GROUP BY [Sector Numerator].Category, [Sector Numerator].SumOfWeightedExposure, [Sector Denominator].SumOfWeightedExposure;

    I don't receive any errors, which is why I am puzzled. Any further thoughts?

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create query to show percentages.
    By Littlewood in forum Access
    Replies: 2
    Last Post: 06-23-2015, 07:12 AM
  2. query that generates percentages
    By jppevy in forum Access
    Replies: 3
    Last Post: 07-30-2013, 10:44 PM
  3. Percentages Query
    By mabrown81 in forum Queries
    Replies: 12
    Last Post: 11-01-2012, 12:50 PM
  4. Replies: 7
    Last Post: 05-06-2012, 12:20 PM
  5. Replies: 1
    Last Post: 03-04-2012, 11:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums