Top 5 Errors 1

[sucoyant]

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...

 

[dhookom]

The primary issue you are facing is due to using un-normalized tables. All of your yes-no fields have field names that are data values.
I would first create a UNION query that normalizes your data.
SELECT Employee, Date as ErrorDate, "Acct Paid Off" as ErrorType
FROM TableA
WHERE [Acct_Paid_Off] =-1
UNION ALL
SELECT Employee, Date as ErrorDate, "All Required Docs"
FROM TableA
WHERE [All_Required_Docs] =-1
UNION ALL
... for all fields.
You can then Create a totals query based on the UNION query that Groups By Employee and ErrorType and Counts ErrorTYpe.

Base your report off the totals query and set the sorting and grouping to
Employee Ascending Group Header: Yes
CountOfErrorType Descending

Add a text box to the detail section of the report:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Then add code to the On Format event of the detail section:
Cancel = Me.txtCount >5


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sucoyant]

Alright. I finished the query, and it's giving me the results I want.. so now i'm onto the report.

I'm a bit confused as to how I would go about doing this. You explaind well... but i'm still in the fog a bit.

There are 8 items that are pulled from the query. I want to show the top 5 of these, in decending order on the report.

Could you expand your answer please?

 

[dhookom]

Have you set up your report and set the sorting and grouping as suggested? Did you add the text box to the detail section of the report? Did you set the properties of the text box as suggested? Did you add the code to the On Format event of the detail section? If you have done all this, are you getting the correct results? If not, what are you getting? Are you seeing any error messages?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sucoyant]

The way I have my query set up is a bit different that your suggested UNION ALL query.
The UNION wouldn't work in this case because I have 8 different errors. Each error query looks like this:

SELECT [Lease Assignment].Employee, Sum(IIf([Lease Assignment].Acct_paid_off,0,1)) AS Acct_paid_off
FROM [Lease Assignment]
WHERE ((([Lease Assignment].Date) Between [Forms]![Menu]![txtLA_PRfrom] And [Forms]![Menu]![txtLA_PRto]))
GROUP BY [Lease Assignment].Employee

Then, I took all of the 8 error queries and INNER joined them by Employee to get this:

SELECT EER_LA_Acct_paid_off.Employee, EER_LA_Acct_paid_off.Acct_paid_off, EER_LA_All_required_docs.All_required_docs, EER_LA_Charged_correct_GL.Charged_correct_GL, EER_LA_Correct_docs_prepared.Correct_docs_prepared, EER_LA_Required_notes_memo_scrn.Required_notes_memo_scrn, EER_LA_Tax_check_or_filing_fee.Tax_check_or_filing_fee, EER_LA_Title_sent_correct_party.Title_sent_correct_party, EER_LA_Title_signed_correctly.Title_signed_correctly
FROM ((((((EER_LA_Acct_paid_off INNER JOIN EER_LA_All_required_docs ON EER_LA_Acct_paid_off.Employee = EER_LA_All_required_docs.Employee) INNER JOIN EER_LA_Charged_correct_GL ON EER_LA_All_required_docs.Employee = EER_LA_Charged_correct_GL.Employee) INNER JOIN EER_LA_Correct_docs_prepared ON EER_LA_Charged_correct_GL.Employee = EER_LA_Correct_docs_prepared.Employee) INNER JOIN (EER_LA_Required_notes_memo_scrn INNER JOIN EER_LA_Tax_check_or_filing_fee ON EER_LA_Required_notes_memo_scrn.Employee = EER_LA_Tax_check_or_filing_fee.Employee) ON EER_LA_Correct_docs_prepared.Employee = EER_LA_Required_notes_memo_scrn.Employee) INNER JOIN EER_LA_Title_sent_correct_party ON EER_LA_Tax_check_or_filing_fee.Employee = EER_LA_Title_sent_correct_party.Employee) INNER JOIN EER_LA_Title_signed_correctly ON EER_LA_Title_sent_correct_party.Employee = EER_LA_Title_signed_correctly.Employee) INNER JOIN EER_LA_Charged_correct_GL AS EER_LA_Charged_correct_GL_1 ON EER_LA_Title_signed_correctly.Employee = EER_LA_Charged_correct_GL_1.Employee;


The output looks like this:

Employee Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
Jon Smith 5 3 4 3 7 5 6 1
Bob Hob 4 1 5 3 2 1 7 4

Each of the questions is different. If I tried a UNION query, the results would look like this:

Employee Q1
John Smith 5
Bob Hob 4
John Smith 3
Bob Hob 1

This wouldn't work... because I need the questions displayed on the report along with the number of errors... and in this case, the only thing showing is question 1.

I'm sorry if this isn't making sense. I'm trying my best.
I don't mean to blatently disregard your UNION query... it's just the fact that I don't know them that well... and I can't figure out how I would get the results I need using a UNION query.

I would appreciate any and all help. Thank you so much for your time!

 

[sucoyant]

Wait wait wait.. brainfart
Oh man.. nevermind.

Thanks!

 

[sucoyant]

Give me just a bit. I royally messed up.
It's one of those days I'll tell you.

Please disregard my previous lengthy post.

 

[sucoyant]

Alrighty. Here is what I need. Sorry!

I need a query with the results of something like this


Question Errors
Q1 10
Q2 9
Q3 7
Q4 5
Q5 2

This is a quality control database, so the Q1 is a question that the reviewers are looking for when going over the file. I.e. Q1 = 1. Is the account Closed?

 

[sucoyant]

If needed, here is my table structure:

 

[dhookom]

I understand your structure. You have committed "spreadsheet" and can only be paroled if you build a normalized results set. Did you try create the UNION query that I suggested?

If so, what is your SQL view?

Do you get any error message from the union query?

What is the returned datasheet from the union query?



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sucoyant]

I got it!

Thanks dhookom! I used your UNION query idea, and it works great. The SQL looks like this:

SELECT "Was the account paid off?" as QuestionText, Acct_Paid_off from T5_Total
UNION
SELECT "Did we have all required documents?", All_required_docs from T5_Total
UNION
Select "Was the filing fee charged to the correct GL and Cost Center, if applicable?", Charged_correct_GL from T5_Total
UNION
Select "Was the title signed off correctly?", Title_signed_correctly from T5_Total
UNION
Select "Were the correct documents prepared if required?", Correct_docs_prepared from T5_total
UNION
select "Was the title sent to the correct party?", Title_sent_correct_party from T5_Total
UNION
Select "Was a tax check and/or filing fee included if required?", Tax_check_or_filing_fee from T5_Total
UNION Select "Were the required notes placed on the Memo Screen?", Required_notes_memo_scrn from T5_Total;

 

[dhookom]

You can use the union query as the source for a Totals Query:

SELECT QuestionText, Abs(Sum(ErrorType)) as NumOf
FROM quniName
GROUP BY QuestionText;

I used the field ErrorType as my original syntax suggested. You didn't alias this field so your query probably returns Acct_Paid_off rather than ErroType.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

My most recent SQL/query can be sorted by the sum Descending and the TOP parameter of the query can be set to 5.

BTW: is the a reason why you removed the dates and employee from the UNION syntax that I suggested? Your original question suggested you wanted this report by employee. If you remove the employee from the UNION query, you won't be able to retrieve the results you originally asked for.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.