[AccessD] Can't get the SQL for this

    You are most welcome Andy!

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Andy Lacey 
  To: Dba 
  Sent: Tuesday, April 19, 2005 14:06
  Subject: [AccessD] Can't get the SQL for this


  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