How to construct a hierarchy from a list? (Access 2003)

Greetings,

Using Access 2003, my objective is to turn a list like this:

Asset ID | Parent Asset ID
1 | NULL
2 | 1
3 | 2
4 | 3
Etc...

Into a table like this:

Asset ID | Parent 1 | Parent 2 | Parent 3 | Etc...
1 | NULL | NULL | NULL |
2 | 1 | NULL | NULL |
3 | 2 | 1 | NULL |
4 | 3 | 2 | 1 |

Any suggestions how to achieve this objective are greatly appreciated.

A similar question was posted in the Access Database Queries Forum on
5/5/2008, titled: Implementing a Hierarchy in MS Access. According to the
responses this can only be done using VBA, which I am just learning, so a
little assistance with code writing would be greatly appreciated also.

Thank you.

Markeyjd2 -

You can acutally do this with just queries, or with VBA code. Create your
second table with the Asset ID as the primary key.

Your first query will just copy all the records from your Assets table into
the second table, populating Asset ID and Parent 1. It will look something
like this (use your table names):

INSERT INTO AssetHierarchy ( [Asset ID], [Parent 1] )
SELECT Assets.[Asset ID], Assets.[Parent Asset ID]
FROM Assets;

(You can also copy/paste these in, but if you want to create a process, then
build the query so you can run it whenever you need.)

Your second query will populate the Parent 2 column. It will look like this
(again, use your table names):
UPDATE Assets INNER JOIN AssetHierarchy ON Assets.[Asset ID] =
AssetHierarchy.[Parent 1] SET AssetHierarchy.[Parent 2] = [Parent Asset ID];

Build as many of these as needed, each level increasing the Parent levels by
one.

--
Daryl S


"markeyjd2" wrote:

Greetings,

Using Access 2003, my objective is to turn a list like this:

Asset ID | Parent Asset ID
1 | NULL
2 | 1
3 | 2
4 | 3
Etc...

Into a table like this:

Asset ID | Parent 1 | Parent 2 | Parent 3 | Etc...
1 | NULL | NULL | NULL |
2 | 1 | NULL | NULL |
3 | 2 | 1 | NULL |
4 | 3 | 2 | 1 |

Any suggestions how to achieve this objective are greatly appreciated.

A similar question was posted in the Access Database Queries Forum on
5/5/2008, titled: Implementing a Hierarchy in MS Access. According to the
responses this can only be done using VBA, which I am just learning, so a
little assistance with code writing would be greatly appreciated also.

Thank you.

Daryl,

Thank you. The query language works perfectly when I copy in my table/field
names. Thanks also for your help with the SQL and making things very easy to
understand.

"Daryl S" wrote:

Markeyjd2 -

You can acutally do this with just queries, or with VBA code. Create your
second table with the Asset ID as the primary key.

Your first query will just copy all the records from your Assets table into
the second table, populating Asset ID and Parent 1. It will look something
like this (use your table names):

INSERT INTO AssetHierarchy ( [Asset ID], [Parent 1] )
SELECT Assets.[Asset ID], Assets.[Parent Asset ID]
FROM Assets;

(You can also copy/paste these in, but if you want to create a process, then
build the query so you can run it whenever you need.)

Your second query will populate the Parent 2 column. It will look like this
(again, use your table names):
UPDATE Assets INNER JOIN AssetHierarchy ON Assets.[Asset ID] =
AssetHierarchy.[Parent 1] SET AssetHierarchy.[Parent 2] = [Parent Asset ID];

Build as many of these as needed, each level increasing the Parent levels by
one.

--
Daryl S


"markeyjd2" wrote:

Greetings,

Using Access 2003, my objective is to turn a list like this:

Asset ID | Parent Asset ID
1 | NULL
2 | 1
3 | 2
4 | 3
Etc...

Into a table like this:

Asset ID | Parent 1 | Parent 2 | Parent 3 | Etc...
1 | NULL | NULL | NULL |
2 | 1 | NULL | NULL |
3 | 2 | 1 | NULL |
4 | 3 | 2 | 1 |

Any suggestions how to achieve this objective are greatly appreciated.

A similar question was posted in the Access Database Queries Forum on
5/5/2008, titled: Implementing a Hierarchy in MS Access. According to the
responses this can only be done using VBA, which I am just learning, so a
little assistance with code writing would be greatly appreciated also.

Thank you.

Happy to help! :-)
--
Daryl S


"markeyjd2" wrote:

Daryl,

Thank you. The query language works perfectly when I copy in my table/field
names. Thanks also for your help with the SQL and making things very easy to
understand.

"Daryl S" wrote:

Markeyjd2 -

You can acutally do this with just queries, or with VBA code. Create your
second table with the Asset ID as the primary key.

Your first query will just copy all the records from your Assets table into
the second table, populating Asset ID and Parent 1. It will look something
like this (use your table names):

INSERT INTO AssetHierarchy ( [Asset ID], [Parent 1] )
SELECT Assets.[Asset ID], Assets.[Parent Asset ID]
FROM Assets;

(You can also copy/paste these in, but if you want to create a process, then
build the query so you can run it whenever you need.)

Your second query will populate the Parent 2 column. It will look like this
(again, use your table names):
UPDATE Assets INNER JOIN AssetHierarchy ON Assets.[Asset ID] =
AssetHierarchy.[Parent 1] SET AssetHierarchy.[Parent 2] = [Parent Asset ID];

Build as many of these as needed, each level increasing the Parent levels by
one.

--
Daryl S


"markeyjd2" wrote:

Greetings,

Using Access 2003, my objective is to turn a list like this:

Asset ID | Parent Asset ID
1 | NULL
2 | 1
3 | 2
4 | 3
Etc...

Into a table like this:

Asset ID | Parent 1 | Parent 2 | Parent 3 | Etc...
1 | NULL | NULL | NULL |
2 | 1 | NULL | NULL |
3 | 2 | 1 | NULL |
4 | 3 | 2 | 1 |

Any suggestions how to achieve this objective are greatly appreciated.

A similar question was posted in the Access Database Queries Forum on
5/5/2008, titled: Implementing a Hierarchy in MS Access. According to the
responses this can only be done using VBA, which I am just learning, so a
little assistance with code writing would be greatly appreciated also.

Thank you.