Return Top 5 (fields, not values)

[ohmbru]

I have a query that is used as a quality checklist. The fields are:

ID
User
Department
Date
Check Item 1
Check Item 2...(these are check boxes where the user puts a check if the an error is found. There are 45 different items listed. The value is 0 if not checked and -1 if checked.

I want to determine the top 5 errors. My query (based on the above) sums each check field, but how can I return the name of the field that is checked most often?

 

[MichaelRed]

Have you tried a crosstab query? It works for the general class of problem.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ohmbru]

A crosstab gets me part of the way, but doesn't give me exactly what I need:

DEPT CHECK 1 CHECK 2 CHECK 2 ...
East -2 0 0
West -1 0 0


My (temporary?) solution is to export my query to Excel and, manipulate the data with a macro, and import it back into a a different table.

East West All
CHECK 1 -2 -1 -3
CHECK 2 0 0 0
CHECK 3 0 0 0
...


It will work, but requires a step outside of Access (which I was trying to avoid).

 

[MichaelRed]

I'm somewhat lost as to the SPECIFIC results you are looking for. Perhaps you could post a sample DESIRED results. It looks like just swappint he row/col in the crosstab returns something similar to you (temporary?) results. One ver of the crosstab (w/ perhaps an ABS thrown in) would be the results. If either of them is 'correct' -but extended- then just layer a select with a TOP 5 clause included.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over