Duplicate Check 1

[nwalk81]

I am stuck right now and would really appreciate any assistance anyone can offer.

I am trying to avoid getting duplicates into the main tale of my database. So far I have all records being imported into a temp table. What I need is to check the temp table against the main table using VB. If the records exist in main table I would like it to be ignored, and if the record does not exist in the main table I would like to append the distinct records to the main table.

I would appreciate any form of assistance.

 

[Remou]

You can use a query to match the records and only append the records where there is no match. For example:
[tt]INSERT INTO Table1 ( ID, Field1 )
SELECT TableTemp.ID, TableTemp.Field1
FROM Table1
RIGHT JOIN TableTemp ON Table1.ID = TableTemp.ID
WHERE Table1.ID Is Null;[/tt]

 

[nwalk81]

Thanks Remou you are a genious. Everything worked in a query, now I'm trying to apply the code to VB, but it's getting a syntax error on the code there

DoCmd.RunSQL(INSERT INTO EmpOnLeave ([Date Loaded], ID, [First Name], [Last], [Job Title], Function, [Location Descr], [VZW Area Code], [Location State], Status, [Service Dt], [Rehire Dt], Phone, [Supv Name], [Claim Type], [Claim Code], [Date Reptd], [Last Date], [ML Expected Return Dt], [Return Dt], [Claim Status Code], [Claim Timestamp], [Stat From Date], [Stat Thru Date], [Claim Status Reason Code], [Denial Rsn Code], [Appeal Dt], [Address 1], [Address 2], City, St, Postal, Email, [Claim #2], [Claim #1 Type], [FMLA Status], [Cert Start], [Cert End Date])
SELECT [tmpEmpOnLeave].[Date Loaded], [tmpEmpOnLeave].[ID], [tmpEmpOnLeave].[First Name], [tmpEmpOnLeave].[Last], [tmpEmpOnLeave].[Job Title], [tmpEmpOnLeave].[Function], [tmpEmpOnLeave].[Location Descr], [tmpEmpOnLeave].[VZW Area Code], [tmpEmpOnLeave].[Location State], [tmpEmpOnLeave].[Status], [tmpEmpOnLeave].[Service Dt], [tmpEmpOnLeave].[Rehire Dt], [tmpEmpOnLeave].[Phone], [tmpEmpOnLeave].[Supv Name], [tmpEmpOnLeave].[Claim Type], [tmpEmpOnLeave].[Claim Code], [tmpEmpOnLeave].[Date Reptd], [tmpEmpOnLeave].[Last Date], [tmpEmpOnLeave].[ML Expected Return Dt], [tmpEmpOnLeave].[Return Dt], [tmpEmpOnLeave].[Claim Status Code], [tmpEmpOnLeave].[Claim Timestamp], [tmpEmpOnLeave].[Stat From Date], [tmpEmpOnLeave].[Stat Thru Date], [tmpEmpOnLeave].[Claim Status Reason Code], [tmpEmpOnLeave].[Denial Rsn Code], [tmpEmpOnLeave].[Appeal Dt], [tmpEmpOnLeave].[Address 1], [tmpEmpOnLeave].[Address 2], [tmpEmpOnLeave].[City], [tmpEmpOnLeave].[St], [tmpEmpOnLeave].[Postal], [tmpEmpOnLeave].[Email], [tmpEmpOnLeave].[Cla
im #2], [tmpEmpOnLeave].[Claim #1 Type], [tmpEmpOnLeave].[FMLA Status], [tmpEmpOnLeave].[Cert Start], [tmpEmpOnLeave].[Cert End Date]
FROM EmpOnLeave RIGHT JOIN tmpEmpOnLeave ON [EmpOnLeave].[Claim Code]=[tmpEmpOnLeave].[Claim Code]
WHERE [EmpOnLeave].[Claim Code] Is Null)

Thanks again

 

[nwalk81]

can you assist me with why I'm getting an expected list separator or ) with emphasis on the INTO section of the statement error

Thank you

 

[Remou]

The SQL needs to be a string.

[tt]DoCmd.RunSQL [red]"[/red]INSERT INTO EmpOnLeave ... Is Null[red]"[/red][/tt]

 

[nwalk81]

Sorry to be a bother, but I'm so close to getting this. Now I get a Syntax error with emphasis on the SELECT section of the statement

 

[Remou]

It all needs to be on one line, or better still, neatly broken up:

[tt]strSQL="INSERT INTO EmpOnLeave " _
& "([Date Loaded], ID, [First Name], " _
& "[Last], [Job Title], Function, " _
& "[Location Descr], [VZW Area Code], " _
& "[Location State], Status, [Service Dt], " _
& "[Rehire Dt], Phone, <... and so on ...>[/tt]

I think you may have a problem in the way the line is broken at [tmpEmpOnLeave].[Claim #2]

 

[nwalk81]

Good morning,

Hope your weekend was well. I came in this morning excited to complete this task however after breaking all the code up neatly and trying to run it I received a "Complie error: Invaild outside procedure" in reference to "INSERT INTO EmpOnLeave "

Can you help?

 

[nwalk81]

Figured it out thanks a million Remou