Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Rows To Columns (XP/03)

    This is probably someone has asked and been answered in the past, yet I can't find anything about it. I'm probably not using the correct keywords in my search.

    My challenge is I have a database with tables containing info, but need to convert the data from a row format to columns (if that makes sense). I don't know how to convert the data into a query, or spreadsheet on the fly. The number of columns will change at run time based on criteria selected by user (i.e. date range or group). I can create the query that provides the data, but just don't know how to convert it to columns.

    I have attached a sample spreadsheet. Please note that the sample contains only six columns of data, in practice the number of columns will vary.

    If necessary, I can also provide sample database. If anyone can point me in the right direction, I would be forever grateful.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rows To Columns (XP/03)

    Does the spreadsheet you attached represent a table in your database? If so, the design of that table is very unfortunate. Or is it the desired result?

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Rows To Columns (XP/03)

    Hans,

    No, the spreadsheet is NOT indicative of the table structures. I can query the tables and come up with the data elements required in the spreadsheet. Since posting original post I tried a cross tab query which ALMOST gives me the results I seek. It gives me the correct rows, and correct column headings (tracker number) but I am not able to figure out how to get the "Date" included as an additional column heading.

    Thanks for your ideas.

    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rows To Columns (XP/03)

    A crosstab query can only have a single row of column headers, not two. You may be able to get what you want in a report. Creating a report based on a crosstab query whose columns may vary is not trivial, search for dynamic crosstab report in this forum, or see <post#=35485>post 35485</post#>, <post#=134439>post 134439</post#> or <post#=248210>post 248210</post#>.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Rows To Columns (XP/03)

    Hans,

    Thanks for the leads. I'll read those and see where they lead. FWIW, we prefer something that can be exported to Excel, which will eliminate the variable columns issues in a report.

    Thanks again for your help.

    Ken

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rows To Columns (XP/03)

    Perhaps you can add the extra header row in Excel, after exporting the crosstab query.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Rows To Columns (XP/03)

    Hans,

    How would I go about adding it in Excel? Would I add a worksheet to the spreadsheet that contains the Tracker Number and Date as a lookup?

    thanks,
    Ken

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rows To Columns (XP/03)

    Yes, that would probably be the best option.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Rows To Columns (XP/03)

    Hans,

    As you probably know, I am a neophyte when it comes to Excel. Will it be possible to create the lookup dynamically? I'll start on this later today, and when I get to the part of adding a sheet and inserting the lookup data, I may have to ask additional questions.

    thanks!

    Ken

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rows To Columns (XP/03)

    You can use Automation to create the lookup formulas in the workbook from Access, but this requires at least some familiarity with Excel VBA.

  11. #11
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Rows To Columns (XP/03)

    Hans,

    I instead of working through Excel Automation, in the query I combined the date field with the track field in a query. Using "TrackDate: Format([TimeStamp],"yyyymmdd") & "_"& <tracker>" gives me "YYYYMMDD_TrackNumber" for the cell/column heading.

    I tried using "TrackDate: Format([TimeStamp],"yyyymmdd") & Chr$(15) & Chr$(10) & <tracker>" to see if I could get "YYYYMMDD CarriageReturn Tracker". Is there a way to insert a carriage return between two fields in a query?

    Thanks,

    Ken

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rows To Columns (XP/03)

    Try Chr(13) instead of Chr(15). The standard carriage return/line feed sequence is Chr(13) & Chr(10).

  13. #13
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Rows To Columns (XP/03)

    Hans,

    That worked GREAT! Thanks!

    Ken

Posting Permissions

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