Function to concatenate values into a string based upon criteria

[pharrison]

Hi,

I have been using Access for a while, but know nothing about VBA. I have a problem that I can't resolve using Access' built in query builder functions and am therefore assuming that I need to use VBA. However, I haven't a clue where to begin

Here's the problem:

I have a table containing data like:

ID | Club | ClubType
--------------------
1 | Club1 |
1 | Club6 | Type2
2 | Club3 | Type1
4 | Club1 |
4 | Club3 | Type1

This is then joined to another table that, amongst other data, lists the unique ID values.

I would like to query the tables to extract a string listing the clubs for each ID where ClubType does not equal certain values e.g. Type1, like so:

ID | Clubs
-------------
1 | Club1, Club6
2 |
3 |
4 | Club1

Your help would be very much appreciated

 

[Cosmos75]

Try this.

DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link).

It shows you how to return
ID | Clubs
-------------
1 | Club1, Club6
2 | Club3
3 |
4 | Club1, Club3

But you could alter the code to filter out certain values, but it may be better to use it as-is and then use some text functions like Replace() and so on on the results.

Hope this helps!