question about reformatting data 1

[factotum]

I have some data in a table that has to be reformatted (even into an Excel spreadsheet would be ok) so it can be used in another SQL query to enter it into a new database. Right now, it looks to be a lot of manual data entry, but I'm wondering if something like a crosstab or a group by cube, might reformat this and automate a lot of this.

Basically, there is a table that has dates when milestones were completed. Each milestone is a column. However, we want to flip this so that the columns become rows, and we lift out the start date.

When I try to set up a crosstab query in Access, (using the wizard) it only allows three row headers, where I will need 21 rows for each project number, and then there are many project numbers. Here's a simplified version of how it is now:

+------------+---------+--------+--------+--------+
| ProjectNo  |    M1   |   M2   |   M3   |   Mn   |
+------------+---------+--------+--------+--------+
|  0001      | 6/12/01 | 6/14/01| 6/15/01| 6/25/01|
+------------+---------+--------+--------+--------+
|  0002      | 6/13/01 | 6/15/01| 6/18/01| 6/27/01|
+------------+---------+--------+--------+--------+
|  0003      | 7/12/01 | 7/15/01| 7/18/01| 7/20/01|
+------------+---------+--------+--------+--------+

Where I need it to be like this for someone else who's writing another query:

+------------+---------+--------+--------+
| ProjectNo  |Milestone|  Start |   End  |
+------------+---------+--------+--------+
|  0001      |    M1   | 6/12/01| 8/19/01|
+------------+---------+--------+--------+
|  0001      |    M2   | 6/13/01| 9/18/01|
+------------+---------+--------+--------+
|  0001      |    M3   | 7/15/01| 12/1/01|
+------------+---------+--------+--------+

Where this will continue down the rows for the subsequent project numbers. Can this be done? I would appreciate any help on this, as spending a little time learning to do this will save a whole bunch of time down the line.

Thank you,

=Daniel

 

[dalchri]

I think that what you are actually after is a union query. I would try the following:
[tt]
SELECT ProjectNo, 'M1' AS Milestone, M1 AS Start
FROM YourTable
UNION ALL
SELECT ProjectNo, 'M2' AS Milestone, M2 AS Start
FROM YourTable
UNION ALL
SELECT ProjectNo, 'M3' AS Milestone, M3 AS Start
FROM YourTable
...
SELECT ProjectNo, 'Mn' AS Milestone, Mn AS Start
FROM YourTable
[/tt]
Unfortunately, this will result in a rather nasty long SQL statement for 21 columns but it will produce the results you are looking for. I was not quite clear on where the End date was coming from so I didn't include it. Hope the simplified example gives you an idea to work with!

 

[factotum]

Thanks dalchri. That seems to be doing the trick (two down with 19 to go...)

Sorry for the confusion; the 'End' was just one of the milestones on down the list.

Thanks.