Stumped by Union Query

G

Guest

Hello learned guru's, boffins and hackers,

I have a perplexing problem.

The object of the exercise is to transpose the fields of one record into a
listbox. Using a UNION query I am able to get two SELECT queries into the
listbox but when I add more I get an" ODBC - call failed" error.

Listbox.rowsource =
SELECT clientsub.clisid, "First Name: " AS f1, [Fname] AS f2, "Fname" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Last Name: " AS f1, [Lname] AS f2, "Lname" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Position: " AS f1, [Position] AS f2, "Position" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Location: " AS f1, [Location] AS f2, "Location" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Work Phone: " AS f1, [Wphone] AS f2, "Wphone" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Mobile Phone: " AS f1, [mphone] AS f2, "mphone" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Email: " AS f1, AS f2, "Email" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]));

Any two SELECT statements joined with a UNION work fine, however add a third
and the failure occurs.

Access 2003 linked via MySQL ODBC 3.51 driver into MySQL Server 5.0.21

I can not figure out why the error is occuring. Can anyone shed some light
on this problem?

Thanks in advance

Nick.
 
B

Baz

Error 3146 means that MySQL has generated an error, not Access. It's
impossible to tell from the Access SQL what the MySQL ODBC driver has done
(unless anyone around here has intimate knowledge of the thing, which is
unlikely). Your mission is to find out exactly what SQL actually arrives at
the MySQL server, and what is wrong with it, and for that you need to be
asking MySQL experts.
 
G

Guest

Thanks Baz,

I wasn't thinking "outside of the box". I will the investigate the MySQL
line and post any solution I find.

Baz said:
Error 3146 means that MySQL has generated an error, not Access. It's
impossible to tell from the Access SQL what the MySQL ODBC driver has done
(unless anyone around here has intimate knowledge of the thing, which is
unlikely). Your mission is to find out exactly what SQL actually arrives at
the MySQL server, and what is wrong with it, and for that you need to be
asking MySQL experts.

Biz Enhancer said:
Hello learned guru's, boffins and hackers,

I have a perplexing problem.

The object of the exercise is to transpose the fields of one record into a
listbox. Using a UNION query I am able to get two SELECT queries into the
listbox but when I add more I get an" ODBC - call failed" error.

Listbox.rowsource =
SELECT clientsub.clisid, "First Name: " AS f1, [Fname] AS f2, "Fname" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Last Name: " AS f1, [Lname] AS f2, "Lname" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Position: " AS f1, [Position] AS f2, "Position" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Location: " AS f1, [Location] AS f2, "Location" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Work Phone: " AS f1, [Wphone] AS f2, "Wphone" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Mobile Phone: " AS f1, [mphone] AS f2, "mphone" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Email: " AS f1, AS f2, "Email" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]));

Any two SELECT statements joined with a UNION work fine, however add a third
and the failure occurs.

Access 2003 linked via MySQL ODBC 3.51 driver into MySQL Server 5.0.21

I can not figure out why the error is occuring. Can anyone shed some light
on this problem?

Thanks in advance

Nick.[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
G

Guest

Seems that MySQL ODBC driver has an issue with multiple UNION statements.
Even failed on the v5 ODBC driver. My solution was to transpose data into a
local table first and use that as the record source for the listbox.

Biz Enhancer said:
Thanks Baz,

I wasn't thinking "outside of the box". I will the investigate the MySQL
line and post any solution I find.

Baz said:
Error 3146 means that MySQL has generated an error, not Access. It's
impossible to tell from the Access SQL what the MySQL ODBC driver has done
(unless anyone around here has intimate knowledge of the thing, which is
unlikely). Your mission is to find out exactly what SQL actually arrives at
the MySQL server, and what is wrong with it, and for that you need to be
asking MySQL experts.

Biz Enhancer said:
Hello learned guru's, boffins and hackers,

I have a perplexing problem.

The object of the exercise is to transpose the fields of one record into a
listbox. Using a UNION query I am able to get two SELECT queries into the
listbox but when I add more I get an" ODBC - call failed" error.

Listbox.rowsource =
SELECT clientsub.clisid, "First Name: " AS f1, [Fname] AS f2, "Fname" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Last Name: " AS f1, [Lname] AS f2, "Lname" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Position: " AS f1, [Position] AS f2, "Position" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Location: " AS f1, [Location] AS f2, "Location" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Work Phone: " AS f1, [Wphone] AS f2, "Wphone" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Mobile Phone: " AS f1, [mphone] AS f2, "mphone" AS
flit FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]))
UNION
SELECT clientsub.clisid, "Email: " AS f1, AS f2, "Email" AS flit
FROM clientsub WHERE (((clientsub.clisid)=[Forms]![rclient]![List51]));

Any two SELECT statements joined with a UNION work fine, however add a third
and the failure occurs.

Access 2003 linked via MySQL ODBC 3.51 driver into MySQL Server 5.0.21

I can not figure out why the error is occuring. Can anyone shed some light
on this problem?

Thanks in advance

Nick.[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union query 5
Union Query - Select Distinct 2 fields 7
Union Query Edit 1
UNION Query 3
"Query Too Complex" Access 2000 help me make this one query less complex? 0
Join on a UNION query 2
Union query help - multiple unions 6
Union Query order By clause Error 3

Top