General discussion

  • Creator
    Topic
  • #2092724

    Excel horiz to vertical w/2 col repeated

    Locked

    by ctcarroll ·

    I currently output an Access query to an Excel spreadsheet comprised of rows of two distinct data elements followed by seven repeating sets of three:

    Resource ID|Project ID|Monday Date\Monday Hrs|Monday Cost|Tuesday Date\Tuesday Hrs|Tuesday Cost|Wenesday Date\Wednesday Hrs|Wednesday Cost|Thursday Date\Thursday Hrs|Thursday Cost|Friday Date\Friday Hrs|Friday Cost|Saturday Date\Saturday Hrs|Saturday Cost|Sunday Date\Sunday Hrs|Sunday Cost|

    I need to reconfigure it to more “vertical” rows asfollows:

    Resource ID|Project ID\Date|Hrs|Cost
    Resource ID|Project ID\Date|Hrs|Cost
    Resource ID|Project ID\Date|Hrs|Cost

All Comments

  • Author
    Replies
    • #3869721

      Excel horiz to vertical w/2 col repeated

      by mark.thomson ·

      In reply to Excel horiz to vertical w/2 col repeated

      The simplest way to achieve this is to re-write your query, I get the impression that you have a flat table something like

      [resourceid],[projectid], Monday Date, Monday Hrs, Monday Cost etc.

      If this is the case then you will need a UNION Query along the lines of

      SELECT ResourceID,ProjectID,MondayDate as RptDate,MondayHrs as Hrs,MondayCost as Cost UNION
      SELECT ResourceID,ProjectID,TuesdayDate as RptDate,TuesdayHrs as Hrs,TuesdayCost as Cost
      UNION (do the same for Wednesday etc.)
      If you don’t have a flat table as I described above then a simple join query (See the access online help) will suffice

      • #3869672

        Excel horiz to vertical w/2 col repeated

        by ctcarroll ·

        In reply to Excel horiz to vertical w/2 col repeated

        I cannot rewrite the query. I am getting this data out of a Blob through the vendor’s query facility which is proprietary and which does not allow me to query based on the data elements.

    • #3868760

      Excel horiz to vertical w/2 col repeated

      by mark.thomson ·

      In reply to Excel horiz to vertical w/2 col repeated

      If the resulting spreadsheet has each of the data elements in a separate cell then

      1) you can link the spreadsheet into a .mdb file as a table and run a union query against the linked table as I described before.

      or

      2) you can manually create a mask worksheet that uses links to transform the existing structure to the required structure (only do this if the number of rows in your result set is constant)

      or

      3) Write some VBA code to scan through the rows and either build an array of items to be written out once the scan is complete or write them out to another sheet during the scan. the VBA code would look something like

      rowidx = 1
      with worksheets(“Sheet1”)
      while not isempty(.cells(rowidx,1).value)
      ‘get monday’s date
      mondaydate = .cells(rowidx,3).value
      ‘ do something with monday’s date here etc.
      ‘ then move onto the next row
      rowidx = rowidx + 1
      wend

      If the data elements are not in separate cells then you need to scan through the cells (as in 3) above)and parse the contents of the cell to extract the data elements

      Hope this is more helpful

    • #3882256

      Excel horiz to vertical w/2 col repeated

      by ctcarroll ·

      In reply to Excel horiz to vertical w/2 col repeated

      This question was closed by the author

Viewing 2 reply threads