Query to return multiple rows in one row with subrows

[jpkeller55]

I am not even sure how to ask this question so I will show what I am trying to accomplish. I have table1 with data where the first field is repeated in multiple rows but the second field has unique values. The third field has data associated with field2 as such:

ID Field1 Field2 Field3
1 Joe Golf Red
2 Joe Baseball Red


3 Joe Running Blue
4 Bill Football Green
5 Bill Golf Yellow
6 Bill Tennis White

I am trying to write a query that can export to excel so the records display on the spreadsheet like this:

Row# Field1 Field2 Field3
1 Joe
2 Golf Red
3 Baseball Red
4 Running Blue
5 Bill
6 Football Green
7 Golf Yellow
8 Tennis White

I know this seems like a strange request, but I need to get the data from the Access 2003 table into the format below for an Excel file that will be used by other users.

Is this possible to do? Thanks so much for any help or suggestions

[weekend00]

select [row#],field1 from table1 union
select [row#],field2,field3 from table1
order by [row#]

Please note: Row# in export sheet is not same as sample, there are duplicate row#.

[jpkeller55]

Thanks W.E. for your help! I am getting the following error message, so I guess I am doing something incorrectly.
"The number of columns in the two selected tables or queries of a union query do not match"

Any idea?

[weekend00]

My mistake. try this:
select [row#],field1,"" as fieldx from table1 union
select [row#],field2,field3 from table1
order by [row#]

[jpkeller55]

I think this is close but I am getting another message which I assume is related to your comment: Please note: Row# in export sheet is not same as sample, there are duplicate row#

"The ORDER BY expression ([row#]) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression"

I appreciate your help...I am a novice at this and trying to learn. Thanks much.

[weekend00]

But we do have [row#] in first query:

select [row#],field1,"" as fieldx from table1 union
select [row#],field2,field3 from table1
order by [row#]

[jpkeller55]

That is what I thought too. If I take out the order by expression, I get data that is close to what I am trying to do but not exactly right. see the bmps attached of the table data and the resultant query.

[weekend00]

try this:

select 1 as temp,[ID],field1,"" as fieldx from table1 union
select 2 as temp,[ID],field2,field3 from table1
order by [ID] ,temp

[jpkeller55]

Getting closer but not quite there...see attached for what returned and what the desired state is

[weekend00]

this was tested:

select 1 as temp,first([ID]) as IDD,field1,"" as field2,"" as field3 from table1 group by field1
union
select 2 as temp,[ID],"",field2,field3 from table1
order by [IDD] ,temp

[jpkeller55]

Oh yeah! That is it! I can't thank you enough. I shall study this code to see what is going on to try to understand what is happening. I greatly appreciate your assistance. Have a great weekend!

[weekend00]

You are very welcome.