Complex Grouping (I think) and totalling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have you tried using the grouping and summary options while building your
report using the wizard?
 
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.
 
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
 
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
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.

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.
 
You might try dropping the ItemData.DateProd from the group by. That is going
to group on every variation of DateProd. If you want to use DateProd to limit
the returns to a date range use it as a WHERE criteria


SELECT EmpList.EmpName, ItemData.MachineNum, QCTable.ErrorNum,
Count(QCTable.ErrorNum) as ErrorCount, 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

=================== Add line to limit to a date range ==============
WHERE ItemData.DateProd Between #1/1/2004# and #1/31/2004#
=================== Add line to limit to a date range ==============

GROUP BY EmpList.EmpName, ItemData.MachineNum, QCTable.ErrorNum,
ErrorList.Error
 
Back
Top