Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a table containing data by week no and by year

    I need to export this data with the weeks spread across the rows and curr yr, last yr and last yr-1 . There will be a total of 10 rows.

    e.g.

    Actual Plants Propagated = wk1 Curryr, wk1 Lastyr, wk1 Lastyr-1, wk2 Curryr, wk2 Lastyr, wk2 Lastyr-1, wk3 etc.etc. to wk52 Curryr, wk52 Lastyr, wk52 Lastyr-1

    I know how to create an array with the data in it but is there any easy way of putting out the excel spreadsheet or even a limited report without creating a table with 157 fields per record?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You might be able to do it by linking some Crosstab queries together on a key grouping field
    Not sure you can do what you want with 1 Crosstab though
    Difficult without seeing an example of the actual table(s)
    Andrew

  3. #3
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you are going to "Export" it, you would need to get your data into a table or have a query that contains the result and use the TransferSpreadsheet method of the DoCmd object but...

    If you have the Array already populated, rather than "Exporting", maybe you would consider opening up Excel (Via Automation - You'll need to set a reference to the Excel Object Model) and loop through the array placing each value into a cell on the Worksheet.

    Perhaps that would work for you?

    Bob Oxford
    Software Wizards, Inc.
    Bob Oxford
    Software Wizards, Inc.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Peter_Thesiger View Post
    I know how to create an array with the data in it but is there any easy way of putting out the excel spreadsheet or even a limited report without creating a table with 157 fields per record?
    If you create a query that makes the data look like your headings (I assume there is a plant type of some sort, or at least one other data field in your source table), and then do a crosstab on that query using the row value as the plant type or condition, the expression created in the query as the column value, and the count as the cell value, it should create what you are looking for. I've never actually created a crosstab with 157 columns, but there doesn't appear to be any limit other than the 255 columns in a returned dataset.
    Wendell

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    Napier, Hawke's Bay, New Zealand
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Have you tried using a PivotTable?

Posting Permissions

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