troubleshooting Question

Convert Access Col values to rows (Access 2010)

Avatar of thelton_us
thelton_us asked on
Microsoft AccessTax / Financial Software
5 Comments2 Solutions448 ViewsLast Modified:
I am currently linked via ODBC to the Peachtree Accounting Chart table which contains the debits, credits and total by month values for each account.  The table is arrayed with columns:
 AccountID  Balance0Dr  Balance0Cr  Balance0Net  Balance1Dr  Balance1Cr  Balance1Net
12345              100.00       -210.00       -110.00             321.00        -20.00          301.00
87654                  0.00        -30.00         -30.00             130.00         -40.00          90.00

 Each Balance column corresponds to the accounting activity for the periods 0, 1, 2, etc.  I am wanting to take the values in these columns, transpose the array, and create a table where the columns would be:

AccountID            BalRef      Amount
12345           Balance0Dr       100.00
12345           Balance0Cr        -210.00
12345           Balance0Net      -110.00
12345           Balance1Dr        321.00
12345           Balance1Cr         -20.00
12345           Balance1Net       301.00
87654           Balance0Dr          0.00
87654           Balance0Cr          -30.00
87654           Balance0Net       -30.00
87654           Balance1Dr         130.00
87654           Balance1Cr          -40.00
87654           Balance1Net        90.00

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros