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?
 
Bill PrewConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.