Results 1 to 8 of 8
  1. #1
    zephaneas is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2008
    Posts
    15

    How To Compute Percentages

    I have these tables:
    http://www.maroisconsulting.com/temp/query.png



    An employee has a title, and optionally a title could represent a manager positon. An employee can work at one or more stores, and each store in in only one corporate group.

    I need to get back a result set that shows the percentage of employees who are managers who have a date in the Certified field. It needs to be grouped by Group.

    Right now there are 4 groups, so my result set should look like:

    Code:
     
    Group A   11
    Group B   32
    Group C   28
    Group D   15
    Here's what I have so far, but it doesn't account for the IsManager field in the Titles table, and I can't seem to get the Titles table int the query correctly.

    Code:
     
    SELECT d.GroupId, Sum(d.cert_complete) AS SumOfcert_complete, Count
    (d.cert_complete) AS CountOfcert_complete
    FROM (SELECT DISTINCT s.GroupId, e.EmployeeID, IIf(e.Certified Is 
    Null,0,1) AS cert_complete FROM (Stores AS s INNER JOIN EmployeeStores 
    AS es ON s.StoreId=es.StoreId) INNER JOIN Employees AS e ON 
    es.EmployeeId=e.EmployeeID)  AS d INNER JOIN Groups ON 
    d.GroupId=Groups.GroupId
    GROUP BY d.GroupId;
    and

    Code:
     
    SELECT q.GroupId, (SumOfcert_complete/CountOfcert_complete)*100 AS 
    percent_certified, Groups.GroupName
    FROM qryGroupCert_base AS q INNER JOIN Groups ON 
    q.GroupId=Groups.GroupId;
    Like I said, these 2 queries don't account for only those employees who are managers (Titles.IsManager), so the result set it wrong.

    I could use a bit of help on this one.

    Thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    is "manager" one of your Titles? If so, get rid of your isManager record completely and query off of the WHERE TitleID = (whatever the "manager" ID is). If it's not, consider doing so.

  3. #3
    zephaneas is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2008
    Posts
    15
    I don't agree with that at all.

    First, there are different types of managers.

    Second, it requires a query to use a user-editable string. What if the user spells it wrong. Or later changes it? Or what if they want to use something like "Shift Leader" and not have the word "Manager" in the title at all? In all these cases the percentage would be wrong because of a difference in spelling.

    Removing the IsManager column adds nothing to the solution here.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The user shouldn't be allowed to make that error. If you are providing the user with a textbox where they enter in a title to assign or to query, you've defeated the purpose of your Titles table and the relationships to the other tables. Rather, provide the user with a combobox or listbox that contains the available Titles to assign or query off of. This way you completely avoid any typographical errors and multiple "nicknames" for the same title.

    Further, it should not be up to the user to decide if they want to use "shift leader" or "supervisor" or "manager" or anything else. That alone will lead to massive headaches. Imagine later on someone asking for a list of all "shift leaders." The reporting person would then have to clarify that what that specific user calls a "shift leader" is called about 20 other things depending on who you ask. I am currently dealing with this same issue at my job. Allowing users to manually enter in a queryable field is an absolute nightmare.

  5. #5
    zephaneas is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2008
    Posts
    15
    What you're saying doesn't even make sense.

    The user shouldn't be allowed to make that error. If you are providing the user with a textbox where they enter in a title to assign or to query, you've defeated the purpose of your Titles table and the relationships to the other tables.

    That's exactly why there's a checkbox bound to the IsManager field - so that it is irrelevant wat the user calls it. The Boolean field allows a query to run regardless of the verbage.


    Rather, provide the user with a combobox or listbox that contains the available Titles to assign or query off of. This way you completely avoid any typographical errors and multiple "nicknames" for the same title.

    Who or what adds or removes titles from the table bound to the combo? This is why there is Titles screen. The ability to add new titles later, or to rename a title, is a client requirement. There is a Titles combo on the Employee screen, but users can add/edit titles anytime from the Titles screen.

    Further, it should not be up to the user to decide if they want to use "shift leader" or "supervisor" or "manager" or anything else.

    Says who? Again, this is a client requirement and is a typical data entry application feature.

    That alone will lead to massive headaches. Imagine later on someone asking for a list of all "shift leaders." The reporting person would then have to clarify that what that specific user calls a "shift leader" is called about 20 other things depending on who you ask.

    I agree that the potental exists to have different verbage for same title, but that's a fact of life in any database app.

    Again, none of thisreally has any bearing on my query.

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The bearing on your query would be the removal of the field that is causing the trouble and make the grouping and percentage counting easier.

    From my experience, employee titles are agreed upon and added to the db during the design phase. A change to employee titles would be an admin-only modification. Further, if multiple titles can be designated as managerial titles, that would create a TitleType table which would designate managerial and non managerial TitleIDs. If the client requirement is what it is, then none of what I said applies.

    Give this a go:
    1. run a query for all employees who have an Is Not Null certified date as well as their isManager status and group ID
    2. using the above run a query that pulls Group, Count(EmployeeID) WHERE isManager = -1
    3. You now have the Groups and counts of the ones that are managers, as well as the total employees in each group. The percentage should be pretty straightforward from here.

    Hope that helps at least a little. If not, my apologies.

  7. #7
    zephaneas is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2008
    Posts
    15
    The problem I'm having and the reason I posted is that I don't understand how to join the table. I'm not very good at Access. MS SQL's Join syntax is more straitforward. This nested join syntax is bizzare.

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The image of the design view looks like the joins are correct. Assuming all join fields are non-nulls and their relationships have been set, you should be good to go.. Just bring down the fields you want. If you save the query as a select query, you can then bring it into another query and treat it as if it was a table. That way you dont have to worry about the nested queries and nested joins. Just let the design view take care of it for you.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-13-2011, 07:24 AM
  2. Percentages in reports for group rows
    By John Donovan in forum Reports
    Replies: 1
    Last Post: 12-28-2010, 09:10 AM
  3. Averaging Percentages in a form
    By DICKBUTTONS in forum Access
    Replies: 1
    Last Post: 11-18-2010, 01:22 PM
  4. Replies: 2
    Last Post: 08-03-2010, 02:47 PM
  5. Graph problems (displaying percentages)
    By bingobong62 in forum Access
    Replies: 0
    Last Post: 09-24-2009, 07:50 AM

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