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

 

[hermanlaksko]

Hi earnie you need to do 2 things well 3 .....

1. create a function
2. create a recordset ( as you imply)
3. create a loop to loop thru these records

Here is how:

Function LoopString()
Dim Re, ReOut as Dao.Recordset
set Re = currentdb.openrecordset "Select * From YourTable"
set ReOut = currentdb.openrecordset "Select * From YourOutTable"

Do while not Re.eof
ReOut.Addnew
ReOut!YrField=Re!YrField
ReOut.update
re.movenext
Loop
End Function

This should do the Trick :-D
The function should be placed in a module

 

[PWise]

take a look at crosstab queries

 

[ahmun]

Herman,

I've tried your example... but Access 2000's help file doesn't seem to help me on one problem: When I try to set the DAO.Recordset variable type, Access bombs out with a "User-Defined Type not Defined" Error.

I've tried randomly changing that declaration to:
Dim rsRecordset as ADDODB.Recordset
............... as Recordset

But access doesn't seem to like the syntax

pwise... I thought about doing a Cross-tab query, too... but once I get the query to spit out the data... how do I gather it into one string?


Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[hermanlaksko]

Lookes like you will have to goto tools/refrences and add
Microsoft DAO 3.x to get the DAO recordset to work for you or else change my suggestion to adodb.

Herman

 

[ahmun]

it seems that Access wants me to set up a connection object..

when I type Set rsRecordset = CurrentDb.OpenRecordset("SELECT * FROM MyTable&quot

I get a type mismatch error... Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[hermanlaksko]

Are you using an MDB or an ADP file for your app.

If you are using the ADP you can forget about using DAO for this app

 

[ahmun]

I am using an MDB..

I'm not sure how to add the ADO defined set to my database (I'm using Access 2002, but working on a a2000 file). I don't see taht option in the menu (Tools/refrences) Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[hermanlaksko]

Ok -
1 goto modules
2 Find tools
3 The first menu should be refrences in this you can select the DAO refrence.

If you are going to use my above mentioned function you do not want ADO but DAO.

Let me know what happens
Herman

 

[ahmun]

Wow... that worked! I didn't know that the menu set for the main database window and the code window was different, so I was unable to find the Tools/References menu at first.

I was able to get at my problem mentioned in the first post of this thread...

You saved me HOURS of clicking thru different tutorials and websites to figure out the right syntax! For that you get a star!

For reference, I wrote the code to assemble all the data into one string kinda like this:

Public Function AccidentCodeToString(intJobNumber As Integer) As String

    Dim rsAccidentCodes As DAO.Recordset

    Dim strAccidentCodes As String

    Dim sSQL As String

    strAccidentCodes = ""

    sSQL = "SELECT tblAccidents.strAccidentCode FROM tblAccidents "

    sSQL = sSQL & "WHERE (tblAccidents.intJobNumber) = " & intJobNumber

    Set rsAccidentCodes = CurrentDb.OpenRecordset(sSQL)

    If Not rsAccidentCodes.EOF Then rsAccidentCodes.MoveFirst

    Do While Not rsAccidentCodes.EOF

        strAccidentCodes = strAccidentCodes & ", " & rsAccidentCodes!strAccidentCode

        rsAccidentCodes.MoveNext

    Loop

    Debug.Print strAccidentCodes

    Set rsAccidentCodes = Nothing

    AccidentCodeToString = Trim(Mid(strAccidentCodes, 2))

End Function

I put this function in the data source property of a textbox in a report.

=AccidentCodeToString([intJobNumber])

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[hermanlaksko]

Thx Earnie, anytime
Herman

 

[belden]

Hey Earnie!

I have the exact same conundrum that you had last month, but I don't know VB to save my life.

I've got a table in an Access 2000 db that looks like this:
MemberID Designation
1 MBA
1 CPA
2 ABC
2 XYZ
3 MBA

Idealy, I'd like to query the data and make a new table that looks like this:
MemberID Designation
1 MBA, CPA
2 ABC, XYZ
3 MBA

This situation was forced upon me by an evil Web developer who, due to my remaining principles, shall go nameless.

Any help would be greatly appreciated!

Thanks,

Belden

 

[ahmun]

Belden.

I made a crude model in Access 2000... you can look at... is there an email I can send the Access database to?

The code I wrote is as follows... put this in a module in your database, then assign the function to a button or event in your db. (or you can copy/paste the code below into a module in your db)

and if anybody else has any better ideas... I am just using what I just learned from Herman!

Option Compare Database

Public Function DesignationToString()
    
    'recordset object to store your Database's Member IDs
    Dim rsMemberID As DAO.Recordset
    
    'recordset object to store your Designations for each Member ID
    Dim rsDesignation As DAO.Recordset
    Dim strDesig As String
    Dim sSQL, sSQL_ID, sSQL_Desig, sSQL_ins As String
    Dim intID As Integer
    
    
    'query to gather all possible membership IDs -
    '***I don't know what your ID table origniated from...
    '*** but I'll work off your example
    sSQL = "SELECT distinct tblDesignations.intMemberID FROM tblDesignations"
    Set rsMemberID = CurrentDb.OpenRecordset(sSQL)
    
    
    'set a tabledef of the table we will use to insert the formatted data into
    Set tdfDesigByID = CurrentDb.TableDefs!tblDesignations
    
    
    'set up query to find all the Designations for a specific ID
    'The value to compare in the WHERE clause is left out
    ' to be built below in the loop
    sSQL_Desig = "SELECT tblDesignations.strDesignation FROM tblDesignations "
    sSQL_Desig = sSQL_Desig & "WHERE (tblDesignations.intMemberID) = "
    
    'loop through each of the Member IDs
    Do While Not rsMemberID.EOF
        'set the ID for the criteria of the WHERE clause
        intID = rsMemberID!intMemberID
        
        'loop through the recordset of the table to gather
        ' all the Designations into a string
        sSQL_ID = sSQL_Desig & intID
        Set rsDesignation = CurrentDb.OpenRecordset(sSQL_ID)
        
        If Not rsDesignation.EOF Then rsDesignation.MoveFirst
        strDesig = ""
        Do While Not rsDesignation.EOF
            strDesig = strDesig & ", " & rsDesignation!strDesignation
            rsDesignation.MoveNext
        Loop
        
        'create a query to insert the new string
        sSQL_ins = "INSERT INTO tblDesignationsByID ( intMemberID, strDesignation )"
        sSQL_ins = sSQL_ins & "SELECT " & intID & ", '" & Trim(Mid(strDesig, 2)) & "'"
        DoCmd.RunSQL sSQL_ins
        rsMemberID.MoveNext
    Loop
    
    Debug.Print strDesig
    Set rsDesignation = Nothing
    Set rsMemberID = Nothing
    'AccidentCodeToString = Trim(Mid(strAccidentCodes, 2))
End Function

' I copied this code from Access Help
Public Sub AppendDeleteField(tdfTemp As TableDef, _
    strCommand As Variant, strName As String, _
    Optional varType, Optional varSize)

    With tdfTemp

        ' Check first to see if the TableDef object is
        ' updatable. If it isn't, control is passed back to
        ' the calling procedure.
        If .Updatable = False Then
            MsgBox "TableDef not Updatable! " & _
                "Unable to complete task."
            Exit Sub
        End If

        ' Depending on the passed data, append or delete a
        ' field to the Fields collection of the specified
        ' TableDef object.
        If strCommand = "APPEND" Then
            .Fields.Append .CreateField(strName, _
                varType, varSize)
        Else
            If strCommand = "DELETE" Then .Fields.Delete _
                strName
        End If

    End With

End Sub

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[ahmun]

oops... I accidentally added that last bit of code that I ended up not using since I didn't udnerstand that "AppendDeleteField Function"

so you can disregard that last function. Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[belden]

Earnie,

Thanks for the quick reply!

Where the "that last bit of code" begin?

I've been swamped today so I haven't been able to try your code yet. When I do I'll let you know how it goes.

Thanks!

Belden

 

[ahmun]

o... it might help to clarify what that "last bit of code" was...

omit all code after:

' I copied this code from Access Help
Public Sub AppendDeleteField(tdfTemp As TableDef, _
    strCommand As Variant, strName As String, _
    Optional varType, Optional varSize)

    With tdfTemp
etc...
.
.
.

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[belden]

Earnie,

I get "Compile Error: Error in loading DLL" and it highlights "CurrentDb" when it get to here:

Set rsMemberID = CurrentDb.OpenRecordset(sSQL)

Any thoughts?

Thanks!

Belden

 

[ahmun]

Belden,

you might need to add the DAO add-in.

To do this:

[ol][li]Open up the Module or code window where you inserted to code.[/li]
[li]Under the Menu: Tools -> References[/li]
[li]Select "Microsoft DAO 3.6 Object Library" from the list[/li][/ol]
lemme know if this fixes things Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[belden]

What if Microsoft DAO 3.6 Object Library is not an option in the list?

Thanks,

Belden

 

[ahmun]

What version of Access are you using? Did you scroll down the entire list?
You need to be sure you are using the menu of the code view window instead of Access' window where you find the database window. (the menu sets are different)

If you can't find the plugin, look through the forum for how to do the similar thing with ADO. I'd love to make an ADO version for you, but I'm kinda bogged down with a project right now.

Hope you figure out your problem!

anybody have another way around this solution? Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once