Display Top 3 Re-Occuring Fields in a Report

[nick404]

Hi all,
I am making a database for tracking problems encountered with raw materials we receive from our suppliers. For each commodity there is a specific set of errors that could occur (commodity 'a' only has errors 1a, 2a, 3a and similarly for other commodities). The designed query allows the user to search a certain part number and then shows all the errors that have occurred (and the commodity associated) with that specific part. In the report I would like to, however, only display the top three re-occurring errors. So if for a part 5 different errors types have occurred, and lets says for error type 1 it occurred 4 times, error 2- 3 times, error 3- 3 times, and for the remaining two error types the error only occurred once. I only want the top three highest occurrences to show within the report. Any way I can do this?? Perhaps the screenshot can shed some clarity:

From the screenshot you see there is Non-Conformance ("error") types 1a, 1b, 2a, and 3a, and it displays the commodities that experienced that error and totals the number of times this occurred. I would only like to display the top 3 error types based on number of occurrences. So I only want the report to show what is circled in red. (As the database grows in size I may have to expand this to top 4, 5, 6, &c. based on management's needs).


Thank you for the help!

[June7]

See if this helps http://allenbrowne.com/subquery-01.html#TopN

[ranman256]

In your query, count the items , in the query property set TOP VALUES = 3

[nick404]

when i set the top values = 3, it no longer displays each record of the non-conformance type. It reduces them all to 1 occurrence. I need to keep the re-occurences

I believe I will need a running count for each non conformance in my query... not sure on the SQL for this? current sql is:

SELECT tblMasterData.ID, tblMasterData.PartNumber, tblMasterData.Commodity, tblMasterData.NonConformance
FROM tblMasterData
GROUP BY tblMasterData.ID, tblMasterData.PartNumber, tblMasterData.Commodity, tblMasterData.NonConformance
HAVING (((tblMasterData.PartNumber)=[Enter Part Number to Search]));

Edit:
Added this line to no avail... COUNT ("*","tblMasterData", "NonConformance <= " & NonConformance)

[orange]

Can you post some data from your tblMasterData?

[nick404]

Everything I have so far is dummy data, I don't actually have the actual information yet for my fields. There is a second table containing NonConformance (as PK) and Commodity and a one-to-many between the NonConformance fields.
tblMasterData:

[IrogSinta]

You'll need two queries. Here's the SQL for the first one:

SELECT TOP 3 Count(tblMasterData.ID) AS CountOfID, tblMasterData.PartNumber, tblMasterData.NonConformance
FROM tblMasterData
GROUP BY tblMasterData.PartNumber, tblMasterData.NonConformance
HAVING (((tblMasterData.PartNumber)="11A"))
ORDER BY Count(tblMasterData.ID) DESC;

And here's the 2nd query which is what you would use for your report. Just replace the name Query1 with whatever you named your first query:

SELECT tblMasterData.*
FROM tblMasterData INNER JOIN Query1 ON (tblMasterData.NonConformance = Query1.NonConformance) AND (tblMasterData.PartNumber = Query1.PartNumber);

Be sure to read the link that June7 posted. It's got very good information on the subject.

Ron

[nick404]

The first code you posted works awesome for what I need. The second query I am not sure what the objective is? It gives me a lot of repetition in the data is returns. I get 3 copies of every ID (which is the primary key, autonumber).

I apply the second query to the report and it works perfectly! Thanks to everyone! Out of curiosity what does the second query do for the report??

[IrogSinta]

The first query just gives you the counts of each Nonconformance. In other words, it tells you that there are 3 commodities that have 1a NonConformance, 2 that have 2a, etc. It puts it in descending order and then takes the top 3. What it doesn't give you is, what are the actual commodities in each Nonconformance category. Since you need this in your report, that's where query 2 comes in. It joins the table with query1 so that you get all the fields in your table, but only for those records that are in query1.

Ron