Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013
    Posts
    1

    Unanswered: Calculating in Select Query?

    Hello, I'm pretty new to access and I'm having some issues with queries and calculating.
    I have a database that houses the accuracy and production for each individual employee. I need to calculate the production accuracy for the whole department not just individuals.

    In this instance I need the monthly accuracy for the department. I have a table with the information listed.
    Table1 Fields:
    Employee ID
    Review
    Reject
    Month Ending
    Quarter Ending
    Year Ending
    Quality

    My select query works just fine and pulls the info I need. It pulls the sum of the "Review" and the sum of the "Reject". Here is my select query:

    SELECT Sum(Table1.Review) AS SumOfReview, Sum(Table1.Reject) AS SumOfReject, Table1.[Month Ending]
    FROM Table1
    GROUP BY Table1.[Month Ending]
    HAVING (((Table1.[Month Ending])=[Month Ending Date]));

    Now I need to take those sums do a calculation to get the accuracy.
    (1-([Reject]/[Review]))

    Can I do this all together in one query? Or do I need to append this to a new table and run an update query to calculate the accuracy onto my the new table?

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Any reason why you cant add (1-([Reject]/[Review])) to your existing qry?

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by sjkuffel View Post
    Can I do this all together in one query?
    Sure!

    Just be careful when doing the accuracy calculation that you cast the rejects and reviewed as decimal or numeric or real so that you are not dividing integers.

    Code:
    select SUM(reject)  Reject, 
           SUM(review) Review, 
           1 - cast(sum(Reject) as real) / cast(sum(Review) as real) Accuracy,
           month_ending
      from @emps
      group by Month_Ending

Tags for this Thread

Posting Permissions

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