Full outer join in Access

[avenuw]

Hello,

I am using the following sql statment below in access to perform a full outer join. And although it runs and produces results, there is something really funny about the results. Some records values almost quadupled in amount!

here is the code:


SELECT A.x,A.z, Sum(a.y), Sum(A.y1),Sum(B.v), Sum(B.v1)
FROM A_tbl AS A LEFT JOIN B_tbl AS B
ON (A.P=B.P) AND (A.[N]=B.[N])
GROUP BY A.x, A.y
ORDER BY A.x, A.y

Union All

SELECT B.x,B.z, Sum(a.y), Sum(A.y1),Sum(B.v), Sum(B.v1)
FROM A_tbl AS A Right JOIN B_tbl AS B
ON (A.P=B.P) AND (A.[N]=B.[N])
GROUP BY B.x, B.y


Union All

SELECT A.x,A.z, Sum(a.y), Sum(A.y1),Sum(B.v), Sum(B.v1)
FROM A_tbl AS A INNER JOIN B_tbl AS B
ON (A.P=B.P) AND (A.[N]=B.[N])
GROUP BY A.x, A.y


Does anyone know if I need to perform the inner join? Since both my left and right joins are also bring those records that match both tables.
And Does anyone know why my numbers are way off? For example,when the query should return 10 for SUM(A.y1) it returns 34, etc.

Thank You,
AV

 

[PHV]

Remove the All keyword from the Union clause (2 times)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

and remove the 3rd SELECT



r937.com | rudy.ca

 

[avenuw]

Thanks PHV and r937,

I did remove the All in my union query but unforchantely I still get the same funny numbers. And I removed the inner select since it was not needed.


I ended up splitting each of my queries and then quering those queries and it worked. But I would still like to know what happened, I should be able to perform JOINS and Aggregate sums in my sql statment like above producing the correct values!

Thanks,
AV