-
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
-
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
-
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....
-
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
-
Um, why would you want to that, Tom? It would seem that Dick wants to use a recordset.
something like:
Code:
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).]
-
I was pretty sure he'd figure out to do a loop until EOF with that code...
