Calculate percentages based on criteria in report

[Jantzev]

Hello everyone. This is my first post and I think I may be overlooking a simple answer to my complex problem. Here it is:

I created a database to record time logged per workorder for each employee on my job. Each time log has a specific "Trade" attached to it along with a number of hours the employee spent on that workorder. I've created a report to display how much time the selected employee spent on each workorder (within a date range) and now I want to see what percentage of their time was spent on a particular "Trade" (for instance, during September Employee "name" spent "percentage" of their time on Electric, "percentage" on HVAC, "percentage" on Plumbing...[and so on])

I have trades listed in the table and in the time log, the form writes to the trades area of the table (probably very elementary for this discussion) and the report lists the name and grand totals with percentage of total time on each workorder, but does not list any trade information.

How can I add this into my report, preferably at the end (Report Footer?)

Sorry if this is a newbie question. My experience with Access is primarily self-taught and rather recent at that. I'm learning as I fumble through.

Thanks in advance for any assistance or guidance you can give me.

 

[rctjoe24]

It can be achieved multiple ways but I believe you can create a textbox in the form footer and add a control source with something like the following:

=formatPercent(Sum(IIF([Employee] = 'John Smith' and [Trade] = 'Electric',[Hours],0))/ Sum(IIF([Employee] = 'John Smith',[Hours],0)),0)


This will sum the total number of hours for a specific employee 'John Smith' where Trade is 'Electric' and divide it among the total number of hours 'John Smith' has on the report.

For example: 50 hrs is on Electric, he works a total of 200 hours on all trades.

50 / 200 = .25

The FormatPercent function converts the .25 to read "%25". You can include decimals on the reports by changing the last zero in the control source. The number represents the number of digits to display past the decimal.

You can fancy it up by adding more to the control source like this:

="John Smith - '" & [Trade] & "': " & formatPercent(Sum(IIF([Employee] = "John Smith" and [Trade] = 'Electric',[Hours],0))/ Sum(IIF([Employee] = "John Smith",[Hours],0)),2)

This will result

John Smith - 'Electric': 25.00%

Hope this helps.

 

[Jantzev]

rctjoe24,

Awesome! thank you for that. Okay, so that is well on my way to achieving what I want to do. Last question, will that work if the report is based on a generated name? What I mean is that the report I pull is based on the employee name entered. So if I am running the report, I am running it for John Smith only, not everyone (though I suppose I could run one for everyone by selecting no one, but that is another issue). So how would I need to change the code to reflect the specific employee name?

Would/Could I just leave off the "Employee" section of the control source to get the results from the generated report?

Thanks again!!

Jeff

 

[Jantzev]

Yea! Okay, I got it to work and I even figured out that my [Trade] called to the tradeid number, not the name (actually made it easier) and by leaving off the employee name, it calculates for the report! Now to make it look snazzy. Thank you for your help! This is solved!!

Jeff

 

[rctjoe24]

No problem, that's why i love places like this