Summing/Counting, but in GROUPS

[rgrantz]

I put a very similar post in the Reports newsgroup, but then it occurred to
me that a query setup may be necessary for what I'm trying to do, rather
than a calculated field. I wasn't sure. Sorry if this is against
netiquette.

I have the following tables:

OrderTable has

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 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 for 002: 9 (this is in Fabricator Group Footer)
- Fabricator 001
Machine Number 7
Cracks in Case: 2
Machine Number 4
Discolorations: 1
Total Fabricator Defects for 001: 3 (in Fabricator Group Footer)

The query source for this report is a GroupBy on Fabricator and Machine, a
Count on Defect, and Sum on Defect Quantity.

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 or Count 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

 

[Edward G]

Question is very nicely laid out, but in which table do you have
QtyProduced?

 

[Edward G]

rgrantz writes:
"The same way I can do a calculation that counts total defects per operator
(there is no
table field "total defects per fabricator)"

That is correct. Total defects per fabricator is the sort of information you
would get from a calculated field in a query.
And Total Qty Produced is likewise the sort of thing Access can do in a
calculated field of a query.
BUT you have a field in a table for Qty of Defect. WHERE is the field for
QtyProduced (defect-free). What table?

 

[rgrantz]

There is no QtyProduced, I was operating under the assumption that Access
could handle the calculation of unique items per Fabricator. The same way I
can do a calculation that counts total defects per operator (there is no
table field "total defects per fabricator), I assumed you could count items
produced using the ItemTable (each item is unique and made by only one
operator).

Is this not the case? Is there no way, when you have a table with ItemID
and Fabricator, to count Items per Fabricator? I know I can do a query that
does a Count PER fabricator, I just need to get it in the report in the
right Footer.

 

[Edward G]

Disregard last message. I was thinking that Qty of Defect was the number of
defective pieces. If I understand correctly, Qty of Defect refers to the
number of
defects on a single piece. Let me give this some thought. I am also "getting
hazy
in head" on this.

 

[Edward G]

Okay. I'm back and praying I understand what you are looking for.
Try this:
Construct a query with two fields from the Item Table....Fabricator and
ItemId
Click View>Totals
Under Fabricator in the Totals row you want Group By
Under ItemId you want Count.
Count of ItemId is what you will want to add to your report (I THINK)
Am I correct?

 

[rgrantz]

You are absolutely correct. However, how do I apply this total per
fabricator in the fabricator footer? The query you speak of for getting the
total per fabricator is a separate one from the query that provides the
details for the report. I want, in the report, to show the total items
produced by each fabricator in his own group footer. For the total defects
per fabricator I can just do a Sum over Group (and I do, and that works
well), because the defects are coming from the same query as all the rest of
the data. This total items per fabricator would be a different query, so
how do I have the field in the fabricator's group footer show the item count
for THAT fabricator, when the count and grouping is coming from a different
query?

My ideal report would show (this is a mix between report design and example
data in it):

---Fabricator Group Header---
- Fabricator 002
---Machine Group Header-----
Machine Number 4
-----Defect Group Header---------
Cracks in Case: 4
Discolorations: 4
----Machine Group Footer------
Total defects for this fabricator on this machine: 8
----Machine Group Header-------
Machine Number 5
----Defect Group Header--------
Cracks in Case: 1
----Machine Group Footer------
Total defects for this fabricator on this machine: 1
------fabricator footer-------
Total Fabricator Defects (Fabricator 002): 9
Total Items produced by Fabricator 002 (including ones with no defects): 25

The 25 number comes from that totally separate query, so how does the report
know to put the Count for Fabricator 002 in the fabricator 002 footer of
this report? What would the control source look like for the Fabricator
Footer's Fabricator Item Total?

 

[Edward G]

"However, how do I apply this total per fabricator in the fabricator
footer?"

Add this new query to the underlying query of the report.
Here's how:
Open the report in Design view.
Click View>Properties.
On the Properties sheet select the Data tab.
Click on the ellipsis next to Record Source.
This opens the Query for the report.
Add this new query to the collection of tables in the upper portion of the
Design window.
Access will probably create a join for you, but if not, join by Fabricator
to the
Fabricator field in the Item table
Drag the CountOfItemId field down to an open column of the QBE grid.
Save and close.
Count Of ItemId is now part of the field list in your report.

Are we there yet?