Is There An Operator Similar To Intersect In Access SQL? 2

[mmignot]

Hello,

In Access 2000, I am performing a UNION on two tables. These tables have the same columns but with different data. Here is an example:

Table-1
KEY COL1 COL2 DataA DataB
Key1 1-A 2-A 2 2
Key2 2-A 2-B 2 2

Table-2
KEY COL1 COL2 DataA DataB
Key1 1-A 2-A 2 2
Key2 2-A 2-B 2 2
Key3 3-A 3-B 5 5

I am trying to develop a query that would give me the following result set.....

Query Result
KEY COL1 COL2 DataA DataB
Key1 1-A 2-A 4 4
Key2 2-A 2-B 4 4
Key3 3-A 3-B 5 5

.... but all of the rows in both tables are being returned.
Does anyone have a suggestion on how to accomplish this in Access?

Thanks in advance..
MM

 

[Maquis]

Do you only want to return the rows in both tables or do what all the rows that are in either table just adding together the ones that match.

If you want all the rows then Bring your union query into your query design window and group by key, COL1 and COL2 and sum DataA and DataB.

If you only want those rows in both tables then bring the tables into the query design and join them on the keys. Then add [Table1].DataA + [Table2].DataA as a field in the query design. (Ditto DataB)


Maq B-)
<insert witty signature here>

 

[tlbroadbent]

This solution is similar to that proposed by Maq.

SELECT
u.Key, u.Col1, u.Col2,
Sum(u.DataA) AS SumA, Sum(u.DataB) AS SumB
FROM [SELECT *
FROM [Table-1]
Union All
SELECT *
FROM [Table-2]]. AS u
GROUP BY u.Key, u.Col1, u.Col2;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mmignot]

Maq/Terry...

Your suggestions worked like a charm. Many thanks for your help!!

MM