Search code examples
ms-access

New column to calculate percentage

Need to add a column with percentage for each "adh_classi" by "stop_code" ex.

"Stop_code" Count adh_Classi
10013 32 Early
10013 101 Late
10013 317 On-Time

Total for 10013 = 450

Early-> 7.11% (32/450)
Late -> 22.44% (101/450)

I do not have much Access experience

Solution

  • Accomplishing in a query requires an aggregate subquery or DSum() aggregate function to calculate the total for each class.

    SELECT Stop_Code, 100 * Count / (SELECT Sum(Count) AS SumCnt FROM tablename AS Q1 
WHERE Q1.Stop_Code = tablename.Stop_Code) AS Pct FROM tablename;

    or

    SELECT tablename.Stop_Code, 100 * Count / SumCnt AS Pct 
FROM tablename 
INNER JOIN (SELECT Stop_Code, Sum(Count) AS SumCnt FROM tablename 
            GROUP BY Stop_Code) AS Q1 
ON tablename.Stop_Code = Q1.Stop_Code;

    or

    SELECT Stop_Code, 100 * Count / DSum("Count", "tablename", "Stop_Code=" & [Stop_Code]) AS Pct 
FROM tablename

    Domain aggregate function causes slower performance in large dataset.

    Another approach is to build a report that uses Grouping & Sorting design and aggregate function calc in textbox of group footer: =Sum([Count]) . Expression in detail section would reference footer textbox: =100 * [Count] / [tbxSubTotal].