none
Query question RRS feed

  • Question

  • I have a question about doing a specific type of query. I have built a database for a manager in which he will track employees and errors commited while performing their job. The database is a simple one with 3 columns. It has the date, the name, and the error committed. I wrote him a web application to populate the database and to run a report. He inputs a date range and selects the employee name from a drop down and the report returns all the records for that employee within that date range. What he would like to have instead of seeing the employee listed many times with the same error he would like to see The name listed once for each error and a count behind it. For example right now he gets this.

     

    DATE         NAME           ERROR

    1/1/07       Jones, Bob    oops

    1/1/07       Jones, Bob    oops

    1/4/07       Jones, Bob    ID10T

    1/5/07       Jones, Bob    ID10T

    1/10/07    Jones,Bob      oops

     

    What he would prefer would be to see something like this.

     

    NAME           ERROR         Count

    Jones, Bob      oops           3

    Jones, Bob      ID10T         2

     

    I don't even know where to begin to write a query to accomplish this. Can anyone help???

    Tuesday, May 1, 2007 6:05 PM

Answers

All replies

  • SELECT Name, Error, Count(*)  AS Count

    FROM MyTable

    GROUP BY Name, Error

     

    Adamus

    Tuesday, May 1, 2007 6:48 PM
  • I would add the following criteria...

     

    WHERE (   Name <> YourName

          AND Error = 'ID10T'

          )

    You do not want those showing up on his reports...  ;-)
    Tuesday, May 1, 2007 6:51 PM
    Moderator