Is it possible and how can it be done?
in [Queries]
|
From: Hallgeir on 10 Mar 2010 05:39 I have a table looking like this: Id TaskLeader PreviousId .... 4 Hans 2 5 Kari 7 6 John 5 7 Jim 4 8 Kari 6 I'm wondering if it's possible to make a query that give me this result below (and of course if it's possible; how) ?: Id Leader Previous Leader 8 Kari John 6 John Kari 5 Kari Jim 7 Jim Hans .... Appreciate any suggestions, Thanks!
From: Stefan Hoffmann on 10 Mar 2010 06:13 hi, On 10.03.2010 11:39, Hallgeir wrote: > I have a table looking like this: > Id TaskLeader PreviousId > 4 Hans 2 > 5 Kari 7 > > I'm wondering if it's possible to make a query that give me this result > below (and of course if it's possible; how) ?: > > Id Leader Previous Leader > 8 Kari John > 6 John Kari Use DLookup() SELECT Id, TaskLeader AS Leader, DLookup("TaskLeader", "yourTable", "id = " & PreviousId) AS [Previous Leader] FROM yourTable or a self-join SELECT T1.Id, T1.TaskLeader AS Leader, T2.TaskLeader AS [Previous Leader] FROM yourTable T1 LEFT JOIN yourTable T2 ON T2.Id = T1.PreviousId I'd prefer the self-join, cause it is the faster solution. mfG --> stefan <--
From: RonaldoOneNil on 10 Mar 2010 06:59 Create a query in desgn view and add your table twice. Create a Join from previousID in the first table to ID in the second table. Add into your query grid, ID and TaskLeader from the first table and TaskLeader from the second table. "Hallgeir" wrote: > I have a table looking like this: > Id TaskLeader PreviousId > ... > 4 Hans 2 > 5 Kari 7 > 6 John 5 > 7 Jim 4 > 8 Kari 6 > > I'm wondering if it's possible to make a query that give me this result > below (and of course if it's possible; how) ?: > > Id Leader Previous Leader > 8 Kari John > 6 John Kari > 5 Kari Jim > 7 Jim Hans > ... > > Appreciate any suggestions, Thanks!
From: Hallgeir on 10 Mar 2010 08:28 Hi guys, your self join solutions returns correctly records, but it's not sorted the way I was looking for. My table contains a kind of nesting where one record points back to another one. I want the resulting query to start with the latest record and then continue with the record PreviousId is pointing to, and so on. Previous leader should always be leader on the record below. Like this: Id Leader Previous Leader 8 Kari John 6 John Kari 5 Kari Jim 7 Jim Hans Still don't know if it's possible, but appreciate any suggestions.
From: Stefan Hoffmann on 10 Mar 2010 08:55
hi, On 10.03.2010 14:28, Hallgeir wrote: > Hi guys, your self join solutions returns correctly records, but it's not > sorted the way I was looking for. My table contains a kind of nesting where > one record points back to another one. I want the resulting query to start > with the latest record and then continue with the record PreviousId is > pointing to, and so on. Previous leader should always be leader on the record > below. Why haven't you posted this little piece of information in the first message? Google for 'adjacency list'... Basically you cannot do this in a single or simple query. The most common approach here is to add an extra column tracking the level of your node. The level itself can be calculated either using recursion for the entire tree or by a simple lookup: parent level + 1 when inserting or moving the node. mfG --> stefan <-- |