Unanswered: Converting Numerous Column Values to Rows and Visa Versa
I am using Microsoft Access to automate a manual process performed by several accountants. My end results are actually a lot more complicated than what I am describing. For simplicity sake, here is my basic scenario:
Each fiscal year, I will import into my Access Database a large Beginning Trial Balance spreadsheet containing all SGL Account numbers that remains constant throughout the year. Periodically, accountants make updates as needed to their Ending Trial Balances whenever money amounts change in a designated Fund. When that occurs, I need to import the Ending Balance spreadsheet in my database.
Although there are currently 243 Funds, they are divided/assigned to six accountants. In other words, when I import, I can import one sixth of the assigned Beginning and Ending Trial Balance spreadsheets, which will reduce the number of SGLs and Funds comparisons.
In this example, I’ve only included 5 Funds in the Beginning Balance Table and 3 Funds in the Ending Balance Table.
I would like to know how to compare a variable number of columns from an Ending Balance Table to a fixed number of columns from a Beginning Balance Table. When the SGLAcct and Fund number fields match in both tables, I will subtract the Ending Balance from the Beginning Balance to obtain the difference.
Since the Funds will eventually exceed the 255 maximum column limit this year, I cannot use a table to reference all the Funds. The only partial solution that I know how to handle column-to-column type comparisons is to convert columns into rows. See attachment for my examples and solution.
If one of you database experts agree, then I would like to know how to convert columns into rows. Due to the numerous Fund Columns, I don’t know how I would use a Transpose and Pivot statement in a SQL statement without hard coding the Fund values. Likewise, I will need to know how to convert rows back into columns. Any assistance would be greatly appreciated!
Last edited by Tim-Morgan; 10-21-15 at 11:19.
Reason: Attachment Now Available