Query Possible???

[fogal]

I have a query with 2 fields as shown below:
I want to create a new field (Field3) to do as shown below.
Field1 Field2
GroupA A1
GroupA A2
GroupA A3
GroupB B1
GroupB B2
GroupB B3
GroupC C1
GroupC C2
GroupC C3
I want my query to display a new field, don’t mind if it is done from a new query:
NewField3:
GroupA
A1
A2
A3
GroupB
B1
B2
B3
GroupC
C1
C2
C3

I have just used sample data to give everyone an idea of what i'm trying to do.

Reason why i'm not using a report:
Unfortunately, I need to do this in a query. I plan to export the query to a text file using access xp. I have this working in a report, as you stated but when I export the data to text, it misses some of the text in the export. It works fine from access 97. I now believe the best way to do it is from a query.

 

[jrbarnett]

You can put in dummy items in the table to sort so that the data comes out sorted correctly.
Alternatively, does it come out correctly if you print the report to a text file?

John

 

[JonFer]

For the lowest Field2 value within each group, this will put Field1 and a CR/LF in front of the Field2 value. When you export to text, it should appear as if they were on separate rows in the query output.

SELECT IIf([Field2]=(Select Min(Field2) from TableName as a where a.Field1=TableName.Field1),[Field1] & Chr$(13) & Chr$(10) & [Field2],[Field2]) AS NewField
FROM TableName
ORDER BY Field1,Field2

Another way is to add another table that has two rows in it and put an IIf that says for the first row, output Field1 and for the second row, output Field2. Your criteria would need to include the first row for the minimum Field2 value within each Field1 and the second row all of the time.