This is my first post so bare with me.

I have a report that runs off of a query and it works perfectly. It is a quality control database. It lists the errors that were made and how many times they were made in a given month

Now I need to list at the bottom of the report the Top Three Errors only! I am unsure how to extract that data. Does Access have a function for that or would I have to use VBA?

I am assuming I would embed a subreport, but I am not sure how to only show the top three errors. Any suggestions or helping finger to point me in the right direction would be wonderful.

Thank you,

If you right click where you add tables to your query select properties then top values you can select from the drop down list or enter your own

Thank you. That was a help in the right direction.

*bump*

Ok, guys, I tried what was stated above. That didn't work.

The totals for each of the error categorys does not get sumed until the report.
Therefore I can't use the top value function in the query itself. I doesn't retrieve the data I need.

I need to do it at the form level. Is there a loop I can do with VBA to list the top 3 at the bottom of the report? I am at a complete loss here.

I have included a screen print of my report so you can see what I am working with.

Thanks guys,
Selena

Hum... With out doing a lot of digging around, my first attempt would be to put it a subreport. Think this would work?

Hum... With out doing a lot of digging around, my first attempt would be to put it a subreport. Think this would work?

Umm, I would love to, but I the decision making has to be done on the report. The query is set up correctly and will not work with the top value.

I'm currently searching now. I'm looking at bubble sort, trying to find that in VBA. If I use that, I can just pull the 1st three since bubble sort puts things in order.

Thanks Kenny, head back to your b'day party now. :D

I think if your sub report is based on the main reports query and you add the 'Top' predicate in the sql statement it will work. Something like:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

Edit: In short: Query Properties->Top Values property

Edit 2: Sorry - I see you've been there :( All I can suggest is to to a totals query on the original data to get the top values...

I think if your sub report is based on the main reports query and you add the 'Top' predicate in the sql statement it will work. Something like:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

Edit: In short: Query Properties->Top Values property

Edit 2: Sorry - I see you've been there :( All I can suggest is to to a totals query on the original data to get the top values...


Yeah, tried that too, It just isn't working. I am going to have to do it on the report's load event, but not sure how to do it.

Thanks though Ken, it appears in your old age, your mind is still as sharp as ever. *ducks and runs off giggling*

Happy b'day Kenny boy!!

Stay with me on this...

You have a query that the main report is based on. Is it a pre-built one or is it a sql statement in the reports recordsource?

Stay with me on this...

You have a query that the main report is based on. Is it a pre-built one or is it a sql statement in the reports recordsource?

It is a the report is based on a query. I'll post the SQL statement below.

SELECT DISTINCT tblErrors.intErrorCategory, tblErrorCategory.strErrorCategory, tblErrors.intFinalCount, tblErrors.ysnFinalError, tblErrors.ysnMembershipError, GetMonth([dtmReturned]) AS intMonth, tblEntryLog.ysnMembership, GetYear([dtmReturned]) AS intYear
FROM tblErrorCategory INNER JOIN ((tblEmployees INNER JOIN tblEntryLog ON tblEmployees.strEmpID = tblEntryLog.strAdminID) INNER JOIN ((tblDocuments INNER JOIN tblDocumentReview ON tblDocuments.intDocumentID = tblDocumentReview.intDocumentID) INNER JOIN tblErrors ON tblDocumentReview.intReviewID = tblErrors.intReviewID) ON tblEntryLog.intEntryNumber = tblDocumentReview.intEntryNumber) ON tblErrorCategory.intErrorCategoryID = tblErrors.intErrorCategory
GROUP BY tblErrors.intErrorCategory, tblErrorCategory.strErrorCategory, tblErrors.intFinalCount, tblErrors.ysnFinalError, tblErrors.ysnMembershipError, GetMonth([dtmReturned]), tblEntryLog.ysnMembership, GetYear([dtmReturned]), tblEntryLog.dtmReturned
HAVING (((tblErrors.ysnFinalError)=True) AND ((tblErrors.ysnMembershipError)=False) AND ((tblEntryLog.ysnMembership)=False) AND ((tblEntryLog.dtmReturned) Is Not Null))
ORDER BY tblErrors.intFinalCount DESC;


This will pull each error category for that date. The report then sums the error category for the entire month.

That is why the top value function in the Query will not work. I tried to do it strickly for the month, but that failed too. It didn't give the correct numbers. *sighs*

*listens again to Kenny*

That's hard to get my head around... Can you post a sample db with enough in it for me to see how it works? Or at a minimum post a sample db with this querys resulting records in a table?

Or would you rather wait and see if someone else offers help?

That's hard to get my head around... Can you post a sample db with enough in it for me to see how it works? Or at a minimum post a sample db with this querys resulting records in a table?

Or would you rather wait and see if someone else offers help?

I would love to, but the dude who build this database made it VERY complicated. It is VERY hard to separate out stuff. Everything is so inter-dependent on everything.

Would it easier if I export the result of the query once it is ran so you can compare it to the report above. You will be able to see the differences in the query results and the report above.

I'll post it anyways.


I have posted an adobe file with the query results as well as a screen print of my query. Thank you for all your help.

Are the top errors shown at the top of the report sorted by the count ?

No, the top errors are through out the report. The report is sorted based on the error cat Id. Starting with Error cat 1 and so and so forth.

Example
Error Cat # -- Total points
Error cat 1 -- 5
Error cat 2 -- 2
Error cat 3 -- 10
Error cat 4 -- 3
Error cat 5 -- 23
Error cat 6 -- 1

Therefore it should pull Error cat 5, Error cat 3 and Error cat 1 in that order at the bottom of the report.

The query can't do it becuase for example Error cat 37 is listed multipule times and is sumed once it gets to the report.

I have tried to sum it in the query, but with out success. :(

You need a totals query based on the original query

You need a totals query based on the original query

Grr, I tried that and It didn't work, but I will try again.

I am about to revert back to the old way: 1) print report 2) get highlighter 3) highlight Top 3 4) Give to managment and smile.

Ok, I did another query based on my first query and summed them. It appears to be matching the report.

However, when I tried the top 3 thingy, it didn't work. How do I specify which field to base the top 3 on?

It appears, I need to sort Decending so I can get the highest, and then it will only pull the top 3 errors. *blush* Well duh!!!!!

Thanks guys, I think the light bulb may have came on.