OK, here's my problem, without having to be too specific for reasons to be fired for putting company practices on the web.
You will find excel spreadsheets made. They are submitted towards the database. I have to remove replicates from uploading. The only method to do that is as simple as ensuring for every single entry that two fields differ from an entry already within the database. As just becasue one area is identical does not necessarily mean its a replica. You will find two specific fields allows give them a call FLDA and FLDB that both must match up with an entry within the database already. I'm able to filter by one area already. I am thinking this needs to be considered a subquery but I am unsure how you can put it on. This really is difficult to decribe. Just request in case your unsure what i'm saying.
I'd an identical problem. My solution ended up being to:
- import right into a staging-table.
- remove the replicates
- copy what remains over in to the live table
It is a little BFI, however it simply works.
Would a question suit? For instance:
INSERT INTO ToUpdate ( Field1, Field2 ) SELECT e.H1, e.H2 FROM (SELECT * FROM [Sheet1$] IN '' [Excel 8.0;HDR=YES;IMEX=1;database=C:\Docs\LTD.xls]) As e LEFT JOIN ToUpdate ON (e.H2 = ToUpdate.Field2) AND (e.H1 = ToUpdate.Field1) WHERE ToUpdate.Field1 Is Null AND ToUpdate.Field2 Is Null
How's it going loading them in to the database? Is with your personal code to see the Stand out files? You are able to read the Excel files using ADO/ADO.NET with the right connection string. Then you may browse the data utilizing a query that will discount the dupes.
Building on CodeSlave's answer, Access supplies a Find Replicates Query wizard that will help you easily build the query to discount the replicates.
Another approach is always to setup a name on FLDA and FLDB. This can prevent duplicate records from even getting written towards the table. Obviously you must also catch the exception that's tossed once the place operation fails.
It is possible to area FLDC that might be different for determining replicates? I presume there has to be, as otherwise getting (FLDA,FLDB) like a unique or primary key would solve your condition immediately.
Presuming there's this type of area, you could attempt something similar to this:
SELECT T1.FLDA, T1.FLDB, T1.FLDC FROM Table1 T1, Table1 T2 WHERE T1.FLDA = T2.FLDA AND T1.FLDB = T2.FLDB AND T1.FLDC <> T2.FLDC
The down-side here's that both original and also the duplicate is going to be came back by something similar to this. Should you simply want to begin to see the replicates, you'll most likely need to discover a method to recognize an 'original' row and add another WHERE clause or two with this.
If you can aquire a query that provides you simply the duplicate rows and never the originals, it ought to be pretty simple to change it out to some Remove query.