Query Problems : Can Code help or cross join??

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

 

[hneal98]

This situation looks more like you would want to do it in a report. This would be a very ineficient query, why whould you want it to look that way? If you think about it, you have approx. 3 items in each group. You list them with the query, you get three items with each group, but to do what you are wanting, you would have 4 items in each group wich means at least one record would have to be duplicated to make up for the extra item.

As stated before, it would make more sense to do this in either a report or form where you have groups and detail sections.

 

[fogal]

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 ebst way to do it is from a query.

 

[hneal98]

Again, you can do that, but you will have to decide what you want to have duplicated, because that is the only way this will work since you can't do grouping in the query.

I am not sure how you would set it up to do this since I never tried it myself. My suggestion would be to play around with the report or form and try to get the data exported from there.

 

[EastWind]

First, add a generic primary key to the table. Then,make a table with the one column "Field 3" that contains the data from "Field 2," sorted by the primary key. Append to that table the "SELECT DISTINCT" data from "Field 1" sorted by the primary key. I haven't tried that, but it sounds like it will produce what you want.

 

[Tranman]

fogal,
If all you're trying to do is create a text file formatted like your example, here's a different way that will get around having to write all that fancy SQL:
(I ran your data through it--worked fine)

Private Sub tester()
Dim rs As New ADODB.Recordset
Dim strHold1 As String
Dim strDummy As String
rs.Open "Select * from groups order by FIELD1, FIELD2", CurrentProject.Connection
'Open the text file here

Do While Not rs.EOF
strHold1 = rs.Fields("FIELD1&quot
strDummy = rs.Fields("FIELD1&quot 'put logic to write text file record here
Do While rs.Fields("FIELD1&quot = strHold1
strDummy = rs.Fields("FIELD2&quot 'put logic to write text file record here
rs.MoveNext
If rs.EOF Then Exit Do
Loop
Loop
'Close the text file here
rs.Close
Set rs = Nothing
End Sub

Tranman

 

[fogal]

Here is how I did it in the end:
Though the code below is actually for my real data, not the sample data given above:
Dim dbs As Database, rst As Recordset, rst2 As Recordset
Dim title1 As String, title2 As String, title3 As String, title4 As String, title5 As String
Dim counttitle1 As Integer, counttitle2 As Integer, counttitle3 As Integer, counttitle4 As Integer, counttitle5 As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset, dbSeeChanges)
Set rst2 = dbs.OpenRecordset("SELECT * FROM Table2", dbOpenDynaset, dbSeeChanges)
title1 = "@TitleHeading:MEN SEEKING MEN"
title2 = "@TitleHeading:MEN SEEKING WOMEN"
title3 = "@TitleHeading:SEEKING FRIENDS"
title4 = "@TitleHeading:WOMEN SEEKING MEN"
title5 = "@TitleHeading:WOMEN SEEKING WOMEN"

counttitle1 = 0
counttitle2 = 0
counttitle3 = 0
counttitle4 = 0
counttitle5 = 0



Do Until rst.EOF
With rst
rst.Edit

'Title1
If rst("Title1&quot = title1 Then
If counttitle1 < 1 Then
counttitle1 = counttitle1 + 1
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = title1
End With
rst2.Update
End If
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = rst(&quot;Text1&quot
End With
rst2.Update
End If

'Title2
If rst(&quot;Title1&quot = title2 Then
If counttitle2 < 1 Then
counttitle2 = counttitle2 + 1
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = title2
End With
rst2.Update
End If
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = rst(&quot;Text1&quot
End With
rst2.Update
End If

'Title3
If rst(&quot;Title1&quot = title3 Then
If counttitle3 < 1 Then
counttitle3 = counttitle3 + 1
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = title3
End With
rst2.Update
End If
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = rst(&quot;Text1&quot
End With
rst2.Update
End If

'Title4
If rst(&quot;Title1&quot = title4 Then
If counttitle4 < 1 Then
counttitle4 = counttitle4 + 1
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = title4
End With
rst2.Update
End If
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = rst(&quot;Text1&quot
End With
rst2.Update
End If

'Title5
If rst(&quot;Title1&quot = title5 Then
If counttitle5 < 1 Then
counttitle5 = counttitle5 + 1
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = title5
End With
rst2.Update
End If
With rst2
rst2.AddNew
rst2(&quot;Textnew&quot = rst(&quot;Text1&quot
End With
rst2.Update
End If

End With
rst.Update
rst.MoveNext
Loop
'Close recordset
rst.Close
rst2.close
Set dbs = Nothing