Grouping and Sums issue (do it w/ Query or Report?)

[rgrantz]

I posted this in the query newsgroup, sorry for crossposting, but I thought
maybe a report guru might know how to tackle this via report properties
rather than underlying queries. I have:

- Production Table w/ OrderNumber
- ItemDetail Table w/ ItemID (each ItemID is tied to Production Table's
OrderNumber, because one order can have one item or many items), Machine,
and Operator
- QualityControl Table w/ Error types and Quantity per ItemID (tied to
ItemID in ItemDetail Table). Each item could possibly have no errors, 1
error, or multiple different types of errors, with quantities of each

For example (QualityControl Table):
Item ID ErrorType Quantity
13 crack 1
13 Discoloration 3
14 Discoloration 1
14 fissure 2
15 No Error



I need to have a report that:

- Top-level groups by operator
- Under operator, groups by machine
- Under machine, shows the total of each type of error (TOTAL on that
machine being operated by THAT operator)

For example, 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 already 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.


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 operator then machine. 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
datasheet 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.


Thanks for any help or feedback on my table/query setup. I would appreciate
your input.

 

[Larry Linson]

It does not appear to be crossposted... there is only this newsgroup's name
in the header... could you possibly be apologizing for multiposting? That
would be much worse, as we would not know if you got any answers there
unless we went and looked for them there. Modest crossposting isn't a
problem but multiposting -- the same question in separate posts to multiple
newsgroup is a problem for most people who answer questions.

If you did NOT receive a useful answer in the queries newsgroup, post back
here to clarify if ItemID is unique across the database, or just unique for
a single OrderNumber. If you already got your answer, just for future
information, visit http://www.mvps.org/access/netiquette.htm for good
suggestions on effective use of newsgroups.

Larry Linson
Microsoft Access MVP