Access query

[Cmaz]

Table Groups:
GroupId Group Name MasterId
1 AAA 0
2 BBB 0
3 CCC 7
4 DDD 2
6 FFF 1
7 GGG 0
8 HHH 3


How to get all Groupname of masterId using query |

|

\/

GroupId Group Name MasterId NameOfMaster
1 AAA 0 AAA
2 BBB 0 BBB
3 CCC 7 GGG
4 DDD 2 BBB
6 FFF 1 AAA
7 GGG 0 GGG
8 HHH 3 CCC

 

[Tom Ellison]

Dear Cmax:

As is common when you have a self-referencing table (your MasterId column
references the GroupId, right) the query will need to operate on two copies
of the same table. This requires aliasing and a self-join. It could look
like this:

SELECT T.GroupId, T.GroupName, T.MasterId,
NZ(T1.GroupName, 0) AS NameOfMaster
FROM YourTable T
LEFT JOIN YourTable T1
ON T1.GroupId = T.MasterId

This is what you get for having just 2 levels of self-join. I've seen 5!
The table is self-joined 4 times for that.

You need to replace YourTable above with the actual name of the table.
Hopefully, no other change is needed.

Iterative self-referencing of tables is not something for which the SQL
language is especially well suited. I hope that improves with time.

Tom Ellison

 

[John Spencer]

Dear Tom and Cmax,

I think there might be one change needed in the query Tom Ellison proposed
to return the values Cmax indicated.

SELECT T.GroupId, T.GroupName, T.MasterId,
NZ(T1.GroupName, T.GroupName) AS NameOfMaster
FROM YourTable T
LEFT JOIN YourTable T1
ON T1.GroupId = T.MasterId

John Spencer

 

[Tom Ellison]

True, true!

I was looking at the MasterId column instead of the NameOfMaster column when
I picked up the default.

Thanks, John!

Tom

 

[Cmaz]

Thank you Tom, for the quick response.

and
Thank you John, for leading us in the right direction.

This is what you get for having just 2 levels of self-join. I've seen 5!
The table is self-joined 4 times for that.

Tom , I tried subgroups

GroupId Group Name MasterId NameOfMaster
50 NNNN 0 NNNN
52 MMMMMMM 0 MMMMMMM
54 OOOOOO 0 OOOOOO
51 PPPPP 50 NNNN
53 QQQQQQQ 50 NNNN
55 KKKKK 52 MMMMMMM
56 HHHHHHH 55 KKKKK
57 WWWWWW 56 HHHHHHH
58 AAAAA 57 WWWWWW
59 GGGGGG 58 AAAAA

its looks OK,
when do you need selfjoin > 2 times

 

[Tom Ellison]

Dear Cmaz:

The concept can be illusive. You have to be able to THINK the way the
software does.

Basically, any time you need access to two different rows in your table,
simultaneously, then you have a self-join. Or, in a hierarchy, you may need
access to 3, 4, or more different rows simultaneously. When this happens,
and you've realized the fact, you need to know which row is the one you know
about first, and make the others successively dependent. Then just build
the JOINs accordingly.

Sounds simple, but if you stumble trying to grasp it, it can be confounding.
I recommend you not struggle, but try to visualize it, even sketching what
is going on. Or write the relevant column values of a row across a piece of
paper, and cut it out to represent a row of data. Do this for the related
rows as well. Now arrange these scraps on the table the way the function
together. Sometimes, I think of the data in this way, being flexibly
arranged. The query code tells how we are to arrange them.

If it helps, think of an outline form, with each subsidiary row in the
hierarchy being indented from its "parent" row. Indeed, I have often
arranged a report where the first column IS indented like this. It's really
a way humans are accustomed to seeing this. Your data would look like:

MMMMMMM
KKKKK
HHHHHHH
WWWWWW
AAAAA
GGGGGG
NNNN
PPPPP
QQQQQQQ
OOOOOO

I have found the above appearance to be the best way to display the data to
humans. You have a maximum of 6 levels of hierarchy built in. A 6 way
UNION query will build a key value on which you can sort, and can assign the
indentation level for you, and you can get this on paper as shown above,
with other columns (not indented) showing any details stored with the data.

There are a couple of tricks to being able to do this well. PLEASE DO NOT
TRY THIS AT HOME! Well, you could, of course. Heck, I had to figure it out
for myself at one time. Well, somebody had to do the dirty work!

Tom Ellison