VBA SQL Intersect replacement for datasets from the same table. (MS Access)

I believe ‘Intersect’ is the solution to my problem, but implementing it in VBA is throwing me..

I have two tables:

1. Items, for our purposes a single column of [ItemNumber]
2. ItemProperties, 2 columns [ItemNumber],[ItemProperty]

Each item as at least (no less then) 2 properties [ItemProperty] and as many as 700 properties.

For each Item in the Items table, I want to compare it with every other Item in the Item Table and see if they have any Properties in common.

My thought was to use:

Reading through Items with two loops. the first loop to select the current item [FristItem], the second to select what item I am comparing to it [SecondItem]

Select ItemProperty from ItemProperties where ItemNumber = FirstItem
Intersect
Select ItemProperty from ItemProperties where ItemNumber = SecondItem

That,I believe will give me the properties in common between FirstItem and SecondItem.

But MS Access VBA does not implement Intersect. The discussions I have seen talk about how to use INNER JOIN to accomplish the same thing, but always uses 2 different Tables as the sources.. When I write it as from a single table, I seem to get ambiguous identifies on the ON part (same identifier on both sides..)

I am a relative beginner at SQL and am sure the answer is simple and obvious, but I am just missing it today. In the end I need to do something based on the number of common properties each pair has, but I think I have that part worked out.. Just need help with the Intersect / INNER JOIN..

Thank you anyone who can point me in the right direction

dave

Answer

You can do a GROUP BY instead. Use HAVING to make sure both FirstItem and SecondItem are there.

Select ItemProperty
from ItemProperties
where ItemNumber in (FirstItem, SecondItem)
having max(ItemNumber) <> min(ItemNumber)

self join alternative:

select distinct i1.ItemProperty
from ItemProperties i1
join ItemProperties i2
  on i1.ItemProperty = i2.ItemProperty
where i1.ItemNumber = FirstItem
  and i2.ItemNumber = SecondItem