Union Query Group By 2

[ZmrAbdulla]

I have a Union Query need to pick fields from a table and a query.
tblBalanceQty is having single entry for each item
tblPurchaseCurrent is having multiple entry.
so far I have this sql with me. It groups by table & Query separate.

SELECT tblBalanceQty.ItemName, tblBalanceQty.BalQty
FROM tblBalanceQty
UNION SELECT  tblPurchaseCurrent.ItemName, Sum(tblPurchaseCurrent.Units) AS BalQty
FROM tblPurchaseCurrent
GROUP BY ItemName;

Is that possible to change the SQL to group it by item and sum by quantity?
Thanks for any help
I will come back tomorrow...

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[LittleSmudge]

How about

SELECT ItemName, Sum(Qty) As TotalQty FROM 
(
 SELECT ItemName, BalQty As Qty FROM tblBalanceQty
 UNION 
 SELECT  ItemName, Units FROM tblPurchaseCurrent
)
GROUP BY ItemName

'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

You may have to replace this:
UNION
By this:
UNION ALL
And this:
)
By something like this:
) AS U

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZmrAbdulla]

LittleSmudge,
That was great.. A Select query to replace a Union Query??!!(Access says it is a select query).. Works great..

PHV,
I couldn't get your solution working.. may be I am doing something wrong. Can you please explain a little more?

Thanks to both..

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[r937]

what PHV was trying to suggest was that if the two SELECTS that are being unioned together just happen to produce the same results, one of them will be eliminated unless you use UNION ALL

r937.com | rudy.ca

 

[LittleSmudge]

Well caught PHV and thanks for the interpretation r937


So putting it all together

SELECT ItemName, Sum(Qty) As TotalQty FROM 
(
 SELECT ItemName, BalQty As Qty FROM tblBalanceQty
 UNION ALL
 SELECT  ItemName, Units FROM tblPurchaseCurrent
) As U
GROUP BY ItemName

The UNION ALL is important. This is because UNION on its own effectively does a DISTINCT on the dynaset it produces.

The 'As U' part is, I think, unnecessary in JET SQL but it does make it more consistent in format with other types of SQL.


'ope-that'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ZmrAbdulla]

Star to PHV being the Master of Masters...

LittleSmudge,
Your first sql was automatically changed like below

SELECT ItemName, Sum(Qty) AS TotalQty
FROM [SELECT ItemName, BalQty As Qty FROM tblBalanceQty
 UNION 
 SELECT  ItemName, Units FROM tblPurchaseCurrent
]. AS [%$##@_Alias]
GROUP BY ItemName;

I don't know what Access was saying by [%$##@_Alias]"
and the second sql stayed as it is.

In the first one table name was "[%$##@_Alias]" and in the second table name become "U".

So PHV is right to have "AS U" in the SQL to stop Access from screaming at me.

I am getting the result without having "ALL" clause. Need to add more items and check

Thanks to all

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[LittleSmudge]

It only adds the [ and ]. AS [%$##@_Alias] when you flip from SQL view into QBE grid view.


That's a limitation of the QBE grid because it needs a name to give to the subQuery.

If you never go to the QBE grid it will work fine without it.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ZmrAbdulla]

It was a new lesson for me..
thanks

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal