Web Development

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

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

This question was closed by the author