Getting percentage report

[reynier09]

Hi, I'm new to Access and I need some help. I have an employees table where one of the fields is yes/no field for trained or not employees. I'm trying to create a report, form o query that obtains the total percentage of trained employees by department. What I was doing is assign a 1 to yes and multiply those by 100 and average that column in a total row. That gives me value but not in the format I would like. I would like to to pull a report, form o query that just displays that percentage by department. How to do this? Is there a way to just reference the cell where the percentage in the total row is displayed in other report? Thanks

[Micron]

Is there a way to just reference the cell

Cells are for spreadsheets. Controls are for forms. I'm not sure but I think you mean to ask if you can reference a form control. Yes, but there's no need to make it complicated. I don't know what you mean by "I would like to to pull a report, form o query..."
If you want to display calculated controls in a report, the report can handle that nicely - no fancy form or query stuff needed. If you'd rather see the calculation in a query and base a report on that, create a Totals query to calculate a percentage by Grouping By on a field such as Dept. More than one grouping is possible, but sometimes negatively affects the desired outcome.

[reynier09]

Thanks, what I mean by a pull is for example go to a TrainedEmployees report and just display for example:
Department Trained employees Percentage
Manufacturing 50%
Production 40%

Something like this, I appreciate the help I've being on Access only for a day and I'm still learning.

[reynier09]

Got it, thanks for the help. I just created a query and added the total row where I added the fields needed to calculate percentage and then created another query with those fields and calculated the percentage in a different field.

[Micron]

why not just calculate the percentage in the totals query (or on the report)?
Whatever you do, know that it is considered bad practice to store calculated values in 99.95% of cases (rough guess).
Anyway, if you're happy with your solution, please mark this thread a solved.
Thanks.

[reynier09]

so how would i do that? could you provide the steps?. my solution works but i know it has its limits thus anything better i'm more than willing to learn.why is it a bad idea to store the results? thanks again

[orange]

It appears that you are new to Access and to Database.

See the links in this post to learn more about database and design.
Good luck.

[Micron]

check out the posted links and see what you think and what might help because what you ask is not something that is conducive to a forum like this. They are for helping and teaching a bit, but not really from ground zero. It's easy enough for most to learn, but it takes a bit of time. You can pick up a lot from something in a posted answer by doing some research. If I say use a Totals query, then you need to Google that rather than ask for what amounts to a tutorial. There's TONS of video and other forms of tutorials, so it makes little sense to recreate the wheel here.

As for storing the results in a table, forms and reports are for displaying calculated data. Reasons for not doing so: basically, if one of the variables changes, all calculated results usually need to change as well, but any stored value will remain stagnant. This is usually desired, but again, not 100% of the time. Even a unit price times quantity calculation should not be stored. To do so would require the calculated value to be recalculated if the quantity or quoted price were to change. Such updates may fail to apply due to poorly written queries or hiccups in code execution. In unsecured databases, data could be incorrectly edited by someone, thereby making the calculation incorrect.

Here is more on the subject (from a place you can learn A LOT of useful information in your quest for knowledge)
http://allenbrowne.com/casu-14.html