[AccessD] Can't get the SQL for this

Andy Lacey andy at minstersystems.co.uk
Tue Apr 19 03:36:50 CDT 2005


Many thanks AD, it worked a treat. I was going at it the wrong way with
creating three separate queries then trying to UNION and GROUP them at the
same time. Thanks for taking the trouble to help me out.

--
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
> --



________________________________________________
Message sent using UebiMiau 2.7.2




More information about the AccessD mailing list