Convert Records in one field to a single string!!! (ACCESS 2000) 1

[ahmun]

I need to be able to generate a single string out of all the records of one field from a table.

for example:

tblAccidentsCodes

Code    Job#  Description
-----   ----  -------------
1322    1234  Disfigurement
1344    2345  Amputation
1211    2345  Laceration
1444    2345  Fall from 2nd floor
5531    3456  burns

I would like to find out all the accient codes for Job#2345 as a single string like thus:
"1344,1211,1444"

"1322, 1344, 5531"

I plan to put this in a report that shows a listing of each of the job# and an accident code (among other fields of data) so it's something like this:

Job#  Accident Codes       other info, etc...
----  ------------------   ----------------------
1234  "1322"               blah blah blah   
2345  "1322, 1344, 5531"   blah blor bleeb
3456  "5531"               doo bahp

I've thought about using a procedure in Access... and creating a recordset, but I have no idea how to do that in Access (but I know the syntax in ASP/vbscript)

is there a way to write a query that can do this?
any ideas?
Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[belden]

Thanks for your help Earnie! I'm sure I'll figure it out soon, but I need to leave it alone for the weekend so that I don't pull out the rest of my hair!

Belden