?
Solved

Help with MS Access SQL.... UNION syntax question

Posted on 2003-03-03
29
Medium Priority
?
357 Views
Last Modified: 2012-06-27
I need some help. I'm having a small problem with a MS ACCESS query. I think I'm simply having a syntax problem.

SELECT sum(subquery1.balance), subquery1.account FROM
(
SELECT balance, account from table1
UNION ALL
SELECT balance, account from table2
) as subquery1
group by subquery1.balance

If anyone can see something wrong I'd appreciate any feedback.

Thanks,
Ryan

0
Comment
Question by:rdorosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 5
  • +2
29 Comments
 
LVL 1

Expert Comment

by:tpatten
ID: 8057848
You need to group by subquery1.account
0
 
LVL 1

Expert Comment

by:obyapka
ID: 8057936
See following example of a SELECT GROUP BY query in the SQL publications (pubs) database

USE pubs
SELECT type, pub_id, 'avg' = AVG(price), 'sum' = sum(ytd_sales)
FROM titles
GROUP BY type, pub_id

You can use one or more aggregate-free expressions. These are usually references to the grouping columns. i.e. you cannot group on Avg() or Sum() etc
0
 
LVL 2

Expert Comment

by:kraig
ID: 8057937
I don't think you can do that--your going to need to save the union query and then include it in a second query.

qunBalanceAcct would be:
SELECT balance, account from table1
UNION ALL
SELECT balance, account from table2

and qryResult would then be:
SELECT Sum(qunBalanceAcct.balance) AS TotBalance, qunBalanceAcct.account
FROM qunBalanceAcct
GROUP BY qunBalanceAcct.account;

If there is a better way to do this I'd be interested in seeing it.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Expert Comment

by:obyapka
ID: 8057939
thus as tpatten states, you need to group on subquery1.account. Above is the reason why.
0
 

Author Comment

by:rdorosh
ID: 8057959
Sorry, it was my mistake to say group by balance... it should have been account.

The error MS Access gives is a syntax error in the first FROM clause.

Thanks,
Ryan

And I'll look into the grouping buy SUM. I've thought this has worked for me in Oracle previously.

0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058018
Do I get the points? :)

Just curious. Why are you looking to group by the sum()? Do you essentially want to sum up each account, and then group by the sums of those accounts?
0
 

Author Comment

by:rdorosh
ID: 8058052
Sorry, it was my mistake to say group by balance... it should have been account.

The error MS Access gives is a syntax error in the first FROM clause.

Thanks,
Ryan

And I'll look into the grouping buy SUM. I've thought this has worked for me in Oracle previously.

0
 
LVL 1

Expert Comment

by:obyapka
ID: 8058067
would have thought it make more sense to ORDER BY sum and GROUP BY account, but this isn't my data and no not what the outcome should be.
0
 

Author Comment

by:rdorosh
ID: 8058081
Tpattern,

I'm trying to group by account, which would then give the sum for the accounts.

Is it just impossible to select from a resultset that involves a union query?

Ryan
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058103
Do I get the points? :)

Just curious. Why are you looking to group by the sum()? Do you essentially want to sum up each account, and then group by the sums of those accounts?
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058106
Do I get the points? :)

Just curious. Why are you looking to group by the sum()? Do you essentially want to sum up each account, and then group by the sums of those accounts?
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058125
Whoops. FOr some reason my post keeps postin'!
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058178
It's not impossible at all. Your original query will group by the accounts and give you a sum for each account. Isn't that what you are looking for? If you're lookingt o group by the accounts and get a sum of ALL accounts, then your query is not doing this. Please advise.
0
 

Author Comment

by:rdorosh
ID: 8058221
Here would be an example of the output i'm looking for.....

Account         Balance
-------         --------
0001            $50.00
0002            $100.00

One thing, these are coming from one table, not two like i did in my example, but the identification method for the account number is done in two ways... which is why i have the union to break up the two queries.

Again, the only problem I'm having is a syntax error from Access.. or so it says. I took the exact query above, and tested it with two sample tables and again.. same syntax error in the from clause.

Thanks, Ryan
0
 
LVL 2

Expert Comment

by:kraig
ID: 8058286
I'm pretty sure you're going to need two queries.
Is there a problem with doing it that way?
0
 
LVL 2

Expert Comment

by:kraig
ID: 8058296
You could possibly eliminate the union aspect of the query and use an immediate if (iif) to calculate the account number in a regular group by/sum query.
0
 

Author Comment

by:rdorosh
ID: 8058310
Just a little more information. When I run the query as

Statement1
UNION
Statement2

it works fine, but I need to then sum up the similar accounts, such as

select sum(balance), account from
(Statement1
UNION
Statement2 )
group by account;

if this isn't possible, is there a good work around as creating a macro that runs my first union statement, creates a temp table, then runs a sum statement on the temp table?
0
 
LVL 2

Expert Comment

by:kraig
ID: 8058335
something like this:

SELECT IIf([tblAccounts].[blnAcctMethod],[tblAccounts].[strAccountA],[tblAccounts].[strAccountB]) AS strAccount, Sum(tblAccounts.curBalance) AS SumOfcurBalance
FROM tblAccounts
GROUP BY IIf([tblAccounts].[blnAcctMethod],[tblAccounts].[strAccountA],[tblAccounts].[strAccountB]);
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058344
SELECT sum(subquery1.balance), subquery1.account FROM
(
SELECT balance, account from table1
UNION ALL
SELECT balance, account from table2
) as subquery1
group by subquery1.account

This statement should work fine. What the subquery does is essentially represent a virtual table or a view.
0
 

Author Comment

by:rdorosh
ID: 8058421
tpattern,

I agree with you, but it does not work. Access keeps complaining with a syntax error. This works in Oracle.

------------------
kraig,

I will try your idea soon and get back to you.


Thanks,
Ryan
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058513
rdorosh: I tested the following statement in Access 2000 and it worked like a charm. What is the specific syntax error that you are receiving? What version of Access are you using? Are you executing this query directly in Access or through ASP/VB/.NET?

SELECT sum(subquery1.balance) as 'total_balance', subquery1.account FROM
(
   SELECT balance, account from table1
   UNION ALL
   SELECT balance, account from table2
) as subquery1
group by subquery1.account
0
 

Author Comment

by:rdorosh
ID: 8058682
tpattern,

I'm using Access 97. Let me test your exact syntax here and see what happens. I hope this works :-) b/c it's frustrating me (Access that is).

R.
0
 

Author Comment

by:rdorosh
ID: 8058824
tpattern,

Sorry, your query didn't work for me. Must be Access 97 maybe. If 2000 and Oracle work, that's the only thing I can conclude.

Ryan
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8058853
I would do as suggested above then. Create a query with the union, then select from the query.
0
 
LVL 7

Accepted Solution

by:
Bruce Cadiz earned 300 total points
ID: 8059514
rdorosh,

tpatten is correct. Access does not handle subqueries like Oracle and requires you to create a UNION query first,  then select off of that query like so:

Create union query and save as subquery1(or any name you like):

                  SELECT balance, account from table1
                  UNION ALL
                  SELECT balance, account from table2

Then create a second query that selects the first:
SELECT sum(subquery1.balance), subquery1.account FROM subquery1
                  group by subquery1.account

This should work in Access 97.

0
 

Author Comment

by:rdorosh
ID: 8064563
tpattern,

Sorry, your query didn't work for me. Must be Access 97 maybe. If 2000 and Oracle work, that's the only thing I can conclude.

Ryan
0
 

Author Comment

by:rdorosh
ID: 8064597
Excellent. Saving the first, and selecting from it using a second query was the solution for Access 97.

Tpattern, I wish there was a way I could give you points as well for all the help. I really appreciate it.
0
 
LVL 2

Expert Comment

by:kraig
ID: 8064650
You're kidding, right?

My post dated 03/03/2003 07:48AM PST:

"I don't think you can do that--your going to need to save the union query and then include it in a second query.

qunBalanceAcct would be:
SELECT balance, account from table1
UNION ALL
SELECT balance, account from table2

and qryResult would then be:
SELECT Sum(qunBalanceAcct.balance) AS TotBalance, qunBalanceAcct.account
FROM qunBalanceAcct
GROUP BY qunBalanceAcct.account;

If there is a better way to do this I'd be interested in seeing it. "

Sweet.
0
 
LVL 1

Expert Comment

by:tpatten
ID: 8064707
It's seven points. Not really worth getting pissy about it.

rdorosh -- no problem. I learned something in this thread too. I did not realize that Access 97 and 2000 differed so much in their support of standard SQL. Let's face it, Access is an evil that many of us simply have to deal with at times :)
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question