[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