Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: Select Distinct and Join

    Is there a query i can write to select distinct names and join specific columns For example

    Name Service
    Lawrence Auto
    Lawrence Hair
    Larry Auto

    Results

    Name Service
    Lawrence Auto, Hair
    Larry Auto

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not the best of solutions, but you could create a public function and call it from a query:
    Code:
    Public Function MergeRows(ByVal Name As Variant) As Variant
    
        Const c_SQL As String = "SELECT Service FROM Tbl1 WHERE Name = '@N';"
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@N", Name)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(Nz(!Service, "")) > 0 Then
                    If Len(MergeRows) > 0 Then MergeRows = MergeRows & ", "
                    MergeRows = MergeRows & !Service
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Then:
    Code:
    SELECT Name, Mergerows([Name]) AS Service
    FROM Tbl1
    GROUP BY Name;
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    Dont yuo think you could help me code that for this table
    Im tryingto combine all this info to be a line per person

    f2 f3 f4 f5 f6 f7
    09:00 A *Q19699 Megan Honeycutt RR JENNY: Jenny McCoy LZFACEFL1: Face Full Laser 1TX
    09:30 A *Q19699 Megan Honeycutt RR JENNY: Jenny McCoy LZNCKFRT1: Neck Front Of - Laser 1TX
    10:00 A *Q13976 Tess Wilmans NO JENNY: Jenny McCoy LZLEGLWR1: Legs Lower 1TX
    10:30 A *Q13976 Tess Wilmans NO JENNY: Jenny McCoy LZLEGUPR1: Leg Upper Laser 1TX
    11:15 A *Q18132 Tiffany Anderson NO JENNY: Jenny McCoy LZBRAZIL1: Brazilian Laser 1TX
    11:50 A *Q8821* Megan Thomas NO JENNY: Jenny McCoy LZUNRARM1: Underarms Laser 1 TX
    12:00 P *Q16006 Lisa Bryant NO JENNY: Jenny McCoy LZUNRARM1: Underarms Laser 1 TX
    12:10 P *Q16006 Lisa Bryant NO JENNY: Jenny McCoy LZCHIN1: Chin Laser Hair 1 TX
    12:20 P *Q16006 Lisa Bryant NO JENNY: Jenny McCoy LZLIP1: Lip Laser Hair 1 TX
    12:30 P *Q21063 Heather Blackmon RR JENNY: Jenny McCoy LZFACEFL1: Face Full Laser 1TX
    02:00 P *Q18887 Lindsey Cosio NR JENNY: Jenny McCoy LZLEGFLL1: Leg Full Laser 1TX
    03:30 P *Q19438 Julie Barnes RR JENNY: Jenny McCoy LZLIP1: Lip Laser Hair 1 TX
    03:40 P *Q19438 Julie Barnes RR JENNY: Jenny McCoy LZCHIN1: Chin Laser Hair 1 TX
    03:50 P *Q21572 Nadia D'avinon NC JENNY: Jenny McCoy LZFACEFL1: Face Full Laser 1TX
    04:30 P *Q15472 Slavica Dabic (Z00000015472) NR JENNY: Jenny McCoy LZLEGLWR1: Legs Lower 1TX
    05:00 P *Q15472 Slavica Dabic (Z00000015472) NR JENNY: Jenny McCoy LZARMFLL1: Arm Full Laser 1TX

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please re-post the data in a csv format (i.e. each row separated from the next with a semicolon ( ; ). As it is it's unusable.
    Have a nice day!

  5. #5
    Join Date
    Mar 2013
    Posts
    6
    Ive attached a picture is that alright?
    Attached Thumbnails Attached Thumbnails rxlaser.JPG  

  6. #6
    Join Date
    Mar 2013
    Posts
    6
    Is that good??

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No it's not, sorry! For writing such a function and testing it to be sure that it works according to your specifications, I need a table with some sample data. I can easily create such a table from a text in csv format, while I don't have the time nor the will to re-type values from a picture.
    Have a nice day!

  8. #8
    Join Date
    Mar 2013
    Posts
    6

    Is this the format?

    f2 ; f3 ; f4 ; f5 ; f6 ; f7
    9:00 AM ; *Q21197 ; Jackie Fuller ; RR ; CARA: Cara Carter ; ESBRWSHP:
    9:15 AM ; *Q21197 ; Jackie Fuller ; RR ; CARA: Cara Carter ; ESBRZWAX:
    10:00 AM ; *Q21419 ; Connie Hinson ; NC ; CARA: Cara Carter ; ESHLEGWX:
    10:30 AM ; *Q21419 ; Connie Hinson ; NC ; CARA: Cara Carter ; ESBRZWAX
    11:00 AM ; *Q21462 ; Sandra J Smith ; NC ; CARA: Cara Carter ; ESBRZWAX:
    2:00 PM ; *Q21212 ; Juli Summit ; RR ; CARA: Cara Carter ; ESBRZWAX
    3:45 PM ; *Q21493 ; Juliann E Kuhn ; NC ; CARA: Cara Carter ; ESBRZWAX
    4:15 PM ; *Q13876 ; Ruthie Leggett ; RR ; CARA: Cara Carter ; ESBRWSHP

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That will do.

    When I see the data, I'm not sure of how you want to merge everything. Anyway, based on the function in my former post, here is a solution.
    Code:
    Public Function MergeRows(ByVal Name As Variant) As Variant
    
        Const c_SQL As String = "SELECT * FROM Tbl1 WHERE f4 = '@N';"
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@N", Name)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(MergeRows) > 0 Then MergeRows = MergeRows & ", "
                MergeRows = MergeRows & !f2 & " " & !f3 & " " & !f5 & " " & !f6 & " " & !f7
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Whith this query:
    Code:
    SELECT f4, Mergerows([f4]) AS Service
    FROM Tbl1
    GROUP BY f4;
    you get:
    Code:
    f4		Service
    Connie Hinson	10:00 AM *Q21419 NC CARA: Cara Carter ESHLEGWX:, 10:30 AM *Q21419 NC CARA: Cara Carter ESBRZWAX
    Jackie Fuller	9:00 AM *Q21197 RR CARA: Cara Carter ESBRWSHP:, 9:15 AM *Q21197 RR CARA: Cara Carter ESBRZWAX:
    Juli Summit	2:00 PM *Q21212 RR CARA: Cara Carter ESBRZWAX
    Juliann E Kuhn	3:45 PM *Q21493 NC CARA: Cara Carter ESBRZWAX
    Ruthie Leggett	4:15 PM *Q13876 RR CARA: Cara Carter ESBRWSHP
    Sandra J Smith	11:00 AM *Q21462 NC CARA: Cara Carter ESBRZWAX:
    Have a nice day!

  10. #10
    Join Date
    Mar 2013
    Posts
    6
    THANK YOU!!!!
    Is there away, instead of separating them by commas, can make it is own columns like "2nd Service"

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not easily, at least with a VBA function. With such a requirement, I would investigate the possibilities of a Crosstab query, although it probably would be quite complex with Access.
    Have a nice day!

Posting Permissions

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