Is There a Group_Concat Function in Ms-Access

is there a group_concat function in ms-access?

You should ask yourself if you need a generic solution (another is by Allen Browne) or if you need it just for the present purpose. If you really only need it this once, do it the easy way.

On a side note, when concatenating lists in VBA code, take advantage of a trick taught to me by long-time Access guru Trevor Best, and that's to stick the delimiter at the beginning of every value and then use Mid() to strip it off. Instead of this inside your loop through the child records:

  If Len(strOutput) = 0 Then
     strOutput = NewValue
  Else
     strOutput = strOutput & ", " & NewValue
  End If

...use this inside the loop:

  strOutput = strOutput & ", " & NewValue

...and then when you exit the loop, strip off the leading delimiter:

  strOutput = Mid(strOutput, 3)

This has implications all over the place and simplifies code for concatenation in a whole host of contexts.

Converting MySQL code to Access: GROUP_CONCAT and a triple JOIN

The most commonly-cited Access alternative to the MySQL GROUP_CONCAT() function is Allen Browne's ConcatRelated() function, available here.

As for parentheses around JOINs, yes, Access SQL is fussy about those. Instead of

FROM
actor AS a
LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id
LEFT JOIN film_category AS fc ON fa.film_id = fc.film_id
LEFT JOIN category AS c ON fc.category_id = c.category_id

try

FROM 
    (
        (
            actor AS a 
            LEFT JOIN 
            film_actor AS fa 
                ON a.actor_id = fa.actor_id
        ) 
        LEFT JOIN 
        film_category AS fc 
            ON fa.film_id = fc.film_id
    ) 
    LEFT JOIN 
    category AS c 
        ON fc.category_id = c.category_id

Concatenate records and GROUP BY in Access

There is no Group_Concat in Access :/. Probably there is no solution that excludes VBA.

Here is one possible: Concatenating Rows through a query

SQL Query to Group By and Concat rows

Sounds like you're looking for a group_concat like function found in MySQL. Check out Allen Browne's solution

SELECT Doc, code, Sum(Qty), ConcatRelated("[Next]","TableName",,,"-") as [Next]
FROM TableName
GROUP BY Doc, code

MS ACCESS Group Query

As i mentioned in my comment to the question, normalized table should look like:

|_Category_|_F_Name_|_F_Val__|
|----------|--------|--------|
|   CAT1   | FieldA |    A   |
|----------|--------|--------|
|   CAT1   | FieldB |    B   |
|----------|--------|--------|
|   CAT1   | FieldC |    C   |
|----------|--------|--------|
|   CAT1   | FieldB |    D   |
|----------|--------|--------|
|   CAT1   | FieldC |    E   |
|----------|--------|--------|
|   CAT1   | FieldA |    F   |
|----------|--------|--------|

How to achieve that?

SELECT A.Category, "FieldA" AS FieldName, A.FieldA AS FieldValue
FROM TableA AS A
WHERE NOT A.FieldA IS NULL
UNION ALL
SELECT A.Category, "FieldB", A.FieldB
FROM TableA AS A
WHERE NOT A.FieldB IS NULL
UNION ALL
SELECT A.Category, "FieldC", A.FieldC
FROM TableA AS A
WHERE NOT A.FieldC IS NULL;

To export the data into new table, use query:

SELECT B.* INTO TableB
FROM (
   --above query
)  AS B;

Do not forget to add autonumber field (as primary key) to TableB to be able to identify each record.

As per my understanding, you want to pivot data. It's not so simple, becasue we need to simulate

ROW_NUMBER() OVER(PARTITION BY FieldName, ORDER BY ID)

which is not supported in MS Access. How to workaround it?

SELECT B.ID, B.Category, B.FieldName, B.FieldValue,
       (SELECT COUNT(A.FieldName)
        FROM TableB AS A
        WHERE A.FieldName=B.FieldName AND A.ID >=B.ID
        GROUP BY A.FieldName ) AS TRank
FROM TableB AS B;

It should produce below record set:

ID  Category    FieldName   FieldValue  TRank
1   CAT1        FieldA      A           3
2   CAT1        FieldA      D           2
3   CAT1        FieldA      F           1
4   CAT1        FieldB      B           1
5   CAT1        FieldC      C           2
6   CAT1        FieldC      E           1

But... you can't use above query as a source of pivot data, because of "The Microsoft Access database engine does not recognize as a valid field name or expression. (Error 3070)" error message. So, finally, you should export these data into another table (let's say TableC).

SELECT C.* INSERT INTO TableC
FROM TableB AS C

Now, you can pivot data:

TRANSFORM First(A.FieldValue) AS FirstOfFieldValue
SELECT A.Category, A.TRank
FROM TableC AS A
GROUP BY A.Category, A.TRank
PIVOT A.FieldName;

Result:

Category    TRank   FieldA  FieldB  FieldC
CAT1        1       F       B       E
CAT1        2       D       C
CAT1        3       A       

Cheers,

Maciej

---

Related Topics