Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Chapel Hill, North Carolina, USA
    Thanked 0 Times in 0 Posts

    Transpose a table (2000)

    I feel like I'm missing something, but I need to do something in access that should be simple but keeps escaping me. I have a table with 1 row and 60 columns (no unique key). There will only ever be one record/row in this table - I know it's silly, but it's something I need for a web page to pull data in a specific manner. Problem is, I also need that same data dynamically fed into a query to wind up with one column and 60 rows. Is this as simple as I think it should be?

    The only way I can figure out how to do this is to write VBA to move the data using the .field attribute from my original table to a second table with only one field.

    Is there an easier way?

    If not, what is the syntax to identify the last field in the table?



  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Thanked 0 Times in 0 Posts

    Re: Transpose a table (2000)

    Is there something specific you need to do with the last field in your table? You can use MyRecSet.Fields.Count to identify the last field in a table to then get it's value.

    If you just want to walk through all the fields in a table to get their values, you can use a FOR EACH statement on the recordset's Fields object.
    This way, you don't need to know the number of fields in the table.

    For Each myfield in MyRecSet.Fields
    varValue = myfield.value
    'Code to add the varValue to your single column Recordset

    Another way would be to create an instance of excel. Export the one record in your recordset to the worksheet. Use the Transpose Worksheet function to create a single column list and then export the transposed data back to another recordset or just reference the transposed data (link the spreadsheet to your DB as a table). I think this would take a fair chunk of code to do it this way though...


Posting Permissions

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