Solved

Overwriting Duplicate SQL Data

Posted on 2006-07-10
6
1,167 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to not remove leading zeros 9 38
Adding icons to a custom, shortcut menu function 6 41
ms access 2013, running .mdb 2 29
how to link subforms ms/access VBA 6 26
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

930 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now