Find only homogenous (1 Viewer)

[Ipem]

Hello guys,

I'm sure there is a common solution for this, but I can't figure it out, and do not even have a clue what to search for

I want to find rows in which a value in a field occurs only together with a certain value in an another. For example, in the table below I want to find only A and C, since they have only "Q" lines.

Field 1|Field1
----------------
A | Q
A | Q
B | Q
B | M
B | Q
C | Q

How do I do this?
Thanks!

 

[Uncle Gizmo]

B has a Q ?????

please explain....???

 

[plog]

I have the answer for you, just be warned: your generic example is my specific example. I solved the problem for the exact situation you presented. If you want specific help for a specific problem, you need to give us your specific problem.

With that said, this query will provide you with exactly what you asked for:

SELECT YourTableNameHere.[Field 1]
FROM YourTableNameHere
GROUP BY YourTableNameHere.[Field 1]
HAVING (((IIf(Count([Field 2])=Sum(IIf([Field 2]="Q",1,0)),True,False))=True));

Be sure to replace instances of 'YourTableNameHere' with the name of your table.

 

[Ipem]

Sorry guys for the delay, I'm in Europe, so my work hours were over.

Uncle Gizmo: Yes B has a Q. To explain, let me use real world names for those fields: I have orders, with several items. All items has a status, like: "cancelled" or "ready for shipping". I want to find those orders of which each and every lines are "ready for shipping"

Plog: Thank you, your solution worked fine form me!

 

[Uncle Gizmo]

Ah! I get it...

Cheers