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
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.
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
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Excel horiz to vertical w/2 col repeated
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