data into one column from multiple rows

Here is a general function that uses a numeric ID field, if your ID field is text then you need to modify the function. You can call with the appropriate parameters for example

=GetFieldFromMultiRecords_NumberID(1,"Client2","ClientID","ClientName")

=GetFieldFromMultiRecords_TextID("1","Client2","ClientID","ClientName")

Cheers, Andrew

Paste the following into a new module

Function GetFieldFromMultiRecords_NumberID(plngID As Long, pstrTable As String, pstrIDField As String, pstrMergeField As String) As String
Dim db As DAO.database
Dim rst As DAO.Recordset
Dim strReturnValue As String

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT [" & pstrMergeField & "] FROM " & pstrTable & " WHERE [" & pstrIDField & "]=" & plngID)
    Do While Not rst.EOF
       strReturnValue = strReturnValue & ", " & rst("[" & pstrMergeField & "]")
       rst.MoveNext
    Loop
   
    If Len(strReturnValue) > 0 Then strReturnValue = Mid(strReturnValue, 3)
   
    GetFieldFromMultiRecords_NumberID = strReturnValue
   
End Function

Function GetFieldFromMultiRecords_TextID(pstrID As String, pstrTable As String, pstrIDField As String, pstrMergeField As String) As String
Dim db As DAO.database
Dim rst As DAO.Recordset
Dim strReturnValue As String

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT [" & pstrMergeField & "] FROM " & pstrTable & " WHERE [" & pstrIDField & "]=" & Chr(34) & pstrID & Chr(34))
    Do While Not rst.EOF
       strReturnValue = strReturnValue & ", " & rst("[" & pstrMergeField & "]")
       rst.MoveNext
    Loop
   
    If Len(strReturnValue) > 0 Then strReturnValue = Mid(strReturnValue, 3)
   
    GetFieldFromMultiRecords_TextID = strReturnValue
   
End Function

Now that you have the function you can create a select query that does

SELECT id, name, [g/ug], sum(gpa), GetFieldFromMultiRecords_NumberID([id],"TableName","ID","grade")
FROM tablename
GROUP BY id, name, [g/ug]

Cheers, Andrew

Basically you have two methods:
1) A function returning the string when the JobID is given
  Slow but also usefull for just filling a field on a form
  (Andrew's solution above)
2) A function building a temp-table with the result
  Faster but needing extra table space

Normally this "comma separated field" isn't needed and as a field has a max of 255 characters truncation can occurr.
In general the way of working is using a form/subform or a report with a subreport. By making the main for Job's and linking by job ID the sub, you'll always see all Items.
A subreport can even be set to have them listed in colmuns...

Nic;o)

Nic;o i have just tested the function and the field I tested was 545 characters in the query.

Cheers, Andrew

Nic;o i have just tested the function and the field I tested was 70,014 characters in the query.

Cheers, Andrew

Hmm, strange, as a Memo field that's maniplated will trunc to 255..
I know in VBA using functions sometimes some 6,000 characters is used as the limit...

Nic;o)

OK In Access 97 when I do a len of the function I get 70k and when I do a len of a control on the form (txtexpr1) which is bound to expr1 which is the function then I get 4,478, when I select the data from the field in the query and paste it to word I also get 4,478.

Cheers, Andrew

When I converted Access 97 to 2000 I got data too long at 70k. Either way the function can be taylored to say if the len is > max allowed in the current version os access then either return the max allowed or a warning.

Cheers, Andrew

hi

SELECT id, name, [g/ug], sum(gpa), GetFieldFromMultiRecords_NumberID([id],"TableName","ID","grade")
FROM tablename
GROUP BY id, name, [g/ug]

y are there two id fields in teh function do i have to place same id in both

hi

SELECT id, name, [g/ug], sum(gpa), GetFieldFromMultiRecords_NumberID([id],"TableName","ID","grade")
FROM tablename
GROUP BY id, name, [g/ug]

y are there two id fields in teh function do i have to place same id in both

hi
the table design is
id(number)
level(text)
sum(gpa)(number)
grade(text)
so i tried to modify the textID function so will id figure in both places

hi
the table design is
id(number)
level(text)
sum(gpa)(number)
grade(text)
so i tried to modify the textID function so will id figure in both places

The first ID field in thwe function paddes the value of the ID field, the second passes the field name.

It is the Number function you want to use as teh ID field is a number.

Cheers, Andrew

hi
the table design is
id(number)
level(text)
sum(gpa)(number)
grade(text)
so i tried to modify the textID function so will id figure in both places

when i ran the function i gave the id field in both places and it gave "IN" in all records
will it create new table or is it gonna modify the table

hi andrew,
thought i would make sure what i wanted ...
i have this table in access which has multiple rows for a same id listing grades he earned each semester.
i wanted to have all those grades in one column.. for each individual record in one row.
the grades will come well within 255 characters...
i have listed the table definition earlier.
thanks

Do you want to send me a database with your table, query and function in it and I will check it out. My email address is in my profile.

Cheers, Andrew