Convert Table Rows into Columns (Chart of Accounts)

[TezzaB]

Hi,

I have an access database table of accounting code information (Chart of Accounts essentially) that has reporting level information in rows that I want to convert to columns in a new access table (which I ultimately want to use in excel pivot reporting) . Example of the table I have and what I am trying to create is below. There are 4 reporting levels 1 - 4. An account with a ReportLevel 1 will always be a header. An account with a ReportLevel 4 will never be a header. An account with ReportLevel 2 or 3 may or may not be a header. If an account isn't a header, then there will be no further levels below it. (ie. if an account is ReportLevel 3 and is not a header, then there will not be a ReportLevel4 applicable to that account). I want to produce a table that shows me the account number of all the reporting levels applicable to any given account. The existing table only gives you the immediate parent account number, but not the parent levels above that (if any). Hopefully the pictures make it clear what I'm on about! I really not sure where to start - hoping someone can point me in the right direction.



thanks

[Micron]

Will the number of report levels change? This info could help anyone proposing a solution, because a crosstab query might be the route to go, but it's field structure cannot change when you use it. Regardless, I probably could not solve this, but I think the info would be needed by anyone who can.

[ssanfu]

Given the test data, and not knowing what the field types are, how close is this? (VBA solution)

[John_G]

I don't think it can be done without using VBA. Each row in the Source table results in one row in the target table, so that makes it easier.

What you would have to do is for each row in the Source, create a new row in the Target, copy the first two columns, then using the level number in the source as the starting point, step backwards up the levels to fill in the other columns.

So, if the level number is 4, you know what ReportLevel4 is, and you know what ReportLevel3 is. ReportLevel2 would be the parent account of ReportLevel3, and ReportLevel1 would be the parent account for ReportLevel2. The DLookup function would work fine for finding the parent accounts from the Source table.

You only have 4 levels, and levels 1 and 2 are trivial (no lookups needed). It's not difficult programming for the other 2 levels.

What level of expertise to you have with VBA?

[TezzaB]

Thanks for your prompt replies while I slept overnight . Yes the number of reporting levels is fixed at 4. I have a moderate knowledge of VBA but this is taking me to new areas.
ssanfu's solution seems to work perfectly with the test data and I will now attempt to incorporate that into my existing database.
Thanks so much for the help.