Results 1 to 8 of 8
  1. #1
    reynier09 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    17

    Getting percentage report


    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

  2. #2
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    reynier09 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    17
    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.

  4. #4
    reynier09 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    17
    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.

  5. #5
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.

  6. #6
    reynier09 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    17
    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

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  8. #8
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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

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

Similar Threads

  1. Replies: 3
    Last Post: 12-10-2013, 12:55 PM
  2. how to format percentage in access report
    By mdnikki in forum Reports
    Replies: 2
    Last Post: 02-11-2013, 01:20 PM
  3. Replies: 1
    Last Post: 01-11-2013, 06:01 PM
  4. Creating a percentage of total records report
    By sai_rlaf in forum Reports
    Replies: 3
    Last Post: 08-12-2011, 11:39 AM
  5. Use report to Calculate sum and percentage
    By bangemd in forum Reports
    Replies: 3
    Last Post: 05-28-2009, 12:01 PM

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