Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012

    Unanswered: Crosstab: put field names in values?

    Hi folks! I've got a spreadsheet people are using to enter values for each quarter. It looks something like this (apologies for the lack of formatting):

    ORIGIN	FY11Q1	FY11Q2	FY11Q3	FY11Q4	FY12Q1
    Eggs	11	33	66	77	
    Fruits	33	4	134	134	77
    Rocks				44	11
    What I'm trying to do is bring it into Access and get one row per Excel cell, creating new columns called FY_QTR and AMOUNT, like so:

    Eggs	FY11Q1	11
    Eggs	FY11Q2	33
    Eggs	FY11Q3	66
    Eggs	FY11Q4	77
    Eggs	FY12Q1	
    Fruits	FY11Q1	33
    Fruits	FY11Q2	4
    Fruits	FY11Q3	134
    Fruits	FY11Q4	134
    Fruits	FY12Q1	77
    Rocks	FY11Q1	
    Rocks	FY11Q2	
    Rocks	FY11Q3	
    Rocks	FY11Q4	44
    Rocks	FY12Q1	11
    So I want to repeat the value of ORIGIN across each row and convert the Excel column names for each fiscal year-quarter into the values for a new field, FY_QTR.

    Please note that in the actual source table there will be many more columns for a multitude of quarters and many more rows for a variety of Origins. Over time we will add more quarters and more origins.

    If there's some way of handling this without using VBA I'd prefer that since I'm VBA-inept... then again, this may be the best time to start learning it. This feels like something I should able to do with a simple crosstab, but I'm stumped.

    I appreciate any help you can provide -- thanks!

  2. #2
    Join Date
    May 2004
    New York State

    As you feared, the only way (that I know, anyhow) to do this conversion is with VBA. The reason is that a query will only represent a dataset in the same form as an Access table, not re-shape an Excel table to conform to an Access table.

    To explain: in Excel, you now have a dataset that has "some" rows and "lots of" columns, or a "row-major" dataset. In Access, you will have "some" columns and "lots of" rows, or a "column-major" dataset.

    I've done this many times through the use of an array. Link to, or import, the Excel table. In Access, determine how many columns there are (as you say you will be adding over time. I have no doubt you'll also be deleting) with the following code
    X = CurrentDb.TableDefs("The Table Name Here in Quotes").Fields.Count
    Also, determine how many rows there are, and store that number in a variable as well with
    Y = CurrentDb.TableDefs("The Table Name Here in Quotes").RecordCount
    Now Dim an array, e.g.
    Dim ProdArry(Y,X) As Variant
    Notice the Y before the X. In VBA, go row by row, reading the value in each column, and storing it in the appropriate element in the array. Since you're changing the dataset format, each time you advance by a column (in the original), you advance a row in the array to store it. MAKE SURE you substitute all null values as 0. After you finish that task, you can figure how to then go back over it row-by-row and enter each product into your re-formatted table.

    You'll need to look in the Help files to study arrays in order to get this right, but it's far from undoable. I'm confident that you're only a "not yet accomplished" VBA coder, just like I was back in 1999.

    Good luck,


Posting Permissions

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