If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access query
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 |
#2
|
|||
|
|||
Access query
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 "Cmaz" wrote in message oups.com... 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 |
#3
|
|||
|
|||
Access query
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" wrote in message ... 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 "Cmaz" wrote in message oups.com... 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 |
#4
|
|||
|
|||
Access query
True, true!
I was looking at the MasterId column instead of the NameOfMaster column when I picked up the default. Thanks, John! Tom "John Spencer" wrote in message ... 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" wrote in message ... 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 "Cmaz" wrote in message oups.com... 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 |
#5
|
|||
|
|||
Access query
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 |
#6
|
|||
|
|||
Access query
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 "Cmaz" wrote in message oups.com... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Import query from access to excel, link to template, email on | jwr | Links and Linking | 11 | October 15th, 2005 05:25 PM |
subtraction | Alvin | Setting Up & Running Reports | 17 | September 29th, 2005 02:51 AM |
What is the difference between 2002 and 2003? | Red Sonya | General Discussion | 2 | March 1st, 2005 05:10 AM |
How to use a Access Query that as a parameter into Excel database query | Karen Middleton | Setting Up & Running Reports | 1 | December 13th, 2004 07:54 PM |
Merging MS Word document with MS Access 2002 criteria query | Doug Robbins | Mailmerge | 2 | November 4th, 2004 05:57 PM |