Solved

Overwriting Duplicate SQL Data

Posted on 2006-07-10
6
1,170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 350 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

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

Assisted Solution

by:jerryb30
jerryb30 earned 150 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

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!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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