Access SQL UNION Query Criteria

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Thanks for the help!

I am using MSAccess 2002. I have a database with 3 linked
tables. These tables contain similar information. One
table is from an Excel spreadsheet. I wish to merge the
data in these three tables into a fourth table, but filter
the data as I merge. Each table contains a list of names
and email addresses separated into three
colums; "FirstName", "LastName", and "Email". There is an
additional criteria field labeled "Remove". I wish to
place the name and email data into a table, while removing
duplicate email addresses, and furthermore removing those
entries where the "Remove" field is a value other than "0"
(zero). A UNION query is what im using now, but I dont
know enough about SQL to filter the data. Just to add
something even more fun to the mix, the "Remove" field may
have different values in different tables, and in fact may
not exist in all of the tables! Please help!

Ben
 
i'm not too great with SQL myself, usually wimp out and
use the QBE grid. if nobody gives you a more efficient
solution, you could try this:
1. write a separate query for each linked table, filtering
out "removed" records where appropriate.
2. base your union query on those 3 queries; you won't
need to select the "remove" field here.
3. write another query, based on your union query, to get
rid of duplicate email addresses - maybe SELECT
DISTINCTROW, or maybe by creating a totals query with
appropriate grouping.
if it were me, after i did all that - and got it to work -
i'd try to patch together the SQL code (that Access wrote
for me in all those separate queries) into one union query
that covers everything....and see if i could get that to
work. (i do a lot of work in stages like that, just trying
to figure things out!) :)
hth
 
Build the union query and then use that as the source for your new table

The SQL would look something like:

SELECT FirstName, LastName, EMail
FROM TableA
WHERE Remove <> 0
UNION
SELECT FirstName, LastName, EMail
FROM TableB
Where Remove = False
UNION
SELECT FirstName, Name_Last, EmailAddress
FROM TableExcel

UNION will strip out all the exact duplicates. Save the query as qUnion_EMAIL
and then use that as the source of an Append query or a make table query.
 
Here's an approach to take if you had just two tables. Select all the
rows where Remove=0 from Table1 then UNION the records from Table2
that do not appear in Table1, again where Remove=0. To select the
records from Table2 that do not appear in Table1, you left join Table2
to Table1 on the key column(s) (Email?) and look for the rows with a
null value in the key column of the table on the right e.g.

SELECT
T1.FirstName, T1.LastName, T1.Email
FROM
Table1 AS T1
WHERE
T1.Remove=0
UNION
SELECT
T2.FirstName, T2.LastName, T2.Email
FROM
Table2 AS T2 LEFT JOIN Table1 AS T3
ON T2.Email = T3.Email
WHERE
T3.Email IS NULL
AND T2.Remove=0

Does this look like what you want to do (except with more than just
two tables)?

Because you have three more tables you must join to the previous
tables to eliminate duplicate records. I don't want to give the whole
query with nested joins, even for just a third table, because it would
look much more complicated than it is (and I may have got the wrong
end of the stick anyhow!)

But you may be able to follow the general approach and incorporate
more tables. Or I may have lost you already; if so, post back when
you've tried the above approach.

Or maybe someone can do the same without a UNION, I'm a little
rusty...
 
John is right, the UNION will take care of the duplicates (I was
thinking of UNION ALL - told you I was a bit rusty!) Mine works but is
more complicated than it needs be, of course. BTW I think you *do*
need WHERE Remove=0.

Still, take a look at the second half of my SELECT in case you need to
do the 'filtering' bit without the UNION (I'm sure I got that bit
right!)
 
Back
Top