title: Converting MySQL code to Access: GROUP_CONCAT and a triple JOIN
body: <p>I'm having a really hard time translating a piece of MySQL-code to Access.
  I'm trying to use one of the queries found in the Sakila (MySQL) Database for an Access project I'm working on. </p>
  
  <p>First of all, the GROUP_CONCAT function doesn't work at all. After some Google searches I found out that Access doesn't support this function but I couldn't find a working alternative. CONCAT however could be replaced by a few '+' operators.  </p>
  
  <p>Then comes the triple LEFT JOIN which kept returning a missing operator error. I found a blog post explaining how a series of brackets could help, but this resulted in even more trouble and prompted me to remove the brackets after which it threw more missing operator errors.  </p>
  
  <p>Also, SEPARATOR doesn't seem to be accepted as well, but this could be due to GROUP_CONCAT not functioning.</p>
  
  <p>Is there anyone willing to get me in the right direction? I've been struggling with this for way too long. </p>
  
  <pre><code>SELECT
  a.actor_id,
  a.first_name,
  a.last_name,
  GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
      (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
                  FROM film f
                  INNER JOIN film_category fc
                    ON f.film_id = fc.film_id
                  INNER JOIN film_actor fa
                    ON f.film_id = fa.film_id
                  WHERE fc.category_id = c.category_id
                  AND fa.actor_id = a.actor_id
               )
           )
           ORDER BY c.name SEPARATOR '; ')
  AS film_info
  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
  GROUP BY a.actor_id, a.first_name, a.last_name
  </code></pre>