Re: Union query and exclusion of data
- From: John Spencer <spencer@xxxxxxxxxx>
- Date: Sun, 30 Mar 2008 09:38:08 -0400
Get records that are in both A and B
SELECT A.X, "A" as Source
FROM A INNER JOIN B
ON A.X = B.X
Get Records in A that are not in B
SELECT A.X, "A" as Source
FROM A LEFT JOIN B
ON A.X = B.X
WHERE B.X is Null
Get Records in B that are not in A
SELECT B.X, "B" as Source
FROM A RIGHT JOIN B
ON A.X = B.X
WHERE A.X is Null
You should be able to use a union query to get
-- Records in A or in B but not in both (Last two queries above)
-- Records in A or B (all three queries)
-- Records in A (First two queries - although just a simple select of records in A will do this more efficiently)
-- Records in A and B or in B (First and third - again a simple select of records in B will do this more efficiently)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Syed Zeeshan Haider wrote:
Hello Everybody,.
I am usin Access 2003 Pro and trying to create a rather complex union query but don't seem to implement conditions.
Let's say I have two tables called A and B. A has column called x and B also has a column called x. I am trying to join these two columns into one column with following conditions:
If e is a member of both A and B then the query should include e from table A only (because the second column calculates a value which depends on the choice of table for picking e). I have tried all INNER JOIN, LEFT JOIN and RIGHT JOIN but none of them have worked so far.
Here is an example of what I did:
select [A].[x]
FROM [A] INNER JOIN [B] ON [A].[x] = [B].[x]
where (([A].[x]) Is Not Null) and (([A].[x])<>([B].[x]))
UNION select [B].[x]
from [B];
Any ideas what am I doing wrong here?
Any help will be highly appreciated.
Thank you,
- References:
- Union query and exclusion of data
- From: Syed Zeeshan Haider
- Union query and exclusion of data
- Prev by Date: Re: Update query
- Next by Date: Re: Union query and exclusion of data
- Previous by thread: Re: Union query and exclusion of data
- Next by thread: Query expression builder returns text instead of numeric
- Index(es):
Relevant Pages
|
