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
