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
>
