Summing AND Counting over group?

  • Thread starter Thread starter Duane Hookom
  • Start date Start date
D

Duane Hookom

You don't show any records with an "items produced" value/field.
Your QCTable doesn't have any values that match values in the ItemTable.

I can only assume that you need to add a group header for the record level
that contains the items produced value and set up a running sum. Until you
fix the issues with the presentation of your actual records, this is only a
guess.
 
I have:

- ProdTable with Order Number (Order Number is unique)
- ItemTable with item ID per order (there can be many items per order, or
just one - Item ID is unique). This table has Fabricator also, for the
person who made the item, and MachineNumber for the machine they used to
make it
- QCTable with defects and count of each per item (there can be multiples of
one defect per item, or several defects on one item, etc.)

For instance:

The ProdTable has:

Order #
123
234
456

The ItemTable has:

Order # Item # Fabricator MachineNumber
123 01 002 4
123 02 001 7
234 23 001 4
234 24 002 5
456 25 002 4

The QCTable has:

Item ID Defect Qty of Defect
Item 01 crack in case 4
Item 01 Discoloration 2
Item 02 crack in case 2
Item 23 Discoloration 1
Item 24 crack in case 1
Item 25 Discoloration 2


I have a report that totals the Quantity of each Defect type per Fabricator
per Machine (groups on Fabricator, then Machine, Then Defect Type,
regardless of Item ID):

- Fabricator 002
Machine Number 4
Cracks in Case: 4
Discolorations: 4
Machine Number 5
Cracks in Case: 1
Total Fabricator Defects: 9 (this is in Fabricator Group Footer)
- Fabricator 001
Machine Number 7
Cracks in Case: 2
Machine Number 4
Discolorations: 1
Total Fabricator Defects: 3 (in Fabricator Group Footer)

What I would like to add is just one field in the Fabricator Group Footer
that also shows total of items produced (so I can do an average of Total
Defects divided by Total Items). I'm having a hard time wrapping my head
around getting the Count of items per Fabricator. I assume I need to do a
completely different query and use the DCount function in a calculated
field, but I keep getting hazy in the head when trying to apply it to a
Group and having that Fabricator's total go in that Fabricator's group
footer.

Thanks for any help on this. I fear that this is one of those questions the
answer to which makes me feel stupid, but I honestly DID try a bunch of
stuff and search the newsgroups first, I just couldn't find something that
matched my needs.

Thanks again
 
Sorry, I mislabeled some of the fields. The correct presentations is:

Order #
123
234
456

The ItemTable has:

Order # Item ID Fabricator MachineNumber
123 01 002 4
123 02 001 7
234 23 001 4
234 24 002 5
456 25 002 4

The QCTable has:

Item ID Defect Qty of Defect
01 crack in case 4
01 Discoloration 2
02 crack in case 2
23 Discoloration 1
24 crack in case 1
25 Discoloration 2

I don't have a field for "Quantity Produced," as I was assuming Access could
perform some calculation, such as Count, or DCount, per fabricator ID for
the Item ID's assoaciated with that Fabricator ID. Do I actually need to
add a table with a field for Quantity Produced per fabricator and manually
input this data? That seems strange for Access.

Sorry again, and can someoene help me still?
 
From my earlier reply
'I can only assume that you need to add a group header for the record
level that contains the items produced value and set up a running sum.'

Apparently the count of your Item Table records is the Quantity Produced.
Set this as a group header and add text box:
Name: txtQty
Control Source: =1
Running Sum: Over All

In the report footer, you can get the total quantity produced with this text
box:
Control Source: =txtQty
 
This won't give me a total Item Produced PER operator, though, will it? I
want to put the total items each operator produced (regardless of machine
used) in the Operator's footer, the same way the total number of defects
(regardless of machine or defect type) is in the footer for fabricator. I
have total number of defects for that fabricator, and I just want to also
have total number of items produced by that fabricator.

I already have another report that's total defects overall (regardless of
fabricator, machine, etc.) and total items (regardless of same).

I need to get total items PER FABRICATOR by using some sort of count
function of unique items per fabricator ID. I was assuming I could do this,
since the Defect count (per fabricator) was achieved using grouping.
 
You can set the running sum to be over group if your operator is the
grouping. You can also create a totals query with the exact numbers that you
want by operator and include the totals query in your report's record
source.
 
Back
Top