Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: combining multiple records

  1. #1
    Board Regular
    Join Date
    Aug 2007
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default combining multiple records

    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 by TyeReece; Sep 14th, 2018 at 04:03 PM.

  2. #2
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,705
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining multiple records

    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.

  3. #3
    Board Regular
    Join Date
    Aug 2007
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining multiple records

    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.

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,619
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining multiple records

    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  5. #5
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining multiple records

    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)

  6. #6
    Board Regular
    Join Date
    Aug 2007
    Posts
    127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining multiple records

    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)

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,619
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: combining multiple records

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •