Access Make Table Query 1

[rradelet]

I am trying to build a make table query in Access 2000,
but don't really know how to start with this one.

Starting with a table that is composed of two fields:

ID: Long Integer
Desc: Text

Sample of Data:

ID Desc
1 1A
2 2A
3 3A
4 3B
5 4A


I want to make a table that will list combinations of the ID field data and list the corresponding Desc fields . For example, with the 5 records above,
there are 15 combinations:

1 1
1 2
1 3
1 4
1 5
2 2
2 3
2 4
2 5
3 3
3 4
3 5
4 4
4 5
5 5

I want to make a table with the following result:

ID1 ID2 Desc1 Desc2
1 1 1A 1A
1 2 1A 2A
1 3 1A 3A
1 4 1A 3B
1 5 1A 4A
2 2 2A 2A
2 3 2A 3A
2 4 2A 3B
2 5 2A 4A
3 3 3A 3A
3 4 3A 3B
3 5 3A 4A
4 4 3B 3B
4 5 3B 4A
5 5 4A 4A

Note: My actual data will have up to 200 or 300 ID records
and the ID data may not always be consecutive (i.e
1 2 3 4 5 8 9 10 13 14 15 25 etc).

Can anyone help get me started?

 

[SkipVought]

Hi,

What you have is a cartesian join, using the following SQL in Excel, where Sheet1 has the source data table...
[tt]
SELECT A.ID, B.ID, A.`Desc`, B.`Desc`

FROM
`C:\dbCartesian.xlsx`.`Sheet1$` A
, `C:\dbCartesian.xlsx`.`Sheet1$` B

WHERE A.ID>=B.ID
[/tt]

[tt]
ID1 ID2 Desc1 Desc3
1 1 1A 1A
2 1 2A 1A
3 1 3A 1A
4 1 3B 1A
5 1 4A 1A
2 2 2A 2A
3 2 3A 2A
4 2 3B 2A
5 2 4A 2A
3 3 3A 3A
4 3 3B 3A
5 3 4A 3A
4 4 3B 3B
5 4 4A 3B
5 5 4A 4A
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rradelet]

Thank you Skip.

I was able to create what I needed.

I changed the Select statement from:

SELECT A.ID, B.ID, A.`Desc`, B.`Desc`

to

SELECT B.ID, A.ID, B.`Desc`, A.`Desc`

and was able to produce data that matched my sample result
exactly.

I have never used MS Query before, but now see where it will
probably come in handy in the future.

Thanks again

RR