Filter all of a group out if one or more parts aren't true

[Gabbelgak]

Drawing a blank on this and need help.

I have the following table:

SW39	000013	WP
SWM2	000024	M5
SW39	000013	M8
SWM2	000006	M5
GMFQ	PTG513	M8
GMFQ	PTG513	M5
GJCA	137014	WP
GJCA	137015	WP
SWM2	000036	M3
SHCP	NA3294	M3
SC22	DDH150	M5
SC22	DDH027	M8
SW1V	NPB152	M8
SC22	DDH035	M8
S5CH	A35003	M8
SC22	DDH101	M5
YZAA	041985	M5
SWGI	000037	M8
GDCC	ADL590	M3
SW1V	NPB152	M5
SC22	130666	M3
SW39	MRP026	JC
GDHC	475559	JC
GDHC	475565	JC
SCCB	DAN017	M8
SC22	DDH035	M3
SC22	DDH027	M3
SH01	620066	JC
S5CH	A35003	JC
SWGI	000037	JC
SC22	DDH158	M8
SC22	DDH158	M3
SC22	DDH129	M8
SC22	DDH129	M3
SCCB	DAN017	M3
SWM2	000008	JC
SWM2	000024	JC
SC22	DDH158	M3
SC22	DDH129	M3
SWM2	000008	M3
SWM2	000024	M3
SWM2	000008	M3
SWM2	000008	JC
SWM2	000008	JC
SWM2	000008	M3
GMC6	NA2055	M3
GMC6	NA2055	M3
GMC6	NA2055	M3
SWM2	000036	M3
SWM2	000008	M3

<caption>active</caption>

<thead>


[TH="bgcolor: #c0c0c0"] Supplier [/TH]

[TH="bgcolor: #c0c0c0"] Item [/TH]

[TH="bgcolor: #c0c0c0"] Status [/TH]




</thead>
<tbody>


</tbody>
<tfoot></tfoot>

I'm trying to filter the results so it only will show groups of Supplier / Item if all of the Status = JC

I was able to put together a query that counts the number of status for each group and another that counts the JC's but am drawing a blank now.

Count Query:
SELECT active.SUPPLIER, active.ITEM, Count(*) AS Expr1
FROM active
GROUP BY active.SUPPLIER, active.ITEM;

Job Complete Count:
SELECT active.SUPPLIER, active.ITEM, Count(*) AS Expr1
FROM active
WHERE STATUS = "JC"
GROUP BY active.SUPPLIER, active.ITEM;


If the count result = the same as the job complete count for the same group I'm trying to get it to show all of them, and if not filter the whole group out.

 

[Micron]

Not sure of what you're asking. Try

SELECT tblSuplrStatus.Supplier, tblSuplrStatus.Item FROM tblSuplrStatus 
WHERE (((tblSuplrStatus.Status)="JC")) 
GROUP BY tblSuplrStatus.Supplier, tblSuplrStatus.Item;

 

[Gabbelgak]

Your query will show me what has a JC which isn't really what I want. The desired result from the above table would be this:

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Supplier[/TD]
[TD]Item[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]GDHC[/TD]
[TD]475559[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]GDHC[/TD]
[TD]475565[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]SH01[/TD]
[TD]620066[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]SW39[/TD]
[TD]MRP026[/TD]
[TD]JC[/TD]
[/TR]
</tbody>[/TABLE]

I've removed all of the S5CH A35003's for example because at least one of the status codes not JC. If the M8 S5CH A35003 were to change to JC then the desired result would be

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Supplier[/TD]
[TD]Item[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]GDHC[/TD]
[TD]475559[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]GDHC[/TD]
[TD]475565[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]SH01[/TD]
[TD]620066[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]SW39[/TD]
[TD]MRP026[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]S5CH[/TD]
[TD]A35003[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]S5CH[/TD]
[TD]A35003[/TD]
[TD]JC[/TD]
[/TR]
</tbody>[/TABLE]

(Duplicates is fine)

I hope this clears things up or I can try to clarify further if need be. Thank you for taking the time to look at my question.

 

[Micron]

OK, that's entirely different than what I thought this meant: at least one of the status codes not JC
I will have to think it over. In the meantime, maybe someone else who's way smarter than me will jump in. Goodness knows there's plenty of those souls out there.

 

[Micron]

OK, that's entirely different than what I thought this meant: at least one of the status codes not JC
I will have to think it over. In the meantime, maybe someone else who's way smarter than me will jump in. Goodness knows there's plenty of those souls out there.

Also, does that mean you'd also eliminate these (just so I understand the constraints)
GMFQ because one is M5 and the other is M8?
That for SC22, you'd eliminate DDHO27, DDHO35, DDHO129, DDHO158?

I know I could examine your reposted results but I'm scanning a table here and just want to be sure. I didn't go through all of it; I figured those would be enough examples.

 

[Gabbelgak]

That is correct.

For GMFQ we only have 1 Item, PTG513, since it has a single status that is not JC (it has one M5 and one M8) I don't want to see any of these.

For SC22 we have 7 different items, 130666, DDH027, DDH035, DDH101, DDH129, DDH150, DDH158 - None of these have a JC so we can eliminate all of them. But to be extra clear, let's say 13066 was JC and not M3 - I would want to see SC22 130666 JC and none of the other 6 SC22's.

 

[Micron]

Between my last and this post, I came up with something, but it was finished before I saw your last post. I can only do it with 3 queries:

create 2 TOTALS queries:
- concatenate supplier & item to create a unique identifier (sort of a grouping) where status is JC and calculate counts for these groups
- in a 2nd query, concatenate again from the main table and get a count of the status for these groups
- in 3rd query, join 1 & 2 WHERE group = group AND status count = group count. Include main table to show status JC, because you can't filter on JC as well as include it in the results in a Totals query (i.e. it can't be criteria and as well as perform an aggregate function on it).
The logic is, if there are 3 counts of a group and a 3-count status for that group, the status must be the same. Otherwise, the status count would be different than the count of a group. I could see that early on, but it took a LOT of playing around to get the result I could envision. Probably is easier to read than understand. I think if you run the first 2 queries separately and in your table, filter to JC and sort by Supplier then Item and compare what the queries show, it might make more sense. The output is
[TABLE="width: 192"]
<tbody>[TR]
[TD]Supplier[/TD]
[TD]Item[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]GDHC[/TD]
[TD]475559[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]GDHC[/TD]
[TD]475565[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]SH01[/TD]
[TD]620066[/TD]
[TD]JC[/TD]
[/TR]
[TR]
[TD]SW39[/TD]
[TD]MRP026[/TD]
[TD]JC[/TD]
[/TR]
</tbody>[/TABLE]

Hope that is what you want - it seems to jive with your posted table of results. If you need to adapt this for other status' then I think you can probably figure out how to tweak it.

 

[Micron]

Whooops! forgot to post the sql. Adding a new post because I may run out of time to edit the prior one.

GROUP COUNT

SELECT [supplier] & [Item] AS SuplrItem, Count([supplier] & [Item]) AS SuplrItemCnt, tblSuplrStatus.Supplier, tblSuplrStatus.Item
FROM tblSuplrStatus
WHERE (((tblSuplrStatus.Status)="JC"))
GROUP BY [supplier] & [Item], tblSuplrStatus.Supplier, tblSuplrStatus.Item;

STATUS COUNT

SELECT [supplier] & [Item] AS SuplrItem, Count(tblSuplrStatus.Status) AS CountOfStatus
FROM tblSuplrStatus
GROUP BY [supplier] & [Item];

RESULT

SELECT qryGroupCnt.Supplier, qryGroupCnt.Item, tblSuplrStatus.Status
FROM tblSuplrStatus INNER JOIN (qryGroupCnt INNER JOIN qryStatusCnt ON 
(qryGroupCnt.SuplrItem = qryStatusCnt.SuplrItem) AND 
(qryGroupCnt.SuplrItemCnt = qryStatusCnt.CountOfStatus)) ON 
(tblSuplrStatus.Item = qryGroupCnt.Item) AND (tblSuplrStatus.Supplier = qryGroupCnt.Supplier);

I put line wraps in the sql lines because they were going beyond rs of screen, so watch out for any missing spaces.

 

[Gabbelgak]

Thank you so much! That's exactly what I was looking for.

 

[Micron]

Glad it worked. Good luck with your project