Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    21

    Unanswered: Compare Variable Number of Columns to Fixed Number of Columns

    Background

    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.

    Dilemma
    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 unsolved solution.

    If one of you database experts agree, then I would like to know how to convert columns into rows. Due to the numerous variable Fund Columns, I do not know how to 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!

  2. #2
    Join Date
    Aug 2003
    Location
    Newbury Berks
    Posts
    6
    Hi Tim, I'm not 100% sure of the nature of your problem, but you've done a very nice presentation! I made a tool a few years ago for transposing the wrong sort of table (spreadsheet like) into something more suitable for MS Access and this is the link:-

    https://sites.google.com/site/msacce...-access-part-1

    there are various articles in text and pictures, and one article as videos. There is also a demonstration file you can download.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    How many different account numbers do you expect. If you have hit the 255 limit then you need to be a bit more creative, or use a different backend which can suppirt the number of columns you need
    Another approach would be to break the funds into logical pages. So, say fund 1...25 on page1, 26..50 page 2 etc. And treat each page as a sub report embedded in a top level report which also has the summarisation report.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2012
    Posts
    21
    I'm not certain if my two responses were sent and received yesterday, so I'll respond again.

    Hey Tony,

    I did find a useful idea from that link you provided, which is to construct one large table into two tables. Therefore, once a year I will import my Beginning Balance Table [0TBL-BB] containing over 200 fixed columns into two tables. I will then try to figure out a way to reformat each of my two tables with multiple columns into rows, which will end up containing four columns, illustrated as [TBL-BB-ReformColumnsToRows] on my original attachment. I’ll then append one table to the other.

    When I import (monthly or as needed) the various Ending Balance Tables [0TBL-EB] containing variable number of columns, I will also convert those columns into rows, illustrated as [TBL-EB-ReformColumnsToRows] on my original attachment. I will then link the Ending Balance Table to the Beginning Balance Tables based on two matching key fields.

    Now the question is: How do I automate converting variable column names (since the column names vary during each Ending Balance Spreadsheet import) into rows?


    ================================================== =====================

    Hi Healdem,

    I appreciate your feedback. If I split my database into a Front-End and Back-End, I’ll still run into the same problem, since the Back-End only contains the tables. So I’m not certain if I understand what you meant by using a “different” backend. Also, my end results, which weren’t mentioned, is to export two files into an Excel Spreadsheet containing Detailed and Summary information based on the difference between each Beginning and Ending Balance Funds.
    Thank you for responding! -Tim

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dufferent back meant yse a different SQL engine to store the data, one that doesnt have the JET SQL limit of around 250 items
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •