trouble with distinct combinations 1

[VickyC]

Greetings

I am asking for some help with the following problem...

Consider table tData

ID1   ID2   ID3    X
====================
 ...

 20    1     1    87
 20    2     1    62
 20    2     2    91
 20    2     3    32
 20    3     1    74
 20    3     2    17

 21    1     1    87
 21    2     1    62
 21    3     1    74
 21    3     2    17

 22  etc...

For each ID1, ID2 has value 1, 2 or 3. Repeats are allowed.
For each ID2, ID3 starts at 1 and increments by 1.

The PK is ID1, ID2, ID3.



Here's the problem. I need a query that produces all possible DISTINCT combinations of ID1 and ID2.
For ID1 = 20, there will be 6 sets of 3 records. For ID1 = 21, there will be 2 sets of 3 records.
The output would look like...

ID1   ID2     X
================
 ...

 20    1     87 
 20    2     62
 20    3     74

 20    1     87
 20    2     62
 20    3     17

 20    1     87
 20    2     91
 20    3     74

 20    1     87
 20    2     91
 20    3     17

 20    1     87 
 20    2     32
 20    3     74

 20    1     87
 20    2     32
 20    3     17


 21    1     87
 21    2     62
 21    3     74

 21    1     87
 21    2     62
 21    3     17

 etc...

Thanks in advance for any hints.

Vicky C.

 

[MajP]

See thread701-1673713 I identify how to to
combinations with replacement
combinations without replacement
permutations with replacement
permutations without replacement

 

[VickyC]

hi MajP - Thanks for the great reference. In the meantime, I had worked out a solution using recordsets in VBA, but I should now be able to develop an SQL solution.
Vicky