[AccessD] Can't get the SQL for this

Thanks AD, I'll try it.

-- Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
> Sent: 18 April 2005 19:53
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Can't get the SQL for this
> 
> 
> Andy,
> 
>     Totals query named Q_Andy2, given below, should get you 
> the desired result. It uses union query named Q_Andy1 as its source.
> 
> Best wishes,
> A.D.Tejpal
> --------------
> 
> Q_Andy2 (Final Query - Totals Query) 
> ==================================
> SELECT [Code1] & [Code2] AS Code, Sum(Q_Andy1.Qty) AS 
> SumOfQty FROM Q_Andy1 GROUP BY [Code1] & [Code2]; 
> ==================================
> 
> Q_Andy1 (Preliminary Query - Union type) 
> ==================================
> Select Code1, Code2, Qty From T_Andy1 
> Union ALL 
> Select Code1, Code2, Qty From T_Andy2 
> UNION ALL 
> Select Code1, Code2, Qty From T_Andy3; 
> ==================================
> 
>   ----- Original Message ----- 
>   From: Andy Lacey 
>   To: Dba 
>   Sent: Monday, April 18, 2005 21:38
>   Subject: [AccessD] Can't get the SQL for this
> 
> 
>   Afternoon all, how's things?
> 
>   Can someone help dense old me? I can't figure out how to do this.
> 
>   I have 3 tables, structure of each is Code1, Code2, Qty 
> (I'm simplifying).
>   Example data:
> 
>   tbl1
>   ----
>   A    B  36
>   A    B  45
>   B    B  21
>   B    C  22
> 
>   tbl2
>   -----
>   A    A  9
>   B    B  12
> 
>   tbl3
>   ----
>   A    B  7
>   B    D  3
> 
>   What I want as output is the total qty for each unique pair 
> of Code1 and Code2, ie
> 
>   A    A  9
>   A    B  88
>   B    B  33
>   B    C  22
>   B    D  3
> 
>   I started by writing a query for each table to sum qty by 
> Code1 and Code2 then began to do a UNION query and stopped 
> there cos I can't see how I sum or Group across the 3 
> original queries in my UNION query. So I must have started 
> wrong but I've got a senior moment or something cos I can't see it.
> 
>   Can someone please help me out?
> 
>   --
>   Andy Lacey
>   http://www.minstersystems.co.uk
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>