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

CodePudding user response：

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 count(distinct ItemNumber) > 1

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

CodePudding user response：

You can also use: this SQL (or similar):

select IPA.ItemName, IPA.ItemProperty, IPB.ItemName as CommonWithThisItem
from   ItemProperties as IPA
           inner join
           ItemProperties as IPB
           on IPA.ItemName>IPB.ItemName
                 and IPA.ItemProperty=IPB.ItemProperty

The greater than criteria used forces only one of the two combinations to be returned (i.e. say, the item names having a comoon property are ItemA and ItemB; normally both ItemA, ItemB, and ItemB, ItemA would be valid returns for your query). If this is not what you want, change the > criteria to <>