MS ACCESS ERROR 3306

I NEED ONLY THE ROWS THAT ARE NOT INCLUDED IN BOTH TABLES.

SELECT [AF MAILING LIST].[Const Type], [AF MAILING LIST].First, [AF MAILING LIST].Last, [AF MAILING LIST].Address, [AF MAILING LIST].[Address 2], [AF MAILING LIST].Address3, [AF MAILING LIST].City, [AF MAILING LIST].St, [AF MAILING LIST].Zip
FROM [AF MAILING LIST], [DEC 20 MAILING AF]
Where EXIST (SELECT *
FROM [DEC 20 MAILING AF]);
IVORY HOFFMANAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
The basic idea would be to look for the rows in each table that don't have a match in the other, and then combine the results, like below.  What are the key columns that would be used for the match?  Are the same column names in both tables?

SELECT t1.Key
FROM t1 LEFT JOIN t2 ON t1.Key = t2.Key
WHERE t2.Key IS NULL
UNION
SELECT t2.Key
FROM t2 LEFT JOIN t1 ON t2.Key = t1.Key
WHERE t1.Key IS NULL
;

Open in new window

Or using EXISTS:

SELECT t1.Key
FROM t1 
WHERE NOT EXISTS (SELECT 'Y' FROM t2 WHERE t2.Key = t1.Key)
UNION
SELECT t2.Key
FROM t2 
WHERE NOT EXISTS (SELECT 'Y' FROM t1 WHERE t1.Key = t2.Key)
;

Open in new window


»bp
0
ste5anSenior DeveloperCommented:
First of all: In almost every online community using ALL CAPS is considered yelling. That's not nice.

In normal SQL this would be a full outer join, but this join type is not available in Access SQL. Thus you need two non-equi joins.

e.g.

SELECT A.[Const Type] ,
       A.First ,
       A.Last ,
       A.Address ,
       A.[Address 2] ,
       A.Address3 ,
       A.City ,
       A.St ,
       A.Zip
FROM   [AF MAILING LIST] A
       LEFT JOIN [DEC 20 MAILING AF] B ON Nz( A.[Const Type], "") = Nz( B.[Const Type], "")
                                          AND Nz( A.FIRST, "") = Nz( B.FIRST, "")
                                          AND Nz( A.LAST, "") = Nz( B.LAST, "")
                                          AND Nz( A.ADDRESS, "") = Nz( B.ADDRESS, "")
                                          AND Nz( A.[Address 2], "") = Nz( B.[Address 2], "")
                                          AND Nz( A.Address3, "") = Nz( B.Address3, "")
                                          AND Nz( A.City, "") = Nz( B.City, "")
                                          AND Nz( A.St, "") = Nz( B.St, "")
                                          AND Nz( A.Zip, "") = Nz( B.Zip, "")
WHERE  B.CheckColumn IS NOT NULL
UNION
SELECT A.[Const Type] ,
       A.First ,
       A.Last ,
       A.Address ,
       A.[Address 2] ,
       A.Address3 ,
       A.City ,
       A.St ,
       A.Zip
FROM   [DEC 20 MAILING AF] A
       LEFT JOIN [AF MAILING LIST] B ON Nz( A.[Const Type], "") = Nz( B.[Const Type], "")
                                        AND Nz( A.FIRST, "") = Nz( B.FIRST, "")
                                        AND Nz( A.LAST, "") = Nz( B.LAST, "")
                                        AND Nz( A.ADDRESS, "") = Nz( B.ADDRESS, "")
                                        AND Nz( A.[Address 2], "") = Nz( B.[Address 2], "")
                                        AND Nz( A.Address3, "") = Nz( B.Address3, "")
                                        AND Nz( A.City, "") = Nz( B.City, "")
                                        AND Nz( A.St, "") = Nz( B.St, "")
                                        AND Nz( A.Zip, "") = Nz( B.Zip, "")
WHERE  B.CheckColumn IS NOT NULL;

Open in new window


The CheckColumn must be a column which cannot be NULL or empty for valid rows.
0
Bill PrewCommented:
So, if those are the same column names, and you wanted to match on all of them, you could do:

SELECT t1.[Const Type], 
       t1.First, 
       t1.Last, 
       t1.Address, 
       t1.[Address 2], 
       t1.Address3, 
       t1.City, 
       t1.St, 
       t1.Zip 
FROM   [AF MAILING LIST] t1 
WHERE  NOT EXISTS (SELECT 'Y' 
                   FROM   [DEC 20 MAILING AF] t2 
                   WHERE  t1.[Const Type] = t2.[Const Type] 
                          AND t1.First = t2.First 
                          AND t1.Last = t2.Last 
                          AND t1.Address = t2.Address 
                          AND t1.[Address 2] = t2.[Address 2] 
                          AND t1.Address3 = t2.Address3 
                          AND t1.City = t2.City 
                          AND t1.St = t2.St 
                          AND t1.Zip = t2.Zip) 
UNION 
SELECT t1.[Const Type], 
       t1.First, 
       t1.Last, 
       t1.Address, 
       t1.[Address 2], 
       t1.Address3, 
       t1.City, 
       t1.St, 
       t1.Zip 
FROM   [DEC 20 MAILING AF] t1 
WHERE  NOT EXISTS (SELECT 'Y' 
                   FROM   [AF MAILING LIST] t2 
                   WHERE  t1.[Const Type] = t2.[Const Type] 
                          AND t1.First = t2.First 
                          AND t1.Last = t2.Last 
                          AND t1.Address = t2.Address 
                          AND t1.[Address 2] = t2.[Address 2] 
                          AND t1.Address3 = t2.Address3 
                          AND t1.City = t2.City 
                          AND t1.St = t2.St 
                          AND t1.Zip = t2.Zip); 

Open in new window


»bp
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

IVORY HOFFMANAuthor Commented:
so sorry for yelling ... it was unintentional.  Thanks for the help.  I am locked into MSAccess and it has been a little challenge (syntax).  I would have just as easily deleted the rows that did not exist in the other table.  

I'm working on it
0
IVORY HOFFMANAuthor Commented:
But to answer your question, I am basically trying to get a list of just the rows that do not match each other in both tables using MSAccess
0
Bill PrewCommented:
Okay, you have a couple of viable approaches there, try pasting them into the SQL editor of a new query and see what you get.


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.