Results 1 to 4 of 4

Thread: Top 5 Errors

  1. #1
    Join Date
    Jun 2003
    Posts
    76

    Unanswered: Top 5 Errors

    Hello all!

    I have to create a report that shows the "Top 5 Errors" by employee.

    My table in the database looks like this:

    Employee
    Cust_FName
    Cust_LName
    Account#
    Date
    Reviewer
    Comments
    Acct_paid_off
    All_required_docs
    Title_signed_correctly
    Correct_docs_prepared
    Title_sent_correct_party
    Tax_check_or_filing_fee
    Charged_correct_GL
    Required_notes_memo_scrn
    The items in bold are all "Yes/No" checkmark fields. They are also the items that need to be calculated for the Top 5 errors.
    Remember, this is by EMPLOYEE... not the table as a whole.

    I'm guessing I would need some VB code that would display only the top 5 errors?

    I'm perplexed... I would really appreciate any help on this one...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    just guessing...

    Code:
    select top 5
           Employee
         , sum(  
             iif ( Acct_paid_off = -1 , 1 , 0 )
           + iif ( All_required_docs = -1 , 1 , 0 )
           + iif ( Title_signed_correctly = -1 , 1 , 0 )
           + iif ( Correct_docs_prepared = -1 , 1 , 0 )
           + iif ( Title_sent_correct_party = -1 , 1 , 0 )
           + iif ( Tax_check_or_filing_fee = -1 , 1 , 0 )
           + iif ( Charged_correct_GL = -1 , 1 , 0 )
           + iif ( Required_notes_memo_scrn = -1 , 1 , 0 )
              ) as TotalEmployeeErrors
    group
        by Employee
    order by 2 desc
    rudy
    http://r937.com/

  3. #3
    Join Date
    Dec 2003
    Posts
    6

    Re: Top 5 Errors

    You really don't need to use VB code to do this. It would be muh more effecient to use "TOP" predicate in the report's underlying query. Try this:

    SELECT TOP 5 ([Acct_paid_off]+[All_required_docs]+[Title_signed_correctly]+[Correct_docs_prepared]+[Title_sent_correct_party]+[Tax_check_or_filing_fee]+[Charged_correct_GL]+[Required_notes_memo_scrn]) AS TotalErrors, Employee, Cust_FName, Cust_LName, Account#, Date, Reviewer, Comments FROM (Your Table Name)
    ORDER BY 1, Account#

    Two things to note in this query: The "1" in the ORDER BY clause corresponds to the column where the Yes/No fields are added together; be sure this column is sorted first. Also the TOP predicate does not distinguish between values that are equal; for example, if the query had 7 records that returned -8 for the TotalError field, then all 7 records will be displayed. You need to have a secondary sort record in the ORDER BY clause that will further distinguish each record so that only 5 will be returned.

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Question

    The word "top" is a little ambiguous here. You list eight or so different "error conditions" in a single record but...
    • If I commit all eight gaffes with one account, is that eight errors or just one?


    If you want to simply find them, you're just looking for records WHERE any one of the flags are Yes. (Note, you must list Yes on separate rows of the query form, not the same line, because you're looking for OR not AND.) "Group by" the employee-ID and count the number of records for that employee.

    If you want to count severity, I like the idea of adding a calculated field which sums up "iif()" function calls as previously described. (Although I don't think you need to include the "= -1" bit.) Then you can sum this field as-shown.

    It'll help, in that case, to go ahead and include the WHERE condition described above, even if you sum the scores as described in the last paragraph, so that only records which will sum to something greater than zero will be considered. (Only records having some error will be selected, and scored.)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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