Re: Complex Grouping (I think) and totalling

From: rgrantz (r_grantz_at_hotmail.com)
Date: 12/10/04 

Date: Fri, 10 Dec 2004 09:33:47 -0800

I'm sorry, I realized on my way home that I completely left out my table
structure, that was stupid.
I have:

- Production Table w/ OrderNumber
- ItemDetail Table w/ ItemID (each ItemID is tied to Production Table's
OrderNumber, because an order can have one item or many items), Machine, and
Operator
- QualityControl Table w/ Error types and quantities per ItemID (tied to
ItemID in ItemDetail Table)

I did try using a wizard to group/count/sort these fields, but somewhere
there's some detail I'm missing, because I'm not getting totals of the error
type by machine then operator. It seems like this may be one of those times
that 2 queries need to be run before the report can be done, but I'm
confused about how to set them up. I've tried several combinations of
Count, Sum, and GroupBy in the query, but have not been able to get a
data*** that shows a sum of each error type for a specific operator on a
specific machine

This Query gives me multiples of operator names AND multiples of machine
names AND multiples of error types:

SELECT EmpList.EmpName, ItemData.MachineNum, QCTable.ErrorNum,
QCTable.ErrorNum, ErrorList.Error, Sum(QCTable.Quantity) AS SumOfQuantity
FROM (ErrorList RIGHT JOIN ((ItemData INNER JOIN ProductionData ON
ItemData.OrderNum = ProductionData.OrderNum) INNER JOIN QCTable ON
ItemData.ItemNum = QCTable.ItemNum) ON ErrorList.ID = QCTable.ErrorNum) LEFT
JOIN EmpList ON ItemData.OperatorNum = EmpList.EmpID
GROUP BY EmpList.EmpName, ItemData.MachineNum, QCTable.ErrorNum,
QCTable.ErrorNum, ErrorList.Error, ItemData.DateProd;

Even with these multiples, when setting the grouping in the report (top
level group = Operator, next level = Machine, next level = ErrorType), I'm
not getting the total count of that error type (the Quantity field). The
grouping by Operator and then by Machine and then by Error Type is working,
but the SumofQuantity is not.

So, to briefly recap, I need to have a report that:

- Top-level groups by operator (I changed this from the first post)
- Under operator, groups by machine
- Under machine, shows the total of each type of error (TOTAL on that
machine being operated by THAT operator, ie. if Tom made 12 items on machine
2, and on 6 of the items there were 4
discolorations, and on 3 of the items there were 2 cracks, and then on
machine 3 he made 10 items, and on 4 of them there was one crack, the report
would show:

Tom
    Machine 2
        Discolorations: 24
        Cracks: 6
    Machine 3
        Cracks: 4
NextOperator:
    etc...

I alread have a report that itemizes error types and totals per ITEM per
machine per operator, but I can't seem to get one that totals/groups by
error TYPE. I've done a lot of fiddling with the query builder, but am
having a hard time getting the "Group By"s and "Sum"s and "Count" right.

Thanks again, sorry about leaving out the table structure. if anyone needs
it, I can email the mdb w/ structure only.

Should I post this is Reports instead, or also? It's usually a matter of
opinion, it seems, on some of these reports vs. queries issues.

"John Spencer (MVP)" <spencer4@comcast.net> wrote in message
news:41B902EC.52CCE9DA@comcast.net...
> Hard to say without knowing your table structure.
>
> SELECT MachineNumber, MachineOperator, ErrorType, Count(MachineOperator)
as ErrorCount
> FROM <<<Your Table(s) Joined to each other >>>
> GROUP BY MachineNumber, MachineOperator, ErrorType
>
> rgrantz wrote:
> >
> > OK, I have a manufacturing plant DB/reporting utility here. The basic
> > workflow structure is:
> >
> > - 10 machines
> > - Each machine has an operator
> > - Each machine manufactures multiple identical items per day
> > - There are 30 different types of defects that can happen when
manufacturing
> > an item on the machine
> > - Each of the defects could happen more than once per item
> >
> > So, on machine 1, an operator might have an item produced that has 2
cracked
> > surfaces, 3 discolorations, and 1 soft spot. His next item might just
have
> > 1 discoloration. The next one might have 1 discoloration, 2 soft spots,
> > etc.
> >
> > I have several reports already going that do what I want, but I need to
make
> > one that:
> >
> > - Top-level groups by machine
> > - Under machine, groups by machine operator
> > - Under operator, shows the total of each type of error (TOTAL on that
> > machine, ie. if they made 12 items, and on 6 of them there were 4
> > discolrations, there would just be a 24 after "Discoloration")
> >
> > I alread have a report that itemizes error types and totals per ITEM per
> > machine per operator, but I can't seem to get one that totals/groups by
> > error TYPE. I've done a lot of fiddling with the query builder, but am
> > having a hard time getting the "Group By"s and "Sum"s and "Count" right.
> >
> > Any help would be greatly appreciated. Using Access 2000 Premium on
Windows
> > 2000 Pro.