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"

Remove the first two semi-colons (ie the ones before the two union statements) — Jonathan Willcock, Aug 16 '17 at 14:30
Also no need give each query the alias field name as the query will just use those on the first query - so can remove `AS [tag_desc]`, etc from second two queries. — Darren Bartrup-Cook, Aug 16 '17 at 14:33
@JonathanWillcock this did not change anything unfortunately, I think I require MySql SQL Statement since MS Access is Link to a MySQl table. — Noob Prgmr, Aug 16 '17 at 14:36
You may want to redesign your database to - you have fields `tag_1`, `tag_2` and `tag_3` which look like they hold the same kind of info. Is `tag_2` and `tag_3` null if `tag_1` holds a value? If so you could just have `tag` which holds text to differentiate between the three tags. You could then just count tags and group by them to get the result you're after. — Darren Bartrup-Cook, Aug 16 '17 at 14:38
Can you edit your post with the new query that doesn't work and post the error, if it is now different? — Jonathan Willcock, Aug 16 '17 at 14:39
@DarrenBartrup-Cook i do not have the option to modify this table unfortunately. tags 1 to 3 can contain null and both unique and duplicate values. — Noob Prgmr, Aug 16 '17 at 14:44
What does the error message say - can't the attached pic where I am at the moment? I take it `chat_tb` is a MySQL table and not an Access query that references a MySQL talbe? Does it work if you remove one of the unions (as was possible in the linked thread)? — Darren Bartrup-Cook, Aug 16 '17 at 14:49
@DarrenBartrup-Cook yes When I take our the last union everything works fine. — Noob Prgmr, Aug 16 '17 at 14:51
@DarrenBartrup-Cook just inserted the error written in the original post — Noob Prgmr, Aug 16 '17 at 14:57
Pretty sure that last update will be the semi-colons. You don't actually need to put any in, but if you do put one it it must be the very last character in the SQL statement. — Darren Bartrup-Cook, Aug 16 '17 at 14:57
Sorry just seen the other problem - you do not need the brackets — Jonathan Willcock, Aug 16 '17 at 14:58
@DarrenBartrup-Cook I've tried the following removed all semi colons, inserted all semicolons, kept the last one and all produce the same exact error — Noob Prgmr, Aug 16 '17 at 14:59
It is a linked table and not a pass-through query isn't it? I'm a bit confused as the error message says `tag` and `tag2` and `tag_2`. — Darren Bartrup-Cook, Aug 16 '17 at 15:07
@DarrenBartrup-Cook its a linked table and not a pass through, sorry typo just adjusted the error both are written as tag_2 — Noob Prgmr, Aug 16 '17 at 15:09
are you sure there's a space between union and select of the next line? — xQbert, Aug 16 '17 at 15:21
Sorry, not sure I can help further. If all the suggestions so far haven't helped it's looking more like the MySQL side of things. Saying that - just noticed your second SQL code block has an extra close bracket on the end. As far as I can tell this should work: `SELECT tag_1 AS [tag_desc], Count(tag_1) AS [Count_of_tag] FROM chat_tb GROUP BY tag_1 UNION SELECT tag_2, Count(tag_2) FROM chat_tb GROUP BY tag_2 UNION SELECT tag_3, Count(tag_3) FROM chat_tb GROUP BY tag_3` — Darren Bartrup-Cook, Aug 16 '17 at 15:40

score 3 · Accepted Answer · answered Aug 16 '17 at 16:17

3

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.

answered Aug 16 '17 at 16:17

Jonathan Willcock

5,012
3
20
31

1

This is an interesting bug. Still in MS Access 13/16 as bug points to much earlier version of Access and MySQL? How about if you remove parentheses around `SELECT` statements? – Parfait Aug 16 '17 at 17:07
1

@Parfait I tried and tried. I even tried cheating by splitting it into two Access queries (which both separately worked) and then doing the union on those; but no joy! I think actually the bug is not in Access, but is in the ODBC driver, which comes from Oracle. I have used ODBC in Access with other back-ends and never seen the same. – Jonathan Willcock Aug 16 '17 at 17:12
1

@JonathanWillcock Thank you for this! – Noob Prgmr Aug 17 '17 at 14:05
1

@NoobPrgmr My pleasure. Have a great day. – Jonathan Willcock Aug 17 '17 at 14:10

multiple unions ms access

1 Answers1