Access Query help with Group By

[smurfer]

I am trying to create a query that will pull data from a table and also concatenate a product description field, should a particular incident have more more than one record with a product. For Example..

Incident# Product Desc Product Number
1 Software 12
1 Hardware 14
1 OS 11
2 Software 12

what I want to create is a query that will pull out the data in this format, basically grouping by the Incident#, and creating a new field called Revised Product Description that will combine the diffetent product descriptions for a like incident# into one field, such as
1 Software, Hardware, OS
2 Software
etc..
Not all records have more than one product so currently in my query I have it pulling over a count of the product desc fields, but I would like to incorporate the count to help format the new description field, so that it knows if the count is 3, it needs, to add the first, then a , and then a second comma. This later part is not so important, but any help would be appreciated.
Thanks,
SM

 

[braindead2]

There may be a away to do what you ask using SQL but the only way I know how is using recordsets.

You will need to paste the function into a module save it and then in your query call it like this

exp1:combinefield([incident#],"incident#","product desc","tablenamehere&quot

paste function in a module. This is typed on the fly so may need some debugging. Also does not drop the last "," so you may want to play with that as well
I also suggest you look at the way you named incident# Access likes to use the pound sign for dates

good luck




Public Function Combinefield(varkey As Variant, keyname As String, fldname As String, tblname As String) As String
'will build a comma seperated string
'pass field , name of field
'name of field to return and name of table that holds data
'will only work if key name is a number type field and DAO is enabled

Dim rst As Recordset, db As Database
Dim strsql As String, StrBuild As String
strsql = "select [" & fldname & "] from [" & tblname & "] where " & keyname & " = " & varkey
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql)
With rst
Do Until .EOF
StrBuild = StrBuild & rst.Fields(0) & ", "
.MoveNext
Loop
.CLOSE
End With
Set db = Nothing
Combinefield = StrBuild
End Function

 

[smurfer]

braindead2,
Thank you for the suggestion, I tried it and it worked perfectly to do exactly what I needed. I even modified it to handle other fields to which I wanted to do the same thing. Thank you for the suggestion and help.
SM