Concatenating Data

[rhfrank]

I know that this is a strange one, but I need to know how to generate a SQL that will allow me to concatenate multiple values within a single field into a single record separated by ",".

I have to take a field called category with several entries based on a single ID and concatenate them into a single record so I can import the record into MS Outlook.

ID Category
1 Safety
1 Industrial Hygiene
1 Environmental

This now must be:

ID Category
1 Safety, Industrial Hygiene, Enviro

------------------
Dick Franklin

[Clunietp]

Hey Dick

I used this with the biblio.mdb file, it should work for you...

SELECT (Au_ID & ', ' & Author & ', ' & [Year Born]) as NAME
FROM Authors;

HTH

Tom

[rhfrank]

Thanks for your feedback Tom:

I applied the SQL statement and it combined the two fields into one as advertised. What I needed to accomplish is a way of reading an entire table and create another one whereby all of the items related to a single ID end up in a single record in a single field. (See) Example in origonal posting.

I am trying to setup an array that might do this, but I am not sure how to accomplish this. The people here want take a relational database and turn it into a flat file.

I will keep on looking but I would really be greatful for any constructive solutions.

Tnx... Dick Fr....

[Clunietp]

hahahahahahahahaha
the thrill of victory.......


add reference to Microsoft Scripting runtime
(part of IE4 i believe)

add code:

Dim fso As New Scripting.FileSystemObject
Dim ts As TextStream
Set ts = fso.CreateTextFile("C:\test.txt", True)

ts.WriteLine ("results of sql here")
ts.WriteLine ("next line here")
ts.WriteLine ("et cetera, et cetera")
ts.Close

woo hoo!

Tom

[JHausmann]

Um, why would you want to that, Tom? It would seem that Dick wants to use a recordset.

something like:

Dim rsTemp  as Recordset
Dim sWork   as String
Dim nHoldID as Integer


nHoldID = -999 ' set to some value not in database
open "C:\output.txt" for output as #6
sSQL= "select ID, category from table order by ID, category"
set rstemp= databaseobject.OpenRecordSet (sSQL)
if rsTemp.EOF = False then ' there's entries to process
   Do while not rsTemp.EOF
      if CInt(rsTmp.Fields(0)) <> nHoldID then
         'do something with previous string
         print #6, sWork
         sWork= trim(rsTmp.fields(0)) & " " & trim(rsTmp.fields(1))
         nHoldID= CInt(rstmp.Fields(0)
       else
          'add to existing string
           sWork= sWork & "," trim(rsTmp.fields(1))
       end if
       rstmp.MoveNext
    loop
end if
close #6
rsTmp.close

[This message has been edited by JHausmann (edited 10-29-1999).]

[Clunietp]

I was pretty sure he'd figure out to do a loop until EOF with that code...