Deleting Duplicate Records

[FrankM]

I have a database with duplicate records within a Table. The ENTIRE record is
duplicate except for the Key which is an Auto Number which Access
automatically enters.

I found these directions ...

http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#15

And have followed them to the letter but I keep getting the error message ...

"Could not delete from the specified tables"

My SQL looks like this ...

DELETE TABLE.*, TABLE.[Event Name], TABLE.[Event Date/Time], TABLE.[First
Name], TABLE.[Last Name], TABLE.Enrolled, TABLE.Attended, TABLE.Email,
TABLE.[Enrollment ID], TABLE.[Customer ID], TABLE.ID
FROM TABLE INNER JOIN [QUERY1] AS [Distinct] ON TABLE.ID = Distinct.FirstOfID
WHERE (((TABLE.[Event Name])=[Distinct].[Event Name]) AND ((TABLE.[Event
Date/Time])=[Distinct].[Event Date/Time]) AND ((TABLE.[First
Name])=[Distinct].[First Name]) AND ((TABLE.[Last Name])=[Distinct].[Last
Name]) AND ((TABLE.Enrolled)=[Distinct].[Enrolled]) AND
((TABLE.Attended)=[Distinct].[Attended]) AND
((TABLE.Email)=[Distinct].) AND ((TABLE.[Enrollment
ID])=[Distinct].[Enrollment ID]) AND ((TABLE.[Customer
ID])=[Distinct].[Customer ID]) AND ((TABLE.ID)<>[Distinct].[FirstOfID]));

Anyone know why this isn't working. I'm using Access 2003 and it seems like
it should work. I have no idea where I'm going wrong.

 

[Ken Snell \(MVP\)]

Try this:

DELETE DISTINCT T.*
FROM

AS T INNER JOIN [QUERY1] AS D
ON T.ID = D.FirstOfID
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D. ) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID]));


Or this:

DELETE T.*
FROM [TABLE] AS T
WHERE T.ID IN
(SELECT D.FirstOfID
FROM [QUERY1] AS D
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D.[Email]) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID])));

 

[John Spencer]

Unless I am missing something the first query should never delete anything as
there seems to be a basic conflict of JOINING on T.ID = D.FirstofID and then
setting criteria of T.ID <> D.FirstOfID.

The second one seems to be a problem also. As I think the poster wants to keep
the records where T.ID matches D.FirstOfID and if the subquery was successful it
would only return values with D.FirstOfID. In other words the poster wants to
retain one and only one record that has the duplicated fields over the entire table.

BEFORE you try any of this, I would suggest you make a backup of your data. If
something goes wrong, the backup may be the only way to recover.

One way to handle this would be to build a new table with a compound unique
index based on the nine fields. Then import all the records into this new table
and ignore the errors. Once the import is successful, delete the old table and
rename the new table to the old table's name.


Another way,
Build a query based on the table that will identify primary key you want to keep
and save that as qKeepThese

SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name],
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id]


Now use that query to do the delete.
DELETE DistinctROW T.*
FROM TABLE AS T LEFT JOIN qKeepThese
ON T.ID = qKeepThese.FirstID

Another alternative is to use NOT IN as follows. This should work, although I
think it will be very slow especially with a large recordset - not in is not
fast in Access or MS SQL and also gives "strange" - but correct - results if
nulls are returned in the returned field of the subquery.

DELETE DistinctRow T.*
FROM Table as T
WHERE T.ID NOT IN (
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name],
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id])

Try this:

DELETE DISTINCT T.*
FROM

AS T INNER JOIN [QUERY1] AS D
ON T.ID = D.FirstOfID
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D. ) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID]));

Or this:

DELETE T.*
FROM [TABLE] AS T
WHERE T.ID IN
(SELECT D.FirstOfID
FROM [QUERY1] AS D
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D.[Email]) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID])));

--

Ken Snell
<MS ACCESS MVP>

[QUOTE="FrankM"]
I have a database with duplicate records within a Table. The ENTIRE record
is
duplicate except for the Key which is an Auto Number which Access
automatically enters.

I found these directions ...

http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#15

And have followed them to the letter but I keep getting the error message
...

"Could not delete from the specified tables"

My SQL looks like this ...

DELETE TABLE.*, TABLE.[Event Name], TABLE.[Event Date/Time], TABLE.[First
Name], TABLE.[Last Name], TABLE.Enrolled, TABLE.Attended, TABLE.Email,
TABLE.[Enrollment ID], TABLE.[Customer ID], TABLE.ID
FROM TABLE INNER JOIN [QUERY1] AS [Distinct] ON TABLE.ID =
Distinct.FirstOfID
WHERE (((TABLE.[Event Name])=[Distinct].[Event Name]) AND ((TABLE.[Event
Date/Time])=[Distinct].[Event Date/Time]) AND ((TABLE.[First
Name])=[Distinct].[First Name]) AND ((TABLE.[Last Name])=[Distinct].[Last
Name]) AND ((TABLE.Enrolled)=[Distinct].[Enrolled]) AND
((TABLE.Attended)=[Distinct].[Attended]) AND
((TABLE.Email)=[Distinct].[Email]) AND ((TABLE.[Enrollment
ID])=[Distinct].[Enrollment ID]) AND ((TABLE.[Customer
ID])=[Distinct].[Customer ID]) AND ((TABLE.ID)<>[Distinct].[FirstOfID]));

Anyone know why this isn't working. I'm using Access 2003 and it seems
like
it should work. I have no idea where I'm going wrong.
[/QUOTE][/QUOTE]

 

[Ken Snell \(MVP\)]

Unless I am missing something the first query should never delete anything
as
there seems to be a basic conflict of JOINING on T.ID = D.FirstofID and
then
setting criteria of T.ID <> D.FirstOfID.

Excellent catch, John. I had missed that exclusion. Thanks.

 

[FrankM]

John, thank you, thank you, thank you.

I had a back-up of the database already, although that is always a good
recommendation. I was working off of the back-up.

I did not like your first recommendation because this likely won't be a one
time function and unless I built append queries and delete queries and all
sorts of other stuff I didn't like that suggestion.

The second suggestion you made seemed similar to what I had already tried so
I tried it and was very careful to follow it very closely but again I
received the error message, "Could not delete from the specified tables".
Dang!

Lastly I tried your final recommendation, which I liked quite a bit because
it could be done with one query vs two and because it didn't involve creating
additional Table or a complex process. I duly noted your warning that it
could be a slow process but that did not bother me.

IT WORKED !!!!!!!!!!!!!!!!!!!!!!!!!!!

It worked beautifully. I double checked the original records, the records I
thought should be deleted, the records I thought should remain and in my
small test it did everything exactly as it should. I then moved it into a
largeer test with a couple hundred records and again it did exactly what it
was suppose to. I'm going to tst it a few more times before I move it into
Production but I am very, very optimistic. It has solved a huge issue for me.
This is awesome and I couldn't be happier.

THANK YOU

Unless I am missing something the first query should never delete anything as
there seems to be a basic conflict of JOINING on T.ID = D.FirstofID and then
setting criteria of T.ID <> D.FirstOfID.

The second one seems to be a problem also. As I think the poster wants to keep
the records where T.ID matches D.FirstOfID and if the subquery was successful it
would only return values with D.FirstOfID. In other words the poster wants to
retain one and only one record that has the duplicated fields over the entire table.

BEFORE you try any of this, I would suggest you make a backup of your data. If
something goes wrong, the backup may be the only way to recover.

One way to handle this would be to build a new table with a compound unique
index based on the nine fields. Then import all the records into this new table
and ignore the errors. Once the import is successful, delete the old table and
rename the new table to the old table's name.


Another way,
Build a query based on the table that will identify primary key you want to keep
and save that as qKeepThese

SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name],
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id]


Now use that query to do the delete.
DELETE DistinctROW T.*
FROM TABLE AS T LEFT JOIN qKeepThese
ON T.ID = qKeepThese.FirstID

Another alternative is to use NOT IN as follows. This should work, although I
think it will be very slow especially with a large recordset - not in is not
fast in Access or MS SQL and also gives "strange" - but correct - results if
nulls are returned in the returned field of the subquery.

DELETE DistinctRow T.*
FROM Table as T
WHERE T.ID NOT IN (
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name],
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id])

Try this:

DELETE DISTINCT T.*
FROM

AS T INNER JOIN [QUERY1] AS D
ON T.ID = D.FirstOfID
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D. ) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID]));

Or this:

DELETE T.*
FROM [TABLE] AS T
WHERE T.ID IN
(SELECT D.FirstOfID
FROM [QUERY1] AS D
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D.[Email]) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID])));

--

Ken Snell
<MS ACCESS MVP>

[QUOTE="FrankM"]
I have a database with duplicate records within a Table. The ENTIRE record
is
duplicate except for the Key which is an Auto Number which Access
automatically enters.

I found these directions ...

http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#15

And have followed them to the letter but I keep getting the error message
...

"Could not delete from the specified tables"

My SQL looks like this ...

DELETE TABLE.*, TABLE.[Event Name], TABLE.[Event Date/Time], TABLE.[First
Name], TABLE.[Last Name], TABLE.Enrolled, TABLE.Attended, TABLE.Email,
TABLE.[Enrollment ID], TABLE.[Customer ID], TABLE.ID
FROM TABLE INNER JOIN [QUERY1] AS [Distinct] ON TABLE.ID =
Distinct.FirstOfID
WHERE (((TABLE.[Event Name])=[Distinct].[Event Name]) AND ((TABLE.[Event
Date/Time])=[Distinct].[Event Date/Time]) AND ((TABLE.[First
Name])=[Distinct].[First Name]) AND ((TABLE.[Last Name])=[Distinct].[Last
Name]) AND ((TABLE.Enrolled)=[Distinct].[Enrolled]) AND
((TABLE.Attended)=[Distinct].[Attended]) AND
((TABLE.Email)=[Distinct].[Email]) AND ((TABLE.[Enrollment
ID])=[Distinct].[Enrollment ID]) AND ((TABLE.[Customer
ID])=[Distinct].[Customer ID]) AND ((TABLE.ID)<>[Distinct].[FirstOfID]));

Anyone know why this isn't working. I'm using Access 2003 and it seems
like
it should work. I have no idea where I'm going wrong.
[/QUOTE][/QUOTE]
[/QUOTE]

 

[John Spencer]

Glad you got it working. I did notice a few things that I did wrong
yesterday.

This had an extra comma in it on line four
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name]
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id]


This was missing an all important where clause
DELETE DistinctROW T.*
FROM TABLE AS T LEFT JOIN qKeepThese
ON T.ID = qKeepThese.FirstID
WHERE qKeepThese.FirstID is Null


Another alternative is to use NOT IN as follows. This should work,
although I
think it will be very slow especially with a large recordset - not in is not
fast in Access or MS SQL and also gives "strange" - but correct - results if
nulls are returned in the returned field of the subquery.

THIS had and extra comma in line seven
DELETE DistinctRow T.*
FROM Table as T
WHERE T.ID NOT IN (
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name]
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id])

John, thank you, thank you, thank you.

I had a back-up of the database already, although that is always a good
recommendation. I was working off of the back-up.

I did not like your first recommendation because this likely won't be a
one
time function and unless I built append queries and delete queries and all
sorts of other stuff I didn't like that suggestion.

The second suggestion you made seemed similar to what I had already tried
so
I tried it and was very careful to follow it very closely but again I
received the error message, "Could not delete from the specified tables".
Dang!

Lastly I tried your final recommendation, which I liked quite a bit
because
it could be done with one query vs two and because it didn't involve
creating
additional Table or a complex process. I duly noted your warning that it
could be a slow process but that did not bother me.

IT WORKED !!!!!!!!!!!!!!!!!!!!!!!!!!!

It worked beautifully. I double checked the original records, the records
I
thought should be deleted, the records I thought should remain and in my
small test it did everything exactly as it should. I then moved it into a
largeer test with a couple hundred records and again it did exactly what
it
was suppose to. I'm going to tst it a few more times before I move it into
Production but I am very, very optimistic. It has solved a huge issue for
me.
This is awesome and I couldn't be happier.

THANK YOU

Unless I am missing something the first query should never delete
anything as
there seems to be a basic conflict of JOINING on T.ID = D.FirstofID and
then
setting criteria of T.ID <> D.FirstOfID.

The second one seems to be a problem also. As I think the poster wants
to keep
the records where T.ID matches D.FirstOfID and if the subquery was
successful it
would only return values with D.FirstOfID. In other words the poster
wants to
retain one and only one record that has the duplicated fields over the
entire table.

BEFORE you try any of this, I would suggest you make a backup of your
data. If
something goes wrong, the backup may be the only way to recover.

One way to handle this would be to build a new table with a compound
unique
index based on the nine fields. Then import all the records into this
new table
and ignore the errors. Once the import is successful, delete the old
table and
rename the new table to the old table's name.


Another way,
Build a query based on the table that will identify primary key you want
to keep
and save that as qKeepThese

SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name],
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id]


Now use that query to do the delete.
DELETE DistinctROW T.*
FROM TABLE AS T LEFT JOIN qKeepThese
ON T.ID = qKeepThese.FirstID

Another alternative is to use NOT IN as follows. This should work,
although I
think it will be very slow especially with a large recordset - not in is
not
fast in Access or MS SQL and also gives "strange" - but correct - results
if
nulls are returned in the returned field of the subquery.

DELETE DistinctRow T.*
FROM Table as T
WHERE T.ID NOT IN (
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name],
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id])

Try this:

DELETE DISTINCT T.*
FROM

AS T INNER JOIN [QUERY1] AS D
ON T.ID = D.FirstOfID
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D. ) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID]));

Or this:

DELETE T.*
FROM [TABLE] AS T
WHERE T.ID IN
(SELECT D.FirstOfID
FROM [QUERY1] AS D
WHERE (((T.[Event Name])=D.[Event Name]) AND
((T.[Event Date/Time])=D.[Event Date/Time]) AND
((T.[First Name])=D.[First Name]) AND
((T.[Last Name])=D.[Last Name]) AND
((T.Enrolled)=D.[Enrolled]) AND
((T.Attended)=D.[Attended]) AND
((T.Email)=D.[Email]) AND
((T.[Enrollment ID])=D.[Enrollment ID]) AND
((T.[Customer ID])=D.[Customer ID]) AND
((T.ID)<>D.[FirstOfID])));

--

Ken Snell
<MS ACCESS MVP>

I have a database with duplicate records within a Table. The ENTIRE
record
is
duplicate except for the Key which is an Auto Number which Access
automatically enters.

I found these directions ...

http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#15

And have followed them to the letter but I keep getting the error
message
...

"Could not delete from the specified tables"

My SQL looks like this ...

DELETE TABLE.*, TABLE.[Event Name], TABLE.[Event Date/Time],
TABLE.[First
Name], TABLE.[Last Name], TABLE.Enrolled, TABLE.Attended,
TABLE.Email,
TABLE.[Enrollment ID], TABLE.[Customer ID], TABLE.ID
FROM TABLE INNER JOIN [QUERY1] AS [Distinct] ON TABLE.ID =
Distinct.FirstOfID
WHERE (((TABLE.[Event Name])=[Distinct].[Event Name]) AND
((TABLE.[Event
Date/Time])=[Distinct].[Event Date/Time]) AND ((TABLE.[First
Name])=[Distinct].[First Name]) AND ((TABLE.[Last
Name])=[Distinct].[Last
Name]) AND ((TABLE.Enrolled)=[Distinct].[Enrolled]) AND
((TABLE.Attended)=[Distinct].[Attended]) AND
((TABLE.Email)=[Distinct].[Email]) AND ((TABLE.[Enrollment
ID])=[Distinct].[Enrollment ID]) AND ((TABLE.[Customer
ID])=[Distinct].[Customer ID]) AND
((TABLE.ID)<>[Distinct].[FirstOfID]));

Anyone know why this isn't working. I'm using Access 2003 and it
seems
like
it should work. I have no idea where I'm going wrong.
[/QUOTE]
[/QUOTE][/QUOTE]

 

[ppc]

Dear John

I am having access 2000 and trying to do same thing as FrankM but not able to
do.
The qKeepThese works fine but DELETE DistinctROW is giving errors.

The SQL statement for qKeepThese is:

SELECT First([Month]) AS FirstMonth
FROM ppcname
GROUP BY [TELNO], [USERNAME], [ACCOUNTNO], [GrossSendMBytes],
[GrossReceiveMBytes], [GrossTimeLength], [FreeSendMBytes], [FreeReceiveMBytes]
, [FreeTimeLength], [PlanCode];

The SQL Statement for deldup is:

DELETE DISTINCTROW ppcname.*
FROM TABLE AS ppcname LEFT JOIN qKeepThese ON ppcname.MONTH=qKeepThese.
FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

When I run query deldup it gives following error:

"syntax error in FROM clause"

what may be wrong?

regds
prasanna

Glad you got it working. I did notice a few things that I did wrong
yesterday.

This had an extra comma in it on line four
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name]
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id]

This was missing an all important where clause
DELETE DistinctROW T.*
FROM TABLE AS T LEFT JOIN qKeepThese
ON T.ID = qKeepThese.FirstID
WHERE qKeepThese.FirstID is Null

Another alternative is to use NOT IN as follows. This should work,
although I
think it will be very slow especially with a large recordset - not in is not
fast in Access or MS SQL and also gives "strange" - but correct - results if
nulls are returned in the returned field of the subquery.

THIS had and extra comma in line seven
DELETE DistinctRow T.*
FROM Table as T
WHERE T.ID NOT IN (
SELECT First(ID) as FirstID
FROM TheTable
GROUP BY [Event Name], [Event Date/Time]
, [First Name], [Last Name]
, Enrolled, Attended, EMail
, [Enrollment Id], [Customer Id])

John, thank you, thank you, thank you.

[quoted text clipped - 176 lines]

 

[Gary Walter]

DELETE DISTINCTROW ppcname.*
FROM ppcname LEFT JOIN qKeepThese
ON ppcname.MONTH=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

 

[ppc via AccessMonster.com]

Thanks Gary

Tried that previously but getting error as below:

"Could not delete from specified tables"

I am having the table ppcname with 11785 records.The qKeepThese query
correctly returns all but one duplicate records(3661).But as I said not able
to remove the same from ppcname.

Where is the hitch?

regds
prasanna

 

[Gary Walter]

I don't know....

DELETE DISTINCTROW *
FROM ppcname LEFT JOIN qKeepThese
ON ppcname.[MONTH]=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

Do you have a primary key defined for
table ppcname?

When a delete query includes 2 or more tables,
you must use DISTINCTROW, but it is a strange
bird.

(from John Viescas
"DISTINCTROW (the default in Access version 7.0 and earlier) requests that
Access return only rows in which the concatenation of the primary keys from
**all tables supplying output columns** is unique. Depending on the columns
you
select, you might see rows in the result that contain duplicate values, but
each row in the result is derived from a distinct combination of rows in the
underlying tables."

So...you gotta use DISTINCTROW...
which depends on a primary key (or keys)...
if ppcname has no primary key,
how is DISTINCTROW going to work?

Or...I could be wrong....

 

[ppc via AccessMonster.com]

As you see I am having 20 Fields table ppcname.Two of them are USERNAME and
MONTH.In the sample rows produced below I want to remove all but one record
where username is cyberinn with month as say 200609.Not possible for me to
define any key as Primary as the table is already created

USERNAME MONTH
cyberinn 200609
cyberinn 200602
cyberinn 200609
cyberinn 200511
cyberinn 200609
cyberinn 200603
cyberinn 200605
cyberinn 200609
cyberinn 200609
cyberinn 200509
cyberinn 200609
cyberinn 200606
cyberinn 200609
cyberinn 200609
cyberinn 200508
cyberinn 200512
cyberinn 200607
cyberinn 200609
cyberinn 200609
cyberinn 200608
cyberinn 200510
cyberinn 200609
cyberinn 200604
cyberinn 200601
cyberinn 200609
cyberinn 200508
cyberinn 200609

Is it possible

Regds
Prasanna

I don't know....

DELETE DISTINCTROW *
FROM ppcname LEFT JOIN qKeepThese
ON ppcname.[MONTH]=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

Do you have a primary key defined for
table ppcname?

When a delete query includes 2 or more tables,
you must use DISTINCTROW, but it is a strange
bird.

(from John Viescas
"DISTINCTROW (the default in Access version 7.0 and earlier) requests that
Access return only rows in which the concatenation of the primary keys from
**all tables supplying output columns** is unique. Depending on the columns
you
select, you might see rows in the result that contain duplicate values, but
each row in the result is derived from a distinct combination of rows in the
underlying tables."

So...you gotta use DISTINCTROW...
which depends on a primary key (or keys)...
if ppcname has no primary key,
how is DISTINCTROW going to work?

Or...I could be wrong....

Tried that previously but getting error as below:

[quoted text clipped - 14 lines]

 

[Gary Walter]

Please explain why you cannot open table
ppcname in design mode, add a field (say "PPCID")
that is Autonumber, then set that field as
the Primary Key....

while you are at it, think about changing field
name from "MONTH" to something that is not
an Access Reserved word...

links from previous Ken Snell post:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

As you see I am having 20 Fields table ppcname.Two of them are USERNAME
and
MONTH.In the sample rows produced below I want to remove all but one
record
where username is cyberinn with month as say 200609.Not possible for me to
define any key as Primary as the table is already created

USERNAME MONTH
cyberinn 200609
cyberinn 200602
cyberinn 200609
cyberinn 200511
cyberinn 200609
cyberinn 200603
cyberinn 200605
cyberinn 200609
cyberinn 200609
cyberinn 200509
cyberinn 200609
cyberinn 200606
cyberinn 200609
cyberinn 200609
cyberinn 200508
cyberinn 200512
cyberinn 200607
cyberinn 200609
cyberinn 200609
cyberinn 200608
cyberinn 200510
cyberinn 200609
cyberinn 200604
cyberinn 200601
cyberinn 200609
cyberinn 200508
cyberinn 200609

Is it possible

Regds
Prasanna

I don't know....

DELETE DISTINCTROW *
FROM ppcname LEFT JOIN qKeepThese
ON ppcname.[MONTH]=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

Do you have a primary key defined for
table ppcname?

When a delete query includes 2 or more tables,
you must use DISTINCTROW, but it is a strange
bird.

(from John Viescas
"DISTINCTROW (the default in Access version 7.0 and earlier) requests that
Access return only rows in which the concatenation of the primary keys
from
**all tables supplying output columns** is unique. Depending on the
columns
you
select, you might see rows in the result that contain duplicate values,
but
each row in the result is derived from a distinct combination of rows in
the
underlying tables."

So...you gotta use DISTINCTROW...
which depends on a primary key (or keys)...
if ppcname has no primary key,
how is DISTINCTROW going to work?

Or...I could be wrong....

Tried that previously but getting error as below:

[quoted text clipped - 14 lines]

ON ppcname.MONTH=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

 

[ppc via AccessMonster.com]

I changed field name from MONTH to BBMONTH but it makes no difference.
When I tried to insert PPCID as pri key it gives me error "File sharing lock
count exceeded Increase MaxLocksPerFile registry entry".Tried changing that
entry in registry from default 9500 to upto 25000 in steps but the error is
same.

Able to open ppcname in design mode but when try to set BBMONTH as pri key it
gives error "The changes you requested weren't successful because they would
create duplicate values in the index,pri key, or relationship"

regds
prasanna

Please explain why you cannot open table
ppcname in design mode, add a field (say "PPCID")
that is Autonumber, then set that field as
the Primary Key....

while you are at it, think about changing field
name from "MONTH" to something that is not
an Access Reserved word...

links from previous Ken Snell post:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

As you see I am having 20 Fields table ppcname.Two of them are USERNAME
and

[quoted text clipped - 76 lines]

 

[Gary Walter]

Hmmm.....

Where did this data come from?

The reason I ask is that just last week I
was whipping out a "quick-and-dirty" app
that involved creating a view on SQL Server,
then started with a make-table query from link
to this view.

I have done this so many times before....

After I get the "make-table," I add an Access
"ID" autonumber field, make it the primary key,
then change make-table query to append query.

Many of our apps just need this "snapshot" arrangement
that is then "sliced-and-diced" in reports. Clear old
data from table, get latest data, then a form allows
user to view pieces of data in reports in multiple ways.

Well...it gave me the same error as you got....
it surprised the heck out of me...
it was only ~53,000 records....

I don't clearly remember all that I tried.

I deleted all indexes.

It had 2 fields that came over as Decimal.

I tried to change those simultaneously and
got same error...

I compacted and repaired, but trying to change
just one field errored out (if I remember right)...

Finally, I started a new blank query, made sure
AutoCorrect was turned off, then imported table.

I changed one Decimal field then saved the change.

I changed second Decimal field then saved the change.

I added autonumber field then saved the change.

I then made that field the primary key and saved change...
success....

Since then the app has been working as expected
during last week or so....
a command button that deletes the table data,
then appends new data from linked view has
worked flawlessly...

I don't know what has happened that I had to be so
deliberate in table changes?????

But, I suggest you try importing your table to new
blank db with AutoCorrect turned off, then (in steps)
add the autonumber field, save change, then make
that field the primary key.

I cannot help think that something has changed
(possibly from latest round of security updates?).
I just don't know....

I changed field name from MONTH to BBMONTH but it makes no difference.
When I tried to insert PPCID as pri key it gives me error "File sharing
lock
count exceeded Increase MaxLocksPerFile registry entry".Tried changing
that
entry in registry from default 9500 to upto 25000 in steps but the error
is
same.

Able to open ppcname in design mode but when try to set BBMONTH as pri key
it
gives error "The changes you requested weren't successful because they
would
create duplicate values in the index,pri key, or relationship"

regds
prasanna

Please explain why you cannot open table
ppcname in design mode, add a field (say "PPCID")
that is Autonumber, then set that field as
the Primary Key....

while you are at it, think about changing field
name from "MONTH" to something that is not
an Access Reserved word...

links from previous Ken Snell post:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

As you see I am having 20 Fields table ppcname.Two of them are USERNAME
and

[quoted text clipped - 76 lines]

ON ppcname.MONTH=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

 

[ppc via AccessMonster.com]

Hi Gary
I done as you said.Added PPCID as pri key but still following is scenario:

I am able to delete all but one duplicate record wit following NOT IN query

DELETE DISTINCTROW ppcname.*
FROM ppcname
WHERE ppcname.PPCID NOT IN (
SELECT First(PPCID) as FirstPPCID
FROM ppcname
GROUP BY ppcname.BBMONTH, ppcname.USERNAME, ppcname.ACCOUNTNO, ppcname.
GrossSendMBytes, ppcname.GrossReceiveMBytes);

I am not able to do the same with following

qkeepthese query :

SELECT First(ppcname.PPCID) AS FirstPPCID, ppcname.BBMONTH, ppcname.USERNAME,
ppcname.ACCOUNTNO, ppcname.GrossSendMBytes, ppcname.GrossReceiveMBytes,
ppcname.TELNO
FROM ppcname
GROUP BY ppcname.BBMONTH, ppcname.USERNAME, ppcname.ACCOUNTNO, ppcname.
GrossSendMBytes, ppcname.GrossReceiveMBytes, ppcname.TELNO;

deletethese:

DELETE DISTINCTROW ppcname.*
FROM ppcname LEFT JOIN qkeepthese ON ppcname.PPCID = qkeepthese.FirstPPCID;

The NOT IN query does the job but is very slow

regds

prasanna

Hmmm.....

Where did this data come from?

The reason I ask is that just last week I
was whipping out a "quick-and-dirty" app
that involved creating a view on SQL Server,
then started with a make-table query from link
to this view.

I have done this so many times before....

After I get the "make-table," I add an Access
"ID" autonumber field, make it the primary key,
then change make-table query to append query.

Many of our apps just need this "snapshot" arrangement
that is then "sliced-and-diced" in reports. Clear old
data from table, get latest data, then a form allows
user to view pieces of data in reports in multiple ways.

Well...it gave me the same error as you got....
it surprised the heck out of me...
it was only ~53,000 records....

I don't clearly remember all that I tried.

I deleted all indexes.

It had 2 fields that came over as Decimal.

I tried to change those simultaneously and
got same error...

I compacted and repaired, but trying to change
just one field errored out (if I remember right)...

Finally, I started a new blank query, made sure
AutoCorrect was turned off, then imported table.

I changed one Decimal field then saved the change.

I changed second Decimal field then saved the change.

I added autonumber field then saved the change.

I then made that field the primary key and saved change...
success....

Since then the app has been working as expected
during last week or so....
a command button that deletes the table data,
then appends new data from linked view has
worked flawlessly...

I don't know what has happened that I had to be so
deliberate in table changes?????

But, I suggest you try importing your table to new
blank db with AutoCorrect turned off, then (in steps)
add the autonumber field, save change, then make
that field the primary key.

I cannot help think that something has changed
(possibly from latest round of security updates?).
I just don't know....

I changed field name from MONTH to BBMONTH but it makes no difference.
When I tried to insert PPCID as pri key it gives me error "File sharing

[quoted text clipped - 39 lines]

 

[Gary Walter]

DELETE DISTINCTROW *
FROM
ppcname LEFT JOIN qkeepthese
ON
ppcname.PPCID = qkeepthese.FirstPPCID
WHERE qkeepthese.FirstPPCID IS NULL;

I done as you said.Added PPCID as pri key but still following is scenario:

I am able to delete all but one duplicate record wit following NOT IN
query

DELETE DISTINCTROW ppcname.*
FROM ppcname
WHERE ppcname.PPCID NOT IN (
SELECT First(PPCID) as FirstPPCID
FROM ppcname
GROUP BY ppcname.BBMONTH, ppcname.USERNAME, ppcname.ACCOUNTNO, ppcname.
GrossSendMBytes, ppcname.GrossReceiveMBytes);

I am not able to do the same with following

qkeepthese query :

SELECT First(ppcname.PPCID) AS FirstPPCID, ppcname.BBMONTH,
ppcname.USERNAME,
ppcname.ACCOUNTNO, ppcname.GrossSendMBytes, ppcname.GrossReceiveMBytes,
ppcname.TELNO
FROM ppcname
GROUP BY ppcname.BBMONTH, ppcname.USERNAME, ppcname.ACCOUNTNO, ppcname.
GrossSendMBytes, ppcname.GrossReceiveMBytes, ppcname.TELNO;

deletethese:

DELETE DISTINCTROW ppcname.*
FROM ppcname LEFT JOIN qkeepthese ON ppcname.PPCID =
qkeepthese.FirstPPCID;

The NOT IN query does the job but is very slow

regds

prasanna

Hmmm.....

Where did this data come from?

The reason I ask is that just last week I
was whipping out a "quick-and-dirty" app
that involved creating a view on SQL Server,
then started with a make-table query from link
to this view.

I have done this so many times before....

After I get the "make-table," I add an Access
"ID" autonumber field, make it the primary key,
then change make-table query to append query.

Many of our apps just need this "snapshot" arrangement
that is then "sliced-and-diced" in reports. Clear old
data from table, get latest data, then a form allows
user to view pieces of data in reports in multiple ways.

Well...it gave me the same error as you got....
it surprised the heck out of me...
it was only ~53,000 records....

I don't clearly remember all that I tried.

I deleted all indexes.

It had 2 fields that came over as Decimal.

I tried to change those simultaneously and
got same error...

I compacted and repaired, but trying to change
just one field errored out (if I remember right)...

Finally, I started a new blank query, made sure
AutoCorrect was turned off, then imported table.

I changed one Decimal field then saved the change.

I changed second Decimal field then saved the change.

I added autonumber field then saved the change.

I then made that field the primary key and saved change...
success....

Since then the app has been working as expected
during last week or so....
a command button that deletes the table data,
then appends new data from linked view has
worked flawlessly...

I don't know what has happened that I had to be so
deliberate in table changes?????

But, I suggest you try importing your table to new
blank db with AutoCorrect turned off, then (in steps)
add the autonumber field, save change, then make
that field the primary key.

I cannot help think that something has changed
(possibly from latest round of security updates?).
I just don't know....

I changed field name from MONTH to BBMONTH but it makes no difference.
When I tried to insert PPCID as pri key it gives me error "File sharing

[quoted text clipped - 39 lines]

ON ppcname.MONTH=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

 

[ppc via AccessMonster.com]

Hi Gary

Corrected the query as said but still I am getting error "Could not delete
from specified tables"

Regds

prasanna

DELETE DISTINCTROW *
FROM
ppcname LEFT JOIN qkeepthese
ON
ppcname.PPCID = qkeepthese.FirstPPCID
WHERE qkeepthese.FirstPPCID IS NULL;

I done as you said.Added PPCID as pri key but still following is scenario:

[quoted text clipped - 105 lines]

 

[Gary Walter]

I was wrong.

Just to recap (if I understand correctly) ....

-- you have a lot of data w/some dup records.
-- "NOT IN" is unbearably slow

for one time only, run following

SELECT ppcname.PPCID
INTO tblDeleteMe
FROM ppcname LEFT JOIN qkeepthese
ON ppcname.PPCID = qkeepthese.FirstOfPPCID
WHERE (((qkeepthese.FirstOfPPCID) Is Null));

If you need to do this on a regular basis, then
(after ran above) change above to append query
and save (say as "qryapptblDeleteMe").

INSERT INTO tblDeleteMe ( PPCID )
SELECT ppcname.PPCID
FROM ppcname LEFT JOIN qkeepthese
ON ppcname.PPCID = qkeepthese.FirstOfPPCID
WHERE (((qkeepthese.FirstOfPPCID) Is Null));


Then use tblDeleteMe in delete query (say "qryDeleteDups").

DELETE DISTINCTROW ppcname.*
FROM ppcname INNER JOIN tblDeleteMe
ON ppcname.PPCID = tblDeleteMe.PPCID;

I know this for sure will work, and be faster.
I can only assume that the Group By got in
the way with earlier proposed solution (just
like you cannot use group by query in update
query). I apologise for sending you down this
dead-end!

If you need to repeat this, command button code
so only a click away follows here:
(be sure set References to include MS DAO)

Private Sub cmdDeleteDups_Click()
On Error GoTo ErrorHandler

Dim db As DAO.Database

Set db = CurrentDb()
db.Execute "DELETE * FROM tblDeleteMe",dbFailOnError
db.Execute "qryapptblDeleteMe", dbFailOnError
db.Execute "qryDeleteDups", dbFailOnError
db.Close

ErrorHandlerExit:
Set dbs = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

This will happen nearly instantaneously compared
to "NOT IN" for large data....

you sound like I probably don't need to say this, but...
please backup data before trying...

Corrected the query as said but still I am getting error "Could not delete
from specified tables"

Regds

prasanna

DELETE DISTINCTROW *
FROM
ppcname LEFT JOIN qkeepthese
ON
ppcname.PPCID = qkeepthese.FirstPPCID
WHERE qkeepthese.FirstPPCID IS NULL;

I done as you said.Added PPCID as pri key but still following is
scenario:

[quoted text clipped - 105 lines]

ON ppcname.MONTH=qKeepThese.FirstMonth
WHERE qKeepThese.FirstMonth Is Null;

 

[Gary Walter]

"Gary Walter" crappily typed:

ErrorHandlerExit:
Set dbs = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

should be

Set db = Nothing

 

[ppc via AccessMonster.com]

Hi Gary

It has worked extremely smooth.Thanks for you great help.Just one thing can I
run the module directly instead of calling it from a Macro with action
"OpenModule".
In my module section I am having correctly compiled module as per the code
given by you.But the Run button on the left side is not activated.

regds,

prasanna

"Gary Walter" crappily typed:

ErrorHandlerExit:
Set dbs = Nothing

[quoted text clipped - 3 lines]

Resume ErrorHandlerExit
End Sub

should be

Set db = Nothing