Help with UPDATE and IF/THEN

[SinCity]

Here is my code...

INSERT INTO Leads ( BLastName, BFirstName, Address, City, Zip, WeddingDate,
HomePhone, email )
SELECT [73106].BLastName2 AS BLastName, [73106].BFirstName2 AS BFirstName,
[73106].Address AS Address, [73106].City AS City, [73106].Zip AS Zip,
[73106].WeddingDate AS WeddingDate, [73106].HomePhone AS HomePhone,
[73106].email AS email
FROM 73106;

I have it set up as an Append Query. When I run it, it appends my Leads
database including duplicates.
How can I change it to be "run this code only if you dont find duplicates in
the email field"?

Do I need to implement some type of IF/THEN code?

Thanks!!

 

[Douglas J. Steele]

How about code that inserts the record if it doesn't exist, or updates the
record if it does?

If that sounds useful, check my November, 2003 "Access Answers" column in
Pinnacle Publication's "Smart Access".

You can download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[SinCity]

How about code that inserts the record if it doesn't exist, or updates the

record if it does?

That sounds like it would work, but how do I accomplish this to occur when I
run my SQL code manually in Access?

 

[John Vinson]

I have it set up as an Append Query. When I run it, it appends my Leads
database including duplicates.
How can I change it to be "run this code only if you dont find duplicates in
the email field"?

Use an Unmatched query to select only records which don't exist in the
target table, using an "unmatched" query:

INSERT INTO Leads ( BLastName, BFirstName, Address, City, Zip,
WeddingDate, HomePhone, email )
SELECT [73106].BLastName2, [73106].BFirstName2,
[73106].Address, [73106].City, [73106].Zip,
[73106].WeddingDate, [73106].HomePhone,
[73106].email
FROM 73106 LEFT JOIN Leads
ON [73106]. = Leads.email
WHERE Leads.email IS NULL;

Note that you don't need to alias the fieldnames to themselves, or
indeed alias them at all; the fields in the SELECT clause will be
inserted into the specified fields in LEADS in the order specified,
regardless of fieldname.

John W. Vinson[MVP]