Overwriting Duplicate SQL Data

I have an SQL statement that I am trying to run.  How the whole thing works is I import a table into my existing database.  The linked table is ARB1 and the one in the database is ARB.  The following is the SQL statement I have in my code to import data from ARB1 into ARB.

I get the error duplicate data.  How do I delete the duplicate data or delete the data first and then import the data?

DoCmd.RunSQL "INSERT INTO tblARB ( [Personnel ID Code] )SELECT tblStudent1.[Personnel ID Code], tblARB1.* FROM tblStudent1 LEFT JOIN tblARB1 ON tblStudent1.[Personnel ID Code] = tblARB1.[Personnel ID Code] WHERE (((tblStudent1.Instructor) Like [Who is the Instructor]));"

Thanks

JS
John SheehySecurity AnalystAsked:
Who is Participating?
 
jrb1Connect With a Mentor Commented:
This should delete any possible dups.

DoCmd.RunSQL "DELETE FROM tblARB where [Personnel ID Code] in (SELECT tblStudent1.[Personnel ID Code] FROM tblStudent1 LEFT JOIN tblARB1 ON tblStudent1.[Personnel ID Code] = tblARB1.[Personnel ID Code] WHERE (((tblStudent1.Instructor) Like [Who is the Instructor])));"
0
 
Raynard7Commented:
jrb is correct - run the above sql - then run your pre-existing sql statement for the inserts; this will remove the duplicates; then your insert will put everything in.
0
 
John SheehySecurity AnalystAuthor Commented:
I get the following error:

Duplicate Output destination 'Personnel ID Code'.

I have the link of personnel ID Code in both tables.  Is there a way around this?

JS
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
John SheehySecurity AnalystAuthor Commented:
Up'd the points
0
 
jerryb30Connect With a Mentor Commented:
Real quick way:  Open query in design view.  You will see that you have [personnel id code] twice (in the arb.* and the
tblStudent1.[Personnel ID Code] field both having [Personnel ID Code] columns. Simply make sure the tblStudent1.[Personnel ID Code] column has no value in the [append to] field.
0
 
John SheehySecurity AnalystAuthor Commented:
Now that worked out great.  I am upping the points to 500 and split accordingly.

JS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.