Returning Field Value on Matching All Records

[Lee Mac]

Conceptually, I have the following two tables:

Table 1:

Group | Item
------+------
  A   |   1
  A   |   2
  A   |   3
  A   |   4
  B   |   1
  B   |   2
  B   |   5
  C   |   1
  C   |   2
  C   |   3

Table 2:

Item
-----
  1
  2
  3

I'm looking to return only Groups which contain all Items in Table 2.

For example, for the above, the query would return:

Group
-----
  A
  C

As these groups contain Items 1, 2 & 3.

I was initially thinking something along these lines:

SELECT Table1.Group
FROM Table1 INNER JOIN Table2 ON Table1.Item = Table2.Item
GROUP BY Table1.Group
HAVING Count(Table1.Item) = DCount("Item","Table2")

But this seems clunky and inefficient - is there a better way?

Thank you in advance for your time!

Lee

 

[sxschech]

Doesn't this provide the result?

SELECT Table1.Group
FROM Table2 INNER JOIN Table1 ON Table2.Item = Table1.Item
GROUP BY Table1.Group;

Based on your example returns:
A
B
C

You said you only show A and C, but your table also contains a 1 and a 2 for B and that is why when I run it, B is included.

 

[bob fitz]

Doesn't this provide the result?

SELECT Table1.Group
FROM Table2 INNER JOIN Table1 ON Table2.Item = Table1.Item
GROUP BY Table1.Group;

Based on your example returns:
A
B
C

You said you only show A and C, but your table also contains a 1 and a 2 for B and that is why when I run it, B is included.

You haven't included the last line:

HAVING Count(Table1.Item) = DCount("Item","Table2")

 

[isladogs]

@sxszech
Your version is incorrect as group B doesn't include item 3.
The rule was - must include all values from table 2

 

[sxschech]

Sorry, did not interpret the statement properly. Still not fully awake today.

I'm looking to return only Groups which contain all Items in Table 2.