Column Field to Row Field

[Guest]

I have a very simple query. It gives back two nice columns of data. In
Column1 is Field1 and in Column2 is Field2. Field1 is always unique but
Field2 has from one to many results for Field1.

It looks like this:
Field1 Field2
A 1
B 2
C 2
D 3

What I need to do (either within this or another query using this result
set) is to make it where for all the Field2 I have a list of the Field1. In
other words, it would look like this:
Field1 Field2
A 1
B, C 2
D 3


Any thoughts? And, thanks for your time in reading this (and hopefully
responding).

 

[Guest]

You'll need to create a function in a standard module to concatenate the
values of Field1 into a comma delimited list, e.g.

Public Function GetField1List(lngField2 As Long) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strList As String

strSQL = "SELECT * FROM YourTable WHERE Field2 = " & lngField2

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Do While Not .EOF
strList = strList & ", " & _
.Fields("Field1")
.MoveNext
Loop
.Close
' remove leading comma and space
strRecipients = Mid$(strRecipients, 3)
End With

Set rst = Nothing
GetField1List = strList

End Function

This assumes Field2 is an integer number data type. You'd then call the
function in a query, passing the value of Field2 into it like so:

SELECT DISTINCT GetField1List(Field2) AS Field1List, Field2
FROM YourTable
ORDER BY Field2;

Ken Sheridan
Stafford, England

 

[Guest]

Create a temp table, and use a procedure to table the data from the original
table into the temp table with the desire resault

something like

Function FillTempTable()
Dim MyDB As DAO.Database, MyTab As DAO.Recordset, MyTempTab As DAO.Recordset
Dim NumCount As Integer, MyStr As String
DoCmd.RunSQL "Delete from TableName_New"
Set MyDB = CurrentDb
Set MyTempTab = MyDB.OpenRecordset("Select * From TableName_New")
Set MyTab = MyDB.OpenRecordset("Select * From TableName Order by Field2")
NumCount = MyTab!Field2
While Not MyTab.EOF
If NumCount <> MyTab!Field2 Then
MyTempTab.AddNew
MyTempTab!Field1 = IIf(Left(MyStr, 1) = ",", Mid(MyStr, 2), MyStr)
MyTempTab!Field2 = NumCount
MyTempTab.Update
MyStr = MyTab!Field1
NumCount = MyTab!Field2
Else
MyStr = MyStr & "," & MyTab!Field1
End If
MyTab.MoveNext
Wend
MyTempTab.AddNew
MyTempTab!Field1 = IIf(Left(MyStr, 1) = ",", Mid(MyStr, 2), MyStr)
MyTempTab!Field2 = NumCount
MyTempTab.Update

End Function

 

[Guest]

Thank you for your help Ken. If I can't get this (or Ofer's suggestion) to
work, I will be back!

 

[Guest]

Thanks for the suggestion Ofer. If I can't get your (or Ken's thought) to
work, I will be back!

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Imdabaum]

I'm doing something similar where I have something like this.
Field 1 Field 2 Field 3 Field 4 Field 5
A date User Z
"NOTE"
A date User Z
"NOTE"
B date User Z
"NOTE"
A date User Z
"NOTE"
B date User Z
"NOTE"
C date User Z
"NOTE"


Field 1 is a projectID and fields 2-5 hold information based on notes
that are entered about that project. I have managed to figure out how
to combine Field 2-5 in one field NoteInfo. Now I am trying to display
these notes in one textBox of Memo type through the main form. Now I
am trying to combine row level data. For example in the previous table
I drew up, I want NoteInfo Where ID= "A" and I want them concatenated
into one field so the end result would be a table with 2 fields, and in
this case only 3 rows instead of 6. One row for all As, One row for
all Bs and one row for the C (and future Cs).

I am currently trying this method

DO Until RECORDSET.EOF
strNote= strNote & recordset.Fields("Noteinfo") & vbNewLine
RECORDSET.MoveNext
Loop
If the recorset field has a text value
with more than 255 characters it stops though. Any thoughts?

 

[Duane Hookom]

I'm not sure why you don't just use the generic function that returns more
than 255 characters. There is nothing in your code that would limit the
number of characters returned.