Error Message on Union Query

[mrssevans]

I am trying to make a union query out of two tables in a unix database and keep getting an error message. It says ODBC Call Failed and it continues on with some jiberish. Could someone look at the attached message and see if this error is fixable and how? Any help would be greatly appreciated.

 

[MattS]

Are you running SQL pass-through queries, or a normal query on a linked table?

Can you post the SQL?

 

[mrssevans]

I believe they are just normal queries on the table. Below is the sql that I am using.

SELECT PUBLIC_RMF008.RM08CS, PUBLIC_RMF008.RM08LT, PUBLIC_RMF008.RM08TY, PUBLIC_RMF008.RM08DT, PUBLIC_RMF008.RM08RF, Count(PUBLIC_RMF008.RM08SQ) AS CountOfRM08SQ, PUBLIC_RMF008.RM08CM, PUBLIC_RMF008.RM08NC, PUBLIC_RMF008.RM08PR, PUBLIC_RMF008.RM08PM, PUBLIC_RMF008.RM08BK, Sum(PUBLIC_RMF008.RM08CO) AS SumOfRM08CO, PUBLIC_RMF008.RM08OC, PUBLIC_RMF008.RM08OR, PUBLIC_RMF008.RM08TT, PUBLIC_RMF008.RM08_NOTES
FROM PUBLIC_RMF008
GROUP BY PUBLIC_RMF008.RM08CS, PUBLIC_RMF008.RM08LT, PUBLIC_RMF008.RM08TY, PUBLIC_RMF008.RM08DT, PUBLIC_RMF008.RM08RF, PUBLIC_RMF008.RM08CM, PUBLIC_RMF008.RM08NC, PUBLIC_RMF008.RM08PR, PUBLIC_RMF008.RM08PM, PUBLIC_RMF008.RM08BK, PUBLIC_RMF008.RM08OC, PUBLIC_RMF008.RM08OR, PUBLIC_RMF008.RM08TT, PUBLIC_RMF008.RM08_NOTES;

union

SELECT PUBLIC_RMF009.RM09CS, PUBLIC_RMF009.RM09LT, PUBLIC_RMF009.RM09TY, PUBLIC_RMF009.RM09DT, PUBLIC_RMF009.RM09RF, Count(PUBLIC_RMF009.RM09SQ) AS CountOfRM09SQ, PUBLIC_RMF009.RM09CM, PUBLIC_RMF009.RM09NC, PUBLIC_RMF009.RM09PR, PUBLIC_RMF009.RM09PM, PUBLIC_RMF009.RM09BK, Sum(PUBLIC_RMF009.RM09CO) AS SumOfRM09CO, PUBLIC_RMF009.RM09OC, PUBLIC_RMF009.RM09OR, PUBLIC_RMF009.RM09TT, PUBLIC_RMF009.RM09_NOTES
FROM PUBLIC_RMF009
GROUP BY PUBLIC_RMF009.RM09CS, PUBLIC_RMF009.RM09LT, PUBLIC_RMF009.RM09TY, PUBLIC_RMF009.RM09DT, PUBLIC_RMF009.RM09RF, PUBLIC_RMF009.RM09CM, PUBLIC_RMF009.RM09NC, PUBLIC_RMF009.RM09PR, PUBLIC_RMF009.RM09PM, PUBLIC_RMF009.RM09BK, PUBLIC_RMF009.RM09OC, PUBLIC_RMF009.RM09OR, PUBLIC_RMF009.RM09TT, PUBLIC_RMF009.RM09_NOTES;

 

[MattS]

I can't see too much wrong with the SQL.

You don't need to use the AS statement (i.e. "AS CountOfRM09SQ") anywhere apart from the first part of the query, and I'd have UNION & SELECT together:

I.e. UNION SELECT ....

Not

Union

Select

Try correcting those, and if that doesn't work, I'd suggest running each part of the Union query on it's own.

HTH,

Matt.

 

[mrssevans]

Unfortunately putting union and select together didn't work. I am able to run them seperately, but not as a union.

 

[MattS]

Hmm. Not sure what to suggest I'm afraid.

I've just tried running a simple union query against a couple of ODBC linked tables in a Sybase database, and had no problems.

Sorry I can't be of any help.

Matt.

 

[mrssevans]

I really appreciate all your suggestions though. Thanks, Sam

 

[pdx_man]

Try creating the two Totals queries separately, then create a Union query joining them. Access is just getting confused as to how to do both things at the same time.

 

[mrssevans]

Great idea...I will try that. Thanks!!!

 

[pdx_man]

Let us know if that works out for you! GL

 

[mrssevans]

Well, I never could get it to work. Finally called the support staff of the database I am pulling the information out of and they are having trouble with that as well. So they are working on it. Thanks for all your help, Sam

 

[pdx_man]

You couldn't get it to run, or it ran but didn't return expected results?

 

[mrssevans]

I got the same error message when I try to do that union as well. There is something wrong with the table definitions in our Unix database from what I have been told, so the union won't work until it is fixed.

 

[Pat Hartman]

FYI, Unix is an operating system not a RDBMS.

Try removing the group by's. They are not necessary. The union will do that anyway.

Error messages returned by ODBC drivers need to be researched at the source database. There is nothing within Access/Jet that can help you.

 

[mrssevans]

Oops..sorry, I meant on a DB on our Unix server. I realized that and the problem is being researched by the DB company. Thanks for the correction.