Is it possible and how can it be done?

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!

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 --

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!

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.

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 --

Thanks!

"Stefan Hoffmann" wrote:

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.

Sorry that I did not explain it well enough in my first post. Thank you for
your answear. This was the kind of answear I was looking for. Anything can be
solved, but obviously I can make it easier for my self designing a better
database.