Query to join records according to "hierarchy"

[sergio.pringle]

My table is something like this:

Fields: Name; Group
John; 1-1
Carl; 1-1
Brian; 1-2
Charles; 2-1
Eddie; 2-2
Carmen; 3-1
Ellen; 3-2
......and so on

What I need is to combine the field Name in group 1-1 to 1-2, 2-2,
3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2.

Is this possible? If so, how can I do it?

The results from the query would be something like this:

Name A Name B Name C Name D
John(1-1) Brian(1-2) Eddie(2-2) Ellen(3-2)
Carl(1-1) Brian(1-2) Eddie(2-2) Ellen(3-2)
Charles(2-1) Eddie(2-2) Ellen(3-2)
Carmen(3-1) Ellen(3-2)

Basically every name in group1-1 can be together with group1-2
group2-2 and group3-3. Every name in group 2-1 can only be together
with group2-2 and group3-2... and group 3-1 can only be together with
3-2

thanks for the help.

 

[David Cox]

The first stage is to put the table twice into the query design window. Do
not join them.

something like yourtable, and yourtable_1

as fields pick yourtable.name , yourtable.name_1.name , yourtable_1.group

where yourtable_1.group > yourtable.group

order by yourtable_1.group ascending.

Use the output of that query as the input to a crosstab to get the table
format that you desire.

 

[Chris2]

My table is something like this:

Fields: Name; Group
John; 1-1
Carl; 1-1
Brian; 1-2
Charles; 2-1
Eddie; 2-2
Carmen; 3-1
Ellen; 3-2
......and so on

What I need is to combine the field Name in group 1-1 to 1-2, 2-2,
3-2. Then 2-1 with 2-2, 3-2. Then 3-1 with 3-2.

Is this possible? If so, how can I do it?

The results from the query would be something like this:

Name A Name B Name C Name D
John(1-1) Brian(1-2) Eddie(2-2) Ellen(3-2)
Carl(1-1) Brian(1-2) Eddie(2-2) Ellen(3-2)
Charles(2-1) Eddie(2-2) Ellen(3-2)
Carmen(3-1) Ellen(3-2)

Basically every name in group1-1 can be together with group1-2
group2-2 and group3-3. Every name in group 2-1 can only be together
with group2-2 and group3-2... and group 3-1 can only be together with
3-2

thanks for the help.

Sergio Pringle,

You are discussing the materialized path hierarchy (also called path enumeration).

This is not a simple subject.

I don't know where you can find a good MS Access-based discussion of it, but this article
is still good. (You would need to adapt the examples for MS Access.)

http://www.dbazine.com/oracle/or-articles/tropashko4

Nested sets will get you there, too, but you would need a different table schema.

Joe Celko's book, Trees and Hierarchies, has information on this, as well. His book
points out that the Itzik Ben-Gan/Tom Moreau book, Advanced Transact-SQL for SQL Server
2000 (chapter 16), is a more extensive source of materialized path information. (You'd
have to adapt examples from either book, as well.)


Sincerely,

Chris O.