Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    24

    Unanswered: Query, Counting multiple criteria from a combobox

    Hi,

    I'm currently working on an Access database for work purposes and I'm having difficulty figuring something out.

    Background info
    The database is being used to monitor the employees quality of work, so for each advisor there could have been monitored multiple times within a month, each monitoring record resulting in a final grade (Green, Amber, Red)

    Ideally I'd like a league table, whereby you enter the date range (e.g. we'd produce a League table for each month) that counts the grades, works out a competency percentage and then lists them with the best staff at the top.

    I have 2 tables
    -Employees (list of personnel)
    -Grades (Green,Amber,Red)
    -Monitoring Records (info on how the advisor performed with a final assigned grade from the Grades table)


    The date range stuff I should be fine with, but the counting of the combo box results is proving difficult. Not least because some employees dont have Red records, so doing a count query cuts it off, but I need it listed for the report in full. Plus when I try and Count the criteria in one query it doesnt work (i think because groupby is working against what i am trying to do)

    Ok, so I need a query to do the following:
    Count Greens, Count Ambers, Count Reds, Count Total Records, Green Count Total DIVIDED by Total Records = Competency Percentage

    | Green | Amber | Red | Total Calls | Competency Percentage
    Employee1 - 8 1 0 9 88%
    Employee2 - 7 0 2 9 77%
    Employee3 - 10 3 0 13 76%

    I'm not sure if this is even possible to do in one query, but if i do need to split it into multiple querys could someone perhaps talk me through how to do this because i've never had to merge queries before

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Perilous View Post
    I have 2 tables
    -Employees (list of personnel)
    -Grades (Green,Amber,Red)
    -Monitoring Records (info on how the advisor performed with a final assigned grade from the Grades table)
    That makes three tables, not two.

    Quote Originally Posted by Perilous View Post
    Ok, so I need a query to do the following:
    Count Greens, Count Ambers, Count Reds, Count Total Records, Green Count Total DIVIDED by Total Records = Competency Percentage

    | Green | Amber | Red | Total Calls | Competency Percentage
    Employee1 - 8 1 0 9 88%
    Employee2 - 7 0 2 9 77%
    Employee3 - 10 3 0 13 76%

    I'm not sure if this is even possible to do in one query, but if i do need to split it into multiple querys could someone perhaps talk me through how to do this because i've never had to merge queries before
    I'm not sure that dividing [Green Count Total] by [Total Records] will yield a Competency indicator, but there is nothing that prevents from using a query here (there seldom is). Here's an example:
    Code:
    SELECT EmployeeGrades.EmployeeID, 
           Sum(EmployeeGrades.Green) AS SumOfGreen, 
           Sum(EmployeeGrades.Amber) AS SumOfAmber, 
           Sum(EmployeeGrades.Red) AS SumOfRed,
           Count(EmployeeGrades.EmployeeID) AS Total,
           Sum(EmployeeGrades.Green)/Count(EmployeeGrades.EmployeeID) As Competency
    FROM   EmployeeGrades
    GROUP BY EmployeeGrades.EmployeeID;
    If you need to add some criteria to filter the records, remember that WHERE applies the criteria BEFORE the grouping, while HAVING applies the criteria AFTER the grouping. In other words, WHERE concerns all rows into the table, while HAVING concerns each group of rows.
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    24
    Thank you so much for the help, with your help and a little bit of tinkering it looks like it's working! The only problem now is the Sum totals are all listed as negative values, is there a way to fix this?

    Employee1 -8 -1 0 9 88%


    Edit
    Nevermind- I found the solution. Thanks again
    Last edited by Perilous; 05-20-12 at 05:50.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •