Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concantonating Field Contents from Different Rows - Access 2010

    I have a table that contains customer information. I would like to create a query or other method that would show me how many "ratings" a customer has. The three fields I need in the results are Company, Customer and Rating. All three fields are numeric. I do NOT need to know how many of each rating the customer has, just how many they have. I would like the results to look something like:

    Company Customer Rating
    12 Smith John 4,6,9

    The table has separate records for each transaction with the customer. I can do a summary query that gives my ratings and customers, but am not sure how to get something that will give me one row with all of the ratings combined.

    Thanks for any ideas you may have.

    Nancy

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You could use a crosstab query perhaps

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need the PD groups to end up in a field, so a crosstab won't work. Thanks for the suggestion.

    Nancy

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Is the number of ratings known and fixed ? If it is, you could write a query that included some correlated subqueries in the SELECT clause. It's a bit of work and it's not really expandable, as you'd have to add a new subquery for each additional rating.

    Another possible way, much easier and that could accommodate any number of ratings, would be to write a VBA function to obtain the values of the ratings, for a given customer, and return the ratings as a string.
    Rui
    -------
    R4

  5. #5
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rui,

    The number of ratings isn't fixed. The majority of customers only have one, but they could have up to 14 (no one has that many). It is not unusual to have three or four.

    Could you give me an idea how to write a function to return the ratings as a string? I am not experienced in writing them. Or point me to a place where I could look?

    Thanks

    Nancy

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I wrote the original code back in 1997, but it still runs in Access 2013. I would probably write it differently today, since I would rather use a different way to access the database. Anway, here it is an example:

    Code:
    Public Function GetFullName(lngID As Long) As String
    Dim rstFullName As Recordset
    Dim intwCount As Integer
    Dim strSql As String
    Dim db As Database
    Dim strkWord As String
    
    Set db = CurrentDb()
    
    strSql = "SELECT ColumnA "
    strSql = strSql & " FROM Tbl_MyTable"
    strSql = strSql & "WHERE ( RecordId=" & lnglID & ")"
    
    Set rstFullName = db.OpenRecordset(strSql)
                
    intwCount = 0
    
    Do Until rstFullName.EOF  
    
        If intwCount = 0 Then
            strkWord = rstFullName!ColumnA  'ColumnA is the field name that has the value I want to concatenate
            intwCount = 1
        Else
            strkWord = strkWord & " " & rstFullName!ColumnA
        End If
    
        rstFullName.MoveNext
        
    Loop
    
    rstFullName.Close
    
    GetFullName= strkWord
    
    End Function
    The function can then be used in a query, just like a native Access function.
    Rui
    -------
    R4

Posting Permissions

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