I tried your sql code code Arsalan. It was slow at first because currently I'm using a temp table to store the records identified as being duplicates. But a colleague of mine at work suggested that I enable the index both for the USERID and CHECKTIME in MS Access (duplicates allowed), thus your sql code is running much quicker now. It works! Thanks! :-)
Benj On Jan 24, 7:17 pm, Arsalan Tamiz <[email protected]> wrote: > I am assuming (please correct if wrong) your Duplicate Rows = Rows having > UserID and CheckTime both are equal > > On idle condition the speed of your query depends on number of rows in table > and indexing. You can delete the duplicate rows without using temporary > table, like this, > > DELETE FROM checkinout AS A > WHERE Exists( > SELECT 1 > FROM checkinout > WHERE UserID = A.UserID AND CheckTime = A.CheckTime > GROUP BY UserID,CheckTime > HAVING Count(CheckTime) > 1 > ) > > This will delete "all" the duplicate rows, for example, > > userid chcektime > 1 1-jan-2009 1:00 PM > 1 1-jan-2009 1:00 PM > 1 1-jan-2009 1:00 PM > > All the above 3 rows will be deleted. And if your requirement is to delete > only the 2 rows (merging duplicates in 1 row) then you need to add another > column which should distinguish the 3 rows and then use that new column in > your query. Note this statement is true even if you are using some other > temporary table with same columns. > > On Sun, Jan 24, 2010 at 2:07 PM, Benj Nunez <[email protected]> wrote: > > The query came from the "Find Duplicate Wizard" of MS Access. I simply > > re-used it. The idea > > was to delete the duplicate coming out as a result of this query. > > > Btw, I've tried storing the duplicate in a temp table and ran this > > query: > > > delete from checkinout A > > where exists(select B.userid, B.checktime > > from tempcheckinout B > > where A.userid = B.userid > > and A.checktime = B.checktime) > > > It works, but it's slow. Has anyone tried removing duplicates like > > this in Access SQL? > > > On Jan 22, 2:38 pm, Arsalan Tamiz <[email protected]> wrote: > > > Your query is NOT logically valid or simply you should type the same > > query > > > as, > > > > SELECT CheckTime, UserID > > > > FROM CheckInOut > > > > ORDER BY CheckTime > > > What your current query saying is, select all rows from "checkinout" > > table > > > where "checktime" should have values coming from same table. Which means > > you > > > are selecting the rows from same table where your are looking for a > > value. > > > > So if you want to get out the duplicates then you should be doing > > "Grouping" > > > and "Counting". > > > > On Fri, Jan 22, 2010 at 11:07 AM, Benj Nunez <[email protected]> > > wrote: > > > > Hello experts, > > > > > I'm currently doing a C# project which involves removing duplicate > > > > time entries in Access. > > > > I was able to store the unique records safely to a collection using a > > > > Dictionary. Now I need > > > > to remove/purge the database of all the duplicates involved. One way > > > > to retrieve all duplicate > > > > records is to run this query which I got from the Access Wizard: > > > > > SELECT CHECKINOUT.CHECKTIME, CHECKINOUT.USERID > > > > FROM CHECKINOUT > > > > WHERE (((CHECKINOUT.CHECKTIME) IN (SELECT [CHECKTIME] FROM > > > > [CHECKINOUT] AS TMP ))) > > > > ORDER BY CHECKINOUT.CHECKTIME; > > > > > Now, I want to delete the records out of it by using sql delete like > > > > this: > > > > > Delete from CheckInOut where exists( > > > > SELECT CHECKINOUT.CHECKTIME, CHECKINOUT.USERID > > > > FROM CHECKINOUT > > > > WHERE (((CHECKINOUT.CHECKTIME) IN (SELECT [CHECKTIME] FROM > > > > [CHECKINOUT] AS TMP ))) > > > > ORDER BY CHECKINOUT.CHECKTIME) > > > > > When run through C# code (using OleDbCommand), I get an exception > > > > like: "Attempted to read or write protected memory. This is often an > > > > indication that other memory is corrupt". > > > > > When run through MS Access, I get a crash report which states: > > > > "Microsoft Office Access has > > > > encountered a problem and needs to close...." > > > > > Is there another way for me to remove the duplicates using an > > > > alternative sql command? > > > > > Benj
