data into one column from multiple rows

nrv_vikram
nrv_vikram used Ask the Experts™
on
hi
i would like to know how to get data from multiple rows into one column for a record as in:

id  name   g/ug    sum(gpa)   grade
1    sam    g      10.3   a
1    sam    g      10.3   b
1    sam    g      10.3   c
10   tom    ug     12     a
10   tom    ug     12     c

as

id  name   g/ug    sum(gpa)    grade
1   sam    g       10.3   a,b,c
10   tom   g       12     a,c

this is for a spl project not a database design.. just need it to export to word doc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Commented:
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)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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
Ok thanks for sending the database.

1. in your module go to the Tools Menu, References and select Microsoft DAO 3.6
2. change your query to
SELECT [testdatagrade].[ET_PIDM], [testdatagrade].[DE_UIC_LEVEL], [testdatagrade].[PE_HANDI], Sum([testdatagrade].[DG_GPAV]) AS SumOfDG_GPAV, GetFieldFromMultiRecords_NumberID([ET_PIDM],"testdatagrade","ET_PIDM","ST_GRADE") AS Expr3
FROM testdatagrade
GROUP BY [testdatagrade].[ET_PIDM], [testdatagrade].[DE_UIC_LEVEL], [testdatagrade].[PE_HANDI];
3. You may want to change the function as your grade field contains spaces. to eliminate the spaces you can add the TRIM() function to the line

strReturnValue = strReturnValue & ", " & rst("[" & pstrMergeField & "]")

so it becomes

strReturnValue = strReturnValue & ", " & Trim(rst("[" & pstrMergeField & "]"))

Cheers, Andrew

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial