Post reply

Calculating a percentage within a group in a query

  • November 29, 2008 at 9:15 pm

    #73265

    Hi,

    Is it possible for Access to calculate a percentage as a total per group for a record. I have a dataset (from timecards) and I need to be able to calculate a records percentage per group. An example of the data is (with the Pct being the piece I need to calculate):

    Name Unit Project Task Hours Pct

    John Doe 1 1000 1000 20 25%

    John Doe 1 2000 1050 20 25%

    John Doe 3 5000 2050 20 25%

    John Doe 1 1000 2050 20 25%

    Jane Doe 3 2000 1000 30 37.5%

    Jane Doe 1 1000 1050 20 25%

    Jane Doe 2 5000 2050 30 37.5%

    So both work 80hrs, but the hrs are split between different combinations, so I need access to calculate the % per combination as a total per person.

    Is this possible to work out in a query?

    Many thanks.

  • December 1, 2008 at 3:48 am

    #905445

    Hi

    From your data example I'm not quite sure what I should recommend you.

    Do you need to aggregate the hours worked on a project per person as well or do you have this total number already? How good is your knowledge of the MS Access Query Builder?

    If you have to aggregate the hours I would recommend you to use a function who does that for you and gives you the result back. You can select this function through the expression builder and pass on the values per group.

    If you get the values as in your example it is just a matter of calculating the percentage, right?

    Result: =Hours*100/80

    I have uploaded an example including two sample queries. The function expects name, project and task and returns the percentage. The total hours are calculated in the function. Ok, I know the query itself gives me the total hours already but I just wanted to show you the principle.

    Hope this helps.

    Rene

  • December 1, 2008 at 4:04 am

    #905455

    James

    If unit,project and task is the unic combination per persom

    Try

    SELECT a.name, a.unit & a.project & a.task AS comb, Sum(a.Hours)/(select sum(hours) from test b where b.name=a.name) * 100 AS percentage

    FROM test AS a

    GROUP BY a.name, a.unit & a.project & a.task

    Table test

    Name text

    unit text

    project text

    task text

    Hours number

    Gosta

  • December 1, 2008 at 9:30 am

    #905715

    Thanks Rene.

    What I should have also mentioned is that the total hours also vary (not always 80hrs). I like the idea of the function and it might be something that I use further on down the line of this project.

    I've adapted Gosta's suggestion below that seems to do the trick.

    Thanks for your input.

  • December 1, 2008 at 9:34 am

    #905720

    Gosta,

    Thanks - this works....I changed it slightly, but it gets me the results I am expecting.

    Thanks for the input!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply