insert into - want only records that don't already exist

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

I have two tables - EMAIL and PUBLISH. I use the access import feature to
import all emails into the table EMAIL. I then created a query to move "new"
records from EMAIL into PUBLISH so that I can modify the contents and flag
the emails with categories, etc. My query is missing records but I can't
figure out why.

I have three tables involved in this query:
ADDRESS - contains the person's email address and a people ID
EMAIL - contains the original email fields including email address and
contents of email, each email is assigned a unique ID
PUBLISH - want to contain the people ID, email ID and contents of the email

Query:
INSERT INTO PUBLISH ( [People ID], [Email ID], [Email Text] )
SELECT ADDRESS.[People ID], EMAIL.ID, EMAIL.Contents
FROM ADDRESS INNER JOIN EMAIL ON ADDRESS.[Email Address]=EMAIL.From
WHERE (((Exists (Select PUBLISH.[Email ID] from PUBLISH WHERE EMAIL.ID =
PUBLISH.[Email ID]))=False));

This query is adding about 90% of my emails from the EMAIL table into the
PUBLISH table. I can't figure out what I am missing. Basically, I want any
email that is in the EMAIL table and NOT in the PUBLISH table to be inserted
into the PUBLISH table, the Email's ID is the unique identifier.

Thanks in advance for any help!
 
Robin -

You get blank People IDs either because there is no address record yet for
that email address, or the ADDRESS.[Email Address] does not equal the
EMAIL.From field. You need to make sure the format is the same, and there
are no extra spaces in the fields anywhere.

--
Daryl S


Robin said:
Okay - I'm getting a little farther. This query returns the correct records,
but the People ID is blank. Any ideas?

SELECT ADDRESS.[People ID], EMAIL.ID, EMAIL.Contents
FROM (ADDRESS RIGHT JOIN (EMAIL LEFT JOIN PUBLISH ON EMAIL.ID =
PUBLISH.[Email ID]) ON ADDRESS.[Email Address]=EMAIL.From)
WHERE ((PUBLISH.[Email ID]) IS NULL);


Robin said:
I have two tables - EMAIL and PUBLISH. I use the access import feature to
import all emails into the table EMAIL. I then created a query to move "new"
records from EMAIL into PUBLISH so that I can modify the contents and flag
the emails with categories, etc. My query is missing records but I can't
figure out why.

I have three tables involved in this query:
ADDRESS - contains the person's email address and a people ID
EMAIL - contains the original email fields including email address and
contents of email, each email is assigned a unique ID
PUBLISH - want to contain the people ID, email ID and contents of the email

Query:
INSERT INTO PUBLISH ( [People ID], [Email ID], [Email Text] )
SELECT ADDRESS.[People ID], EMAIL.ID, EMAIL.Contents
FROM ADDRESS INNER JOIN EMAIL ON ADDRESS.[Email Address]=EMAIL.From
WHERE (((Exists (Select PUBLISH.[Email ID] from PUBLISH WHERE EMAIL.ID =
PUBLISH.[Email ID]))=False));

This query is adding about 90% of my emails from the EMAIL table into the
PUBLISH table. I can't figure out what I am missing. Basically, I want any
email that is in the EMAIL table and NOT in the PUBLISH table to be inserted
into the PUBLISH table, the Email's ID is the unique identifier.

Thanks in advance for any help!
Click to expand...
Click to expand...
 
Thank you so much - you are exactly right! I had an email address with an
extra blank in it that I did not notice. I REALLY APPRECIATE IT!!!

Daryl S said:
Robin -

You get blank People IDs either because there is no address record yet for
that email address, or the ADDRESS.[Email Address] does not equal the
EMAIL.From field. You need to make sure the format is the same, and there
are no extra spaces in the fields anywhere.

--
Daryl S


Robin said:
Okay - I'm getting a little farther. This query returns the correct records,
but the People ID is blank. Any ideas?

SELECT ADDRESS.[People ID], EMAIL.ID, EMAIL.Contents
FROM (ADDRESS RIGHT JOIN (EMAIL LEFT JOIN PUBLISH ON EMAIL.ID =
PUBLISH.[Email ID]) ON ADDRESS.[Email Address]=EMAIL.From)
WHERE ((PUBLISH.[Email ID]) IS NULL);


Robin said:
I have two tables - EMAIL and PUBLISH. I use the access import feature to
import all emails into the table EMAIL. I then created a query to move "new"
records from EMAIL into PUBLISH so that I can modify the contents and flag
the emails with categories, etc. My query is missing records but I can't
figure out why.

I have three tables involved in this query:
ADDRESS - contains the person's email address and a people ID
EMAIL - contains the original email fields including email address and
contents of email, each email is assigned a unique ID
PUBLISH - want to contain the people ID, email ID and contents of the email

Query:
INSERT INTO PUBLISH ( [People ID], [Email ID], [Email Text] )
SELECT ADDRESS.[People ID], EMAIL.ID, EMAIL.Contents
FROM ADDRESS INNER JOIN EMAIL ON ADDRESS.[Email Address]=EMAIL.From
WHERE (((Exists (Select PUBLISH.[Email ID] from PUBLISH WHERE EMAIL.ID =
PUBLISH.[Email ID]))=False));

This query is adding about 90% of my emails from the EMAIL table into the
PUBLISH table. I can't figure out what I am missing. Basically, I want any
email that is in the EMAIL table and NOT in the PUBLISH table to be inserted
into the PUBLISH table, the Email's ID is the unique identifier.

Thanks in advance for any help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top