Reformatting to run another SQL statement (crosstab?)

[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 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

 

[factotum]

I apologize for miss marking this post. It should be a "?" but I didn't even notice it was set to news. Feeling sheepish now...

 

[CindyK]

Daniel - I'm guessing there could be any number of dates between the start and end dates? If there's always just a start date and an end date, then it might be do-able as a crosstab, using some sort of formula to pull out the first and last dates within each MX.

If, on the other hand, there are multiple dates and you need to pull out ONLY the start and end dates, then I think the best way to do that would be to loop through each record, building the table via code as you go. It's a bit messy, but not too bad... I've got some code that could be tweaked to accomplish it.

Which scenario do you have? (and what version of Access...)