multiple unions ms access

Question

I've been looking for an example online for multiple unions on a single table, only solution I found similar to the issue I am having is this thread.

This is the query i've built in MS ACCESS

  SELECT chat_tb.tag_1 AS [tag_desc], Count(chat_tb.tag_1) AS [Count_of_tag] FROM chat_tb GROUP BY chat_tb.tag_1
UNION
SELECT chat_tb.tag_2 AS [tag_desc], Count(chat_tb.tag_2) AS [Count_of_tag]  FROM chat_tb GROUP BY chat_tb.tag_2
UNION 
SELECT chat_tb.tag_3 AS [tag_desc], Count(chat_tb.tag_3) AS [Count_of_tag]  FROM chat_tb GROUP BY chat_tb.tag_3;

But I get the following error, Im assuming since it is linking to MySQL table I cannot perform the union as the current syntax, can anyone assist me in changing it to mysql format, thank you in advance!

NOTE: If I remove the last union the statement works.

View Error Message

ODBC--call failed. {MySQL]{ODBC 3.51 Driver]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' Union (Select 'tag',Count('tag_2') FROM 'chat_tb" GROUP BY 'tag_2')) UNIO' at line 1 (#1064)

I found a another thread similar issues.

and did the following:

 SELECT chat_tb.tag_1 AS [tag_desc], Count(chat_tb.tag_1) AS [Count_of_tag] FROM chat_tb GROUP BY chat_tb.tag_1;
UNION ALL (

SELECT chat_tb.tag_2 AS [tag_desc], Count(chat_tb.tag_2) AS [Count_of_tag]  FROM chat_tb GROUP BY chat_tb.tag_2;

UNION ALL

SELECT chat_tb.tag_3 AS [tag_desc], Count(chat_tb.tag_3) AS [Count_of_tag]  FROM chat_tb GROUP BY chat_tb.tag_3
)); 

This gives me a "Syntax error in union query"

Answer 1

I have just managed to replicate your problem. What you are doing is attempting to run the Query as an Access query on a MySQL linked table. Having done a bit of research this appears to be a known bug: see here. You cannot have more than one union. It does however work as SQL Pass-Through. On the design tab select Pass-Through (globe symbol) under Union. This does mean you need to select your DNS to run it.