A
Allen_N
I want to build a table containing counts of items common to pairs of sets,
but I don't know how to build the SQL query. The essential data in the source
table looks something like:
set item
---- -----
set1 item1
set1 item2
set1 item3
set2 item4
set2 item5
... ...
setN itemM
There will be many cases in which a given value of 'item' is associated with
various values of 'set', and I want to count the number of these redundancies
for each pair of sets in the table. (The output table will be N x N, to
handle supersets as well as subsets.)
I just tried this:
SELECT COUNT (1) FROM [intable] INNER JOIN [intable] AS intable2 ON
[intable].Item = intable2.Item AND intable.set <> intable2.set
but the query wouldn't stop running. (There are ~60,000 records, comprising
~10,000 sets, so maybe I just have to be patient.) I don't think my syntax is
right, and I may even have created a monster. Maybe I have to populate 1
output cell at a time, in a VB loop?
but I don't know how to build the SQL query. The essential data in the source
table looks something like:
set item
---- -----
set1 item1
set1 item2
set1 item3
set2 item4
set2 item5
... ...
setN itemM
There will be many cases in which a given value of 'item' is associated with
various values of 'set', and I want to count the number of these redundancies
for each pair of sets in the table. (The output table will be N x N, to
handle supersets as well as subsets.)
I just tried this:
SELECT COUNT (1) FROM [intable] INNER JOIN [intable] AS intable2 ON
[intable].Item = intable2.Item AND intable.set <> intable2.set
but the query wouldn't stop running. (There are ~60,000 records, comprising
~10,000 sets, so maybe I just have to be patient.) I don't think my syntax is
right, and I may even have created a monster. Maybe I have to populate 1
output cell at a time, in a VB loop?