Grouping joined lookup values in Access

Question

My database includes several lookup tables (shown as pulldown menus on the UI form).

For example,

customer_data - customer demographic info.

lookup_car - stores car descriptions (Pinto, Vega, Reliant Robin, Mustang, Corvette)

junction_car_customer - joins a customer with one or more cars

Customer Jeremy Clarkson (cust_id: 1) owns three cars. The dropdown for his record shows:

Pinto (car_id=100)
Reliant Robin (car_id=101)
Vega (car_id=102)

The junction_car_customer data looks like this:

cust_id    car_id
1          100
1          101
1          102

I'm trying to return a row showing the customer name and the models owned (as a semi-colon delimited string).

Here's my query:

SELECT 
 cd.cust_id,
 cd.name_first,
 cd.name_last,
 jcc.car_id,
 lc.car_desc
FROM
 ((customer_data AS cd)
 LEFT JOIN ju_cust_car AS jcc ON jcc.cust_id = cd.cust_id)
 LEFT JOIN lookup_cars AS lc ON lc.car_id = jcc.car_id
ORDER BY 
 cd.name_last

This returns:

cust_id name_first name_last car_id car_desc
1       Jeremy     Clarkson  100    Pinto
1       Jeremy     Clarkson  101    Reliant Robin
1       Jeremy     Clarkson  102    Vega

What I'd like is:

cust_id name_first name_last car_desc
1       Jeremy     Clarkson  Pinto;Reliant Robin;Vega

Is there an efficient way of returning the above result?

Access SQL does not include a built-in feature which concatenates values from separate rows. You can use a custom VBA function such as Allen Browne's [ConcatRelated](http://allenbrowne.com/func-concat.html). However, any such method imposes a performance hit due to the amount of work Access must do to gather the values which you concatenate. — HansUp, Nov 04 '13 at 14:21

score 1 · Accepted Answer · answered Nov 04 '13 at 15:10

1

As HansUp says, you need to use a custom VBA function. If the data is fairly static, you can speed things up by caching the results. So...

1) In the VB editor, add a reference to the 'Microsoft Scripting Runtime' (we'll be needing the Dictionary class from this library).

2) Create a new standard module, and add code to it like the following:

Option Explicit

Private mCache As New Scripting.Dictionary

Sub ClearCarDescCache(Optional cust_id)
  If IsMissing(cust_id) Then
    mCache.RemoveAll
  Else
    mCache.Remove CInt(cust_id)
  End If
End Sub

Function GetCarDescList(cust_id) As String
  If mCache.Exists(cust_id) Then
    GetCarDescList = mCache(cust_id)
    Exit Function
  End If
  Dim RS As DAO.Recordset, S As String
  Set RS = CurrentDb.OpenRecordset( _
    " SELECT car_desc " + _
    " FROM junction_car_customer INNER JOIN lookup_car " + _
    "   ON junction_car_customer.car_id = lookup_car.car_id " + _
    " WHERE cust_id = " & cust_id & _
    " ORDER BY car_desc", dbOpenForwardOnly)
  While Not RS.EOF
    If Len(S) = 0 Then
      S = RS(0)
    Else 
      S = S + ";" & RS(0)
    End If
    RS.MoveNext
  Wend
  mCache.Add cust_id, S
  GetCarDescList = S
End Function

3) The main query can now look like this:

SELECT cust_id, name_first, name_last, GetCarDescList(cust_id) AS car_desc
FROM customer_data
ORDER BY name_last

4) Add explicit calls to ClearCarDescCache as appropriate.

answered Nov 04 '13 at 15:10

Chris Rolliston

4,788
1
16
20

Compile error: User-defined type not defined. – Georgia Road Nov 04 '13 at 15:19
@GeorgiaRoad - where? – Chris Rolliston Nov 04 '13 at 15:22
`mCache As New Scripting.Dictionary` – Georgia Road Nov 04 '13 at 15:24
1

@GeorgiaRoad - did you add the reference to the Microsoft Scripting Runtime as outlined in step (1)? – Chris Rolliston Nov 04 '13 at 15:27
I did not - apologies. Adding now. [upd, compiles OK now] – Georgia Road Nov 04 '13 at 15:28
This works well, and on my <1000 row table performance is not an issue (using six of the above examples modified for other lookup/junction tables). – Georgia Road Nov 04 '13 at 19:40
@GeorgiaRoad - I've found the caching has most benefit when the data is shown in a datasheet view. This is because Access will seek to refresh the data on an oddly frequent basis, making lots of apparently unnecessary calls to the custom function in the process (and enumerating a recordset in VBA code is much slower than in pure SQL). – Chris Rolliston Nov 04 '13 at 19:51

Grouping joined lookup values in Access

1 Answers1