using identical variables from two different tables in a query

[sarahphonics]

I have two tables in my database, CH2_1 and CH2_2. They both have identical
variables - Quarter, Purchaser, Provider, O_D, WAIT02, WAIT35.
However, they have different values in the WAIT02,WAIT35 variables. Both
tables have some cases with the same quarter,purchaser etc, bt table CH2_1
has some values which CH2_2 does not have.

What I need to do, is produce a query which sums the WAIT02 in CH2_1 and
CH2_2, into a variable called Wait 0-2, and sums WAIT35 in CH2_1 and CH2_2
into a variable Wait 3-5, but at the same time, aggregates by quarter,O_D and
purchaser.

I need all the values in both tables, even if the other table does not have
a case for the combination of quarter, O_D and purchaser.

IF you need any further clarification, ill do my best to explain.

A quick response would be gratefully appreciated

 

[KARL DEWEY]

Try these two queries --
sarahphonics_1 --
SELECT CH2_1.Quarter, CH2_1.O_D, CH2_1.Purchaser
FROM CH2_1
UNION SELECT CH2_2.Quarter, CH2_2.O_D, CH2_2.Purchaser
FROM CH2_2;

SELECT sarahphonics_1.Quarter, sarahphonics_1.O_D, sarahphonics_1.Purchaser,
Sum(nz([CH2_1].[WAIT02],0)+nz([CH2_2].[WAIT02],0)) AS [Wait 0-2],
Sum(nz([CH2_1].[WAIT35],0)+nz([CH2_2].[WAIT35],0)) AS [Wait 3-5]
FROM (sarahphonics_1 LEFT JOIN CH2_1 ON (sarahphonics_1.O_D = CH2_1.O_D) AND
(sarahphonics_1.Purchaser = CH2_1.Purchaser) AND (sarahphonics_1.Quarter =
CH2_1.Quarter)) LEFT JOIN CH2_2 ON (sarahphonics_1.O_D = CH2_2.O_D) AND
(sarahphonics_1.Purchaser = CH2_2.Purchaser) AND (sarahphonics_1.Quarter =
CH2_2.Quarter)
GROUP BY sarahphonics_1.Quarter, sarahphonics_1.O_D, sarahphonics_1.Purchaser;

 

[sarahphonics]

Karl, thank you very much for this. I had attempted myself the day before and
once I tried yours, I got the same answer. However, this does not match the
answer if I manually add together the two tables for the quarter, O_D,
purchaser I want. Im not sure if there is some hidden information with these
tables, I am working on a database that has been in use for some time and I
do not know the background of it.

Thanks for your help anyway, the query seems to be correct, I feel its a
problem with the actual tables

Try these two queries --
sarahphonics_1 --
SELECT CH2_1.Quarter, CH2_1.O_D, CH2_1.Purchaser
FROM CH2_1
UNION SELECT CH2_2.Quarter, CH2_2.O_D, CH2_2.Purchaser
FROM CH2_2;

SELECT sarahphonics_1.Quarter, sarahphonics_1.O_D, sarahphonics_1.Purchaser,
Sum(nz([CH2_1].[WAIT02],0)+nz([CH2_2].[WAIT02],0)) AS [Wait 0-2],
Sum(nz([CH2_1].[WAIT35],0)+nz([CH2_2].[WAIT35],0)) AS [Wait 3-5]
FROM (sarahphonics_1 LEFT JOIN CH2_1 ON (sarahphonics_1.O_D = CH2_1.O_D) AND
(sarahphonics_1.Purchaser = CH2_1.Purchaser) AND (sarahphonics_1.Quarter =
CH2_1.Quarter)) LEFT JOIN CH2_2 ON (sarahphonics_1.O_D = CH2_2.O_D) AND
(sarahphonics_1.Purchaser = CH2_2.Purchaser) AND (sarahphonics_1.Quarter =
CH2_2.Quarter)
GROUP BY sarahphonics_1.Quarter, sarahphonics_1.O_D, sarahphonics_1.Purchaser;

--
KARL DEWEY
Build a little - Test a little

I have two tables in my database, CH2_1 and CH2_2. They both have identical
variables - Quarter, Purchaser, Provider, O_D, WAIT02, WAIT35.
However, they have different values in the WAIT02,WAIT35 variables. Both
tables have some cases with the same quarter,purchaser etc, bt table CH2_1
has some values which CH2_2 does not have.

What I need to do, is produce a query which sums the WAIT02 in CH2_1 and
CH2_2, into a variable called Wait 0-2, and sums WAIT35 in CH2_1 and CH2_2
into a variable Wait 3-5, but at the same time, aggregates by quarter,O_D and
purchaser.

I need all the values in both tables, even if the other table does not have
a case for the combination of quarter, O_D and purchaser.

IF you need any further clarification, ill do my best to explain.

A quick response would be gratefully appreciated