• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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

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
rdorosh
Asked:
rdorosh
  • 10
  • 10
  • 5
  • +2
1 Solution
 
tpattenCommented:
You need to group by subquery1.account
0
 
obyapkaCommented:
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
 
kraigCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
obyapkaCommented:
thus as tpatten states, you need to group on subquery1.account. Above is the reason why.
0
 
rdoroshAuthor Commented:
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
 
tpattenCommented:
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
 
rdoroshAuthor Commented:
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
 
obyapkaCommented:
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
 
rdoroshAuthor Commented:
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
 
tpattenCommented:
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
 
tpattenCommented:
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
 
tpattenCommented:
Whoops. FOr some reason my post keeps postin'!
0
 
tpattenCommented:
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
 
rdoroshAuthor Commented:
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
 
kraigCommented:
I'm pretty sure you're going to need two queries.
Is there a problem with doing it that way?
0
 
kraigCommented:
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
 
rdoroshAuthor Commented:
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
 
kraigCommented:
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
 
tpattenCommented:
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
 
rdoroshAuthor Commented:
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
 
tpattenCommented:
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
 
rdoroshAuthor Commented:
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
 
rdoroshAuthor Commented:
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
 
tpattenCommented:
I would do as suggested above then. Create a query with the union, then select from the query.
0
 
Bruce CadizQuality SpecialistCommented:
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
 
rdoroshAuthor Commented:
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
 
rdoroshAuthor Commented:
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
 
kraigCommented:
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
 
tpattenCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 10
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now