APPEND Query Duplicates Records rather than Adding New

[Angela Corvaia]

Help!

I have two tables, the original: Project and the newest
version: Project1. I want to Append about 40 new records
from the Project1 table (which contains 440 records) to
the Project table (which has 1425 records). Both tables
have QuoteNo as a primary, unique key.


The following Append Query is adding 400 records from
Project 1 - Just the Opposite of the new 40 records I
want.

INSERT INTO Project ( QuoteNo, QuoteRev, QuoteDate,
CustomerNo, Customer, CustomerRef, CustomerType, Status )
SELECT DISTINCTROW Project.QuoteNo, Project.QuoteRev,
Project.QuoteDate, Project.CustomerNo, Project.Customer,
Project.CustomerRef, Project.CustomerType, Project.Status
FROM Project RIGHT JOIN Project1 ON Project.QuoteNo =
Project1.QuoteNo;


HELP!!!!

 

[John Viescas]

You left out the WHERE clause to exclude the rows that are already in
Project. Also, you're selecting from the wrong table.

INSERT INTO Project ( QuoteNo, QuoteRev, QuoteDate,
CustomerNo, Customer, CustomerRef, CustomerType, Status )
SELECT Project1.QuoteNo, Project1.QuoteRev,
Project1.QuoteDate, Project1.CustomerNo, Project1.Customer,
Project1.CustomerRef, Project1.CustomerType, Project1.Status
FROM Project RIGHT JOIN Project1 ON Project.QuoteNo =
Project1.QuoteNo
WHERE Project.QuoteNo IS NULL;

After you make this change, be sure to look at this query in Datasheet view
to verify that it's picking the correct rows. If it is, then you can
execute it to get the result you want.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Angela Corvaia]

It totally worked! However, I don't understand the 'Is
Null' statement??!! I was experimenting with
QuoteNo<>QuoteNo to eliminate duplicated QuoteNumbers, but
I NEVER thought that a Null value entered into it. Can
you explain?

Thanks!!!!!

Sorry about the freaked out SQL statement - I was goofing
with it and copied the wrong one.

 

[John Viescas]

Well, you asked for a RIGHT JOIN - all the rows from Project1 and any
matching rows in Project. When there is no matching row in Project, the
query engine returns a Null value - so this works to find out what rows are
in Project1 that are not in Project. You'll get similar syntax if you use
the unmatched query wizard.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411