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;

Update: 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.