Works in MySql but not in Ms Access ODBC bug. I need a work around

Question

Works in MySql but not in Ms Access ODBC bug. I need a work around

marishasingh82

Old Hand

Points: 397
More actions
October 21, 2011 at 12:07 pm

#246812

Hello All,
My database is in MySql and I am developing a reporting tool in Ms Access. So I link to my MySql database and I use Ms Access report wizard for creating reports.
Paper_CB_table
BuildingIDMonthYearVendorIDPaperQuantityCardboardQuantity
949 January2011 3 26 78
949 January2011 6 67 0
949 February2011 3 54 89
949 February2011 6 45 0
I want the report in this format
BuildingIDMonthYearPaperQuantityCardboardQuantity
949 January2011 93 78
949 February2011 99 89
I use a UNION query in MySql and I get the result. But when I tried to use more than two UNION like
SELECT .... UNION SELECT..... UNION SELECT ...
It gave me error.(#1064). It is an error in that ODBC 5.1 . I cannot use more than one UNION in a query. I have no idea how else can I get the report in the format that I want now. Is this a table design fault that I should correct. I have almost 60 buildings and I have to prepare annual reports.
so the month range will be (Jan - Dec)
I hope you can guide me here

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

since MySQL is preventing you from multiple UNIONs, you'll end up having to insert into a temp table (MySQL has temp tables, right?

3 unions would be three inserts, all into the same temp table, and then select fromt eh temp table for the final result?

the MySQL page certainly implies multiple unions are OK...

http://dev.mysql.com/doc/refman/5.0/en/union.html

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sbelche-760308 SSC-Addicted Points: 467 More actions · Answer 2

It sounds like the OP is using Access to generate the reports not MYSQL. Access won't let you do multiple unions.

marishasingh82 Old Hand Points: 397 More actions · Answer 3

Yes my database is in MySql... I am generating reports in Ms Access by linking to MySql

System Data Source

Driver: MySQL ODBC 5.1 Driver

There is a bug I read about it on MySql Db website. That ODBC driver will not allow more than one UNION.

I am not sure what are other ways of getting the data in the format that I want. Is my table design wrong that is why I am asking this problem or this kind of data is ok in the table.

David Moutray SSCertifiable Points: 5956 More actions · Answer 4

Paper_CB_table
BuildingIDMonthYearVendorIDPaperQuantityCardboardQuantity
949 January2011 3 26 78
949 January2011 6 67 0
949 February2011 3 54 89
949 February2011 6 45 0
I want the report in this format
BuildingIDMonthYearPaperQuantityCardboardQuantity
949 January2011 93 78
949 February2011 99 89

Based on the sample data, it looks like you want a sum. Try this:

SELECT BuildingID, Month, Year, SUM(PaperQuantity), SUM(CardboardQuantity)

FROM Paper_CB_Table

GROUP BY Year, Month, BuildingID

marishasingh82 Old Hand Points: 397 More actions · Answer 5

Yes the query works. Thank you I dont need UNION now so I do not have to worry about the ODBC bug ... Thank you 🙂