How to construct a hierarchy from a list? (Access 2003)
in [General]
|
Prev: Change lblHeading.Caption of report based on which form's button clicked
Next: Calculating dates based on two dates from 24 hours
From: markeyjd2 on 27 May 2010 15:53 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.
From: Daryl S on 27 May 2010 16:37 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. >
From: markeyjd2 on 27 May 2010 17:58 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. > >
From: Daryl S on 27 May 2010 17:59 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. > > >
From: ���� on 30 May 2010 22:04
111111111111111111111111111111111111111 "markeyjd2" <markeyjd2(a)discussions.microsoft.com> д����Ϣ����:9F99E940-BA43-4F3B-969B-44A4E3EAF9BE(a)microsoft.com... > 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. > |