Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Overwriting Duplicate SQL Data

Posted on 2006-07-10
6
Medium Priority
?
1,174 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:John Sheehy
6 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 1400 total points
ID: 17077638
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
 
LVL 35

Expert Comment

by:Raynard7
ID: 17077661
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
 

Author Comment

by:John Sheehy
ID: 17077737
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:John Sheehy
ID: 17077738
Up'd the points
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 600 total points
ID: 17077780
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
 

Author Comment

by:John Sheehy
ID: 17077800
Now that worked out great.  I am upping the points to 500 and split accordingly.

JS
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question