combining multiple records

TyeReece

Board Regular
Joined
Aug 3, 2007
Messages
136
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?

Code:
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
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top