Append data from one table to another, skipping duplicates (1 Viewer)

sherlocked

Registered User.
Local time
Today, 04:23
Joined
Sep 22, 2014
Messages
125
Greetings and here's hoping you can help with my conundrum.

I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.

I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.

Any help you can provide will be greatly appreciated! I think there is a way to do this by creating a recordset and looping through to check for dups but my VBA kung fu is beginner level.

INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism )
SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism
FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN
WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));
 

TJPoorman

Registered User.
Local time
Today, 05:23
Joined
Jul 23, 2013
Messages
402
Without seeing your table structure I can't give an absolute answer, but to get you on the right track... Look into an outer join of your select statement to your tblNewHire. Then you can limit the list by using a WHERE on the ID field Is Not Null to limit your results.
 

sherlocked

Registered User.
Local time
Today, 04:23
Joined
Sep 22, 2014
Messages
125
Hi TJ,

Can you give me an idea as to what that would look like? I already have an Inner Join between two different tables, as you can see. Can I possibly add another outer join? I really don't know what that would look like.

I apologize in advance for my lack of VBA skill! :mad:
 

Users who are viewing this thread

Top Bottom