Pull top 5 entries per month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tim@rybak.com - 02 Nov 2005 18:06 GMT

Hello all,

I cannot figure this out for the life of me. I looked over the boards,
but didn't find anything that would help. Also, I am just learning
Access, so please respond with idiot proof directions, and thank you in
advance.

I have a single table that my user's enter data into. The table has 4
fields that I use to query data by (Date, Shift, Right Side/Left Side,
Front/Rear), and 50 or so fields that store values equal to errors
found in our product. Every day the quality folks keep up with errors
that occur, and then enter the data into the database. All of the
errors have default values of 0. The operator opens a new record,
inputs the Date, Shift, etc. and then changes the 0's of the fields
that had errors during the day to an integer equaling the number of
errors at that point.

I hope this is clear.

Anyway, I want to be able to query the data to give me the top 5 errors
that occurred on a particular date, or shift, or date and shift, or
date, shift and right or left, etc. Basically, by any combination of
the 4 identifier fields. To keep it simple, I can keep these as
separate queries, but I can't figure out how to have it sum, say, all
of the errors for October, and then report back the top 5.

Thanks!
Tim

Reply to this Message

KARL DEWEY - 02 Nov 2005 19:20 GMT

I think your data structure is wrong. You need a table that list the error
types instead of the 50 fields.
ErrorCodes--
ErrID - Autonumber - primary key
Description - Text
Active - Text - Default "Y" - Validation "Y" or "N"

Product--
ProdDate - DateTime
Shift - Number - Integer
Location - Text - Validation "R" or "L" or "F" or "B" (Right, Left,
Front, Back)
ErrID - Number - Integer
QTY - Number - Integer

Create a data entry form using a ListBox for selection of the ErrID.

Use a totals query to extract your error data.

> Hello all,
>
[quoted text clipped - 24 lines]
> Thanks!
> Tim

Reply to this Message

John Spencer - 02 Nov 2005 19:25 GMT

Your problem is all in the design of your table structure.
You should have at least two tables and probably three

Table: A
IdKey - PrimaryKey (I would use an autonumber)
Date
Shift
(Not sure if these next two belong here or in one of the other tables)
FrontRear
RightSide/LeftSide

Table: ActualErrors
IdKey (This would be equal to the IdKey in TableA
ErrorID (This would be equalt to the ErrorID in ErrorTypes
ErrorCount

Table: ErrorTypes
ErrorID - Primary Key
ErrorName

With this structure your current task would be simple AND if an additional
error type was added, you would simply need to add a new record to the error
type table.

A way to make your current structure work would be to use a UNION query to
normalize the data. With 50 error fields, I think you will find that this
query breaks with a too complex error.

SELECT [Date], Shift, [Right Side/Left Side],[Front/Rear], ErrorField1 as
ErrorFieldValue, "ErrorField1" as ErrorType
FROM YourTable
UNION ALL
SELECT [Date], Shift, [Right Side/Left Side],[Front/Rear], ErrorField2,
"ErrorField2"
FROM YourTable
UNION ALL
...
SELECT [Date], Shift, [Right Side/Left Side],[Front/Rear], ErrorField50,
"ErrorField50"
FROM YourTable

Once you've done this (if it is possible) then you used the saved Union
query

SELECT TOP 5 ErrorType, Sum(ErrorFieldValue) as CountErrors
FROM TheUnionQuery as Q
WHERE ....(enter your criteria here)
GROUP BY ErrorType
ORDER BY Sum(ErrorFieldValue) DESC

> Hello all,
>
[quoted text clipped - 24 lines]
> Thanks!
> Tim

Reply to this Message

Michel Walsh - 02 Nov 2005 19:27 GMT

Hi,

Change the design of the table. Instead of

Date, shift, side, front_rear, f1, f2, f3, ..., f50
1-1-01, 1, left, front, 0, 2, 77, ...., 1

have

Date, shift, side, front_read, code, value
1-1-01 1 left front 2 3
1-1-01 1 left front 3 77
...
1-1-01 1 left front 50 1

ie, have one record per possible default (which has a count <> 0)

It is then a matter to find a MAX(value) GROUP BY date to get the
maximum, number of error for any given code, per day.

======================
SELECT date, MAX(value)
FROM myTable
GROUP BY date
======================

Nothing more. See how it is easy with the right table design. I would use
another name than date for a field name, since there may be some confusion
with the Date() function, which returns today's date.

To get the code that generated that MAX, use one of the technique exposed at
http://www.mvps.org/access/queries/qry0020.htm

Hoping it may help,
Vanderghast, Access MVP

> Hello all,
>
[quoted text clipped - 24 lines]
> Thanks!
> Tim

Reply to this Message

tim@rybak.com - 02 Nov 2005 20:11 GMT

I think I may be able to change the structure of the table to match
Michael's suggestion. It seems to make the most sense to me. However,
I am bit lost on how to pull the "Top 5" for a given date, or
date/shift, etc. combination.

I understand that with this table structure, I would have only one
error and an associated quantity of that error per record, instead of
50 values per record. I just don't see how to get at the Top 5 part of
it.

Please excuse my ignorance, but I am definately learning!

Tim

Reply to this Message

KARL DEWEY - 02 Nov 2005 21:50 GMT

In the design view of a Totals query click on icon bar symbol that reads
"All" and enter the number 5. Use SORT Descinding in the sum column.
NOTE - If your fifth place item has others of equal value they will be
pulled also - example -
55 - 1
45 - 2
44 - 3
43 - 4
42 - 5
42 - 6
41 - 7

You will get six in the output.

> I think I may be able to change the structure of the table to match
> Michael's suggestion. It seems to make the most sense to me. However,
[quoted text clipped - 9 lines]
>
> Tim

Reply to this Message

Michel Walsh - 02 Nov 2005 22:44 GMT

Hi,

If there is data for more than one month, you also need a WHERE clause to
limit the records to the month you are interested.

If you need to see more than one single month, you have to change the
technique. One possible solution is to rank-per-month:

SELECT a.code, Format(a.date, "yyyy.mm.01"), a.value
FROM myTable As a INNER JOIN myTable As b
ON a.code=b.code
AND a.value <= b.value
AND Format(a.date, "yyyy.mm.01") = Format(b.date, "yyyy.mm.01")
GROUP BY a.code, Format(a.date, "yyyy.mm.01"), a.value
HAVING COUNT(*) <= 5

To understand the strategy, starts to look at the inner join as a cross join
where each record from "a" is "associated" with each possible record of
"b". Next, keep only the records, in the previous merge, that satisfy the ON
clause. Clearly, for a given code and a given year-month, a given record
initially from "a" will see all the records, in "b" for which b.value >=
a.value. So, if a.value is the max, in this group, there will be just ONE
record in "b" still associated to it. If a.value is the second max value,
then there will be 2 records in "b" still associated to it, and so on. The
number associated to the group is given by COUNT(*), so the suggested code.

Hoping it may help
Vanderghast, Access MVP

> In the design view of a Totals query click on icon bar symbol that reads
> "All" and enter the number 5. Use SORT Descinding in the sum column.
[quoted text clipped - 23 lines]
>>
>> Tim

Reply to this Message