combining multiple records

[TyeReece]

I create access queries from design view frequently but have just started working with code. I have a simple query that returns something like this.
ID DSM_IV
1 Autism Spectrum Disorder
1 Communication Disorder (Includes NOS and Unspecified)
2 Reactive Attachment Disorder (RAD)
3 Anxiety Disorder (Includes Other Specified/Unspecified/NOS)
3 Disruptive Mood Dysregulation Disorder
3 Pica
3 PTSD (Posttraumatic Stress Disorder)

ID is a unique identifier and DSMIV the data I want to combine by ID. I have found multiple modules on the net that do this but don't seem to for me.

First, what does the "DAO" in the following text mean? "Dim db As DAO.Database". All the functions I got from the net break here when I try to compile.

Second, there is the possibility of returning up to 450 or so characters in the combined field. Will this create a problem?

Third, below is one of the shortest things I found. How, do I change this work in my case?

Option Compare Database

Public Function fMakeCommaDelimited() As String
Dim strTemp() As String
Dim rs As ADODB.Recordset
Dim strSql As String
Dim i As Long
Dim lngRecCount As Long
Set rs = New ADODB.Recordset
strSql = "SELECT User_ID From YourTable;" 'Change the fieldname and table name to yours.
With rs
    .ActiveConnection = CurrentProject.Connection
    .Source = strSql
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open options:=adCmdText
    lngRecCount = .RecordCount
    ReDim strTemp(lngRecCount - 1)
    Do Until .EOF
        strTemp(i) = !User_ID
        .MoveNext
        i = i + 1
    Loop
    .Close
End With
Set rs = Nothing
fMakeCommaDelimited = Join(strTemp, ",")
End Function

 

[ranman256]

You don't need any code to collect data. This is what queries are for.
You can combine queries in a UNION query.

a memo (long string) field can combine other fields.

 

[TyeReece]

I've done a union query before but don't see how that would work here. The data are all in one table and any ID may have up to 10 diagnoses out of 300 or so possible diagnoses. Help me understand what you mean.

 

[Micron]

Sample results might help. I'm thinking you want to combine Autism Spectrum Disorder & Communication Disorder (Includes NOS and Unspecified) into one field. Why?? This just seems like a violation of db normalization principles. Maybe if we knew what the end goal is we could provide more focused answers.

Where do you see DAO in that code? I only see ADODB. If you Google the difference, watch out for out of date articles. M$ intended to drop support for DAO then reversed course. They went back to continuing to further develop DAO. While I've seen a post from a very respective developer who stated that DAO is really ACE now, I suspect that's incorrect. Fairly certain that ACE replaced JET. Not what you asked, I know. But seeing as you're inquisitive and might study this further, consider all that cautionary.

DAO stands for "Data Access Objects" and ADO stands for "ActiveX Data Objects". The latter is what you'd use to connect to external data sources. If you're working only in Access, DAO is simpler for my money. They are called project libraries and are found under Tools > References in the vb editor.

 

[james_lankford]

are you saying you want to return one column that contains all DSM_IV that have an ID of 3 ?

so all the DSM_IVs together separated by commas or something like that ?

Anxiety Disorder (Includes Other Specified/Unspecified/NOS), Disruptive Mood Dysregulation Disorder, Pica, PTSD (Posttraumatic Stress Disorder)

 

[TyeReece]

Yes, but I would also include the ID number in a separate column. So the results would look like:

ID Diagnosis
1 Autism Spectrum Disorder,Communication Disorder (Includes NOS and Unspecified)
2 Reactive Attachment Disorder (RAD)
3 Anxiety Disorder (Includes Other Specified/Unspecified/NOS), Disruptive Mood Dysregulation Disorder, Pica, PTSD (Posttraumatic Stress Disorder)

 

[Micron]

AFAIK, this can only be done via code. See concat related here