ms access - SQL, combining the result

Question

I am using Access and have this SQL

SELECT land.id, land.official_name, vaksiner.vaksiner
FROM land INNER JOIN (vaksiner INNER JOIN land_sykdom ON vaksiner.id = land_sykdom.sykdom)        ON land.kort = land_sykdom.land
ORDER BY land.official_name

The SQL gives me a result like this:

id    official_name    vaksiner

1     a                A
1     a                C    
2     b                A
2     b                B
2     b                C

But I want to combine the result so that it looks like this:

id    official_name    vaksiner

1     a                A, C
2     b                A, B, C

Answer 1

See Allen Browne's ConcatRelated function (Concatenate values from related records)

If you save your existing query as qryVaksinerRaw, you can build a new query like this to produce the results you're after.

SELECT DISTINCT
    id,
    official_name,
    ConcatRelated("vaksiner","qryVaksinerRaw","id = " & [id]) AS vaksiner
FROM qryVaksinerRaw;

To add the function to a module, copy the function from the web page starting with

Public Function ConcatRelated(strField As String, _

and continuing to include

End Function

Then paste the copied text into your module.

Note ConcatRelated() will then be available for queries run from inside Access. However, like any custom VBA function, it can not be used in queries run from outside Access (like classic ASP, .Net, etc.)