counting common values of a field between 2 super-fields

[Guest]

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?

 

[strive4peace]

Hi Allen,

try this:

SELECT Set, Item, Count(*) as NumSets
FROM tablename1
GROUP BY Set, Item

if this is not what you are after, please show an example with the way
you want your results to look

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal,

That didn't do it, but thanks for replying. In the end, I decided that SQL
was way too slow for this part of the job. Instead, I opened a recordset of
the entire table and assigned all of its fields and records to an array.
Then, I did all the comparisons on this RAM image of the table, aided by the
use of pointers to the 1st record of each set. (The table was pre-sorted to
allow this.) The speed increase was at least 100 X, maybe even 1,000 X.

{I don't always get it right on the first go, but I usually do on the last!}

Hi Allen,

try this:

SELECT Set, Item, Count(*) as NumSets
FROM tablename1
GROUP BY Set, Item

if this is not what you are after, please show an example with the way
you want your results to look

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

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?

 

[strive4peace]

Hi Allen,

I am glad you got something to work Sounds like a good solution ...
wish you continued success with your database.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

That didn't do it, but thanks for replying. In the end, I decided that SQL
was way too slow for this part of the job. Instead, I opened a recordset of
the entire table and assigned all of its fields and records to an array.
Then, I did all the comparisons on this RAM image of the table, aided by the
use of pointers to the 1st record of each set. (The table was pre-sorted to
allow this.) The speed increase was at least 100 X, maybe even 1,000 X.

{I don't always get it right on the first go, but I usually do on the last!}

Hi Allen,

try this:

SELECT Set, Item, Count(*) as NumSets
FROM tablename1
GROUP BY Set, Item

if this is not what you are after, please show an example with the way
you want your results to look

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

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?