Cannot make delete duplicates query work

[David Vollmer]

I have a table with over 100,000 records. Only 26,740 are unique, meaning
that their customer number and customer name are the same. The reason they
are duplicated is that many have multiple account numbers. I have copied all
of the customer numbers and all of the account numbers and put them in a
separate table along with an autonumber key field.

Now I need to delete every duplicated customer and then make the customer
number field a key field that will relate to the account number table. I have
tried to follow the "Find, eliminate, or hide duplicate records in Access"
training method but to no avail.

The SQL of the "Distinct records" query is:

SELECT tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full],
First(tblPrivateClientPortfolios.CustID) AS FirstOfCustID
FROM tblPrivateClientPortfolios
GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full]
ORDER BY tblPrivateClientPortfolios.[M Name Full];

The SQL of the "Delete query" is:

DELETE tblPrivateClientPortfolios.*, distinct.[Rlof Cust Num],
distinct.FirstOfCustID
FROM tblPrivateClientPortfolios INNER JOIN [qryCustomers-DistinctRecords] AS
[distinct] ON tblPrivateClientPortfolios.[Rlof Cust Num] = distinct.[Rlof
Cust Num]
WHERE (((distinct.[Rlof Cust Num])=[distinct].[Rlof Cust Num]) AND
((distinct.FirstOfCustID)<>[distinct].[FirstOfCustID]));

I have tried variation of the above but as written when I preview the
records that will be deleted I get no records as a result. I should be
getting around 75,000 records that will be deleted.

What am I doing wrong?

Thank you,
David Vollmer

 

[Jeff Boyce]

David

Another approach might be to create a new (empty) table pattered after your
"customer" table, with a unique index set on the fields that contain
"duplicate" information.

Then you could create an append query, from your existing table to this new
table. Because of the unique index on the new table, "duplicates" in the
existing table will not be written.

Of course, if the data contained in the non-unique fields is different, how
would you decide which of the duplicate records holds the correct additional
data?

Before doing any of this, backup!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David Vollmer]

Jeff,

Thank you for your quick reply. Can I use the query I did that found the
distinct records and append those records to the new table? Of course I would
need the other fields of data that are not now contained in that query.

Which of the duplicates is the one that is appended to the new table is not
important as I have a separate table with the other account numbers tied to
the customer numbers.

The other information in the duplicate records is not necessary for my
purposes.

David

David

Another approach might be to create a new (empty) table pattered after your
"customer" table, with a unique index set on the fields that contain
"duplicate" information.

Then you could create an append query, from your existing table to this new
table. Because of the unique index on the new table, "duplicates" in the
existing table will not be written.

Of course, if the data contained in the non-unique fields is different, how
would you decide which of the duplicate records holds the correct additional
data?

Before doing any of this, backup!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with over 100,000 records. Only 26,740 are unique, meaning
that their customer number and customer name are the same. The reason they
are duplicated is that many have multiple account numbers. I have copied
all
of the customer numbers and all of the account numbers and put them in a
separate table along with an autonumber key field.

Now I need to delete every duplicated customer and then make the customer
number field a key field that will relate to the account number table. I
have
tried to follow the "Find, eliminate, or hide duplicate records in Access"
training method but to no avail.

The SQL of the "Distinct records" query is:

SELECT tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full],
First(tblPrivateClientPortfolios.CustID) AS FirstOfCustID
FROM tblPrivateClientPortfolios
GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full]
ORDER BY tblPrivateClientPortfolios.[M Name Full];

The SQL of the "Delete query" is:

DELETE tblPrivateClientPortfolios.*, distinct.[Rlof Cust Num],
distinct.FirstOfCustID
FROM tblPrivateClientPortfolios INNER JOIN [qryCustomers-DistinctRecords]
AS
[distinct] ON tblPrivateClientPortfolios.[Rlof Cust Num] = distinct.[Rlof
Cust Num]
WHERE (((distinct.[Rlof Cust Num])=[distinct].[Rlof Cust Num]) AND
((distinct.FirstOfCustID)<>[distinct].[FirstOfCustID]));

I have tried variation of the above but as written when I preview the
records that will be deleted I get no records as a result. I should be
getting around 75,000 records that will be deleted.

What am I doing wrong?

Thank you,
David Vollmer

 

[David Vollmer]

Jeff,

I created an append query but don't know how to write the criteria to only
append unique customer numbers.

Thank you,
David

David

Another approach might be to create a new (empty) table pattered after your
"customer" table, with a unique index set on the fields that contain
"duplicate" information.

Then you could create an append query, from your existing table to this new
table. Because of the unique index on the new table, "duplicates" in the
existing table will not be written.

Of course, if the data contained in the non-unique fields is different, how
would you decide which of the duplicate records holds the correct additional
data?

Before doing any of this, backup!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with over 100,000 records. Only 26,740 are unique, meaning
that their customer number and customer name are the same. The reason they
are duplicated is that many have multiple account numbers. I have copied
all
of the customer numbers and all of the account numbers and put them in a
separate table along with an autonumber key field.

Now I need to delete every duplicated customer and then make the customer
number field a key field that will relate to the account number table. I
have
tried to follow the "Find, eliminate, or hide duplicate records in Access"
training method but to no avail.

The SQL of the "Distinct records" query is:

SELECT tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full],
First(tblPrivateClientPortfolios.CustID) AS FirstOfCustID
FROM tblPrivateClientPortfolios
GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full]
ORDER BY tblPrivateClientPortfolios.[M Name Full];

The SQL of the "Delete query" is:

DELETE tblPrivateClientPortfolios.*, distinct.[Rlof Cust Num],
distinct.FirstOfCustID
FROM tblPrivateClientPortfolios INNER JOIN [qryCustomers-DistinctRecords]
AS
[distinct] ON tblPrivateClientPortfolios.[Rlof Cust Num] = distinct.[Rlof
Cust Num]
WHERE (((distinct.[Rlof Cust Num])=[distinct].[Rlof Cust Num]) AND
((distinct.FirstOfCustID)<>[distinct].[FirstOfCustID]));

I have tried variation of the above but as written when I preview the
records that will be deleted I get no records as a result. I should be
getting around 75,000 records that will be deleted.

What am I doing wrong?

Thank you,
David Vollmer

 

[John Spencer]

ON a copy of your data, try the following.

DELETE DISTINCTROW PC.*
FROM tblPrivateClientPortfolios AS PC INNER JOIN
[qryCustomers-DistinctRecords] AS D
ON PC.[Rlof Cust Num] = D.[Rlof Cust Num]
WHERE PC.CustID<>[D].[FirstOfCustID]

I would avoid using Distinct since that has a specific meaning in SQL
queries.
Your posted query was checking the value of FirstOfCustId against itself and
saying if it doesn't match itself. That condition would never be true
(except with nulls, you might get something back - I don't know for sure at
this point, but I would expect null to be returned).

 

[Turin]

Hi David,
Here is what Jeff left out;

to delete duplicate records:
right click the table that contains the duplicates
select copy
right click the tables area and select paste
select structure only
click OK
click design view of the new table
in the key field, change the key to "indexed (no duplicates)"
save it
create an append query:
under objects select queries
click new
click design view
select the table that contains the duplicates
add all the fields by dragging the asterisk to the first column
from the menu bar select query
select append query
from the query desin bar select the exclamation point(run)
when it is finished you will get an error msg stating that it could not
append certain records due to key violations, click yes to continue
the msg "you are about to append XXX records..." click yes

now you will have a duplicate free table. delete the old table that contains
the duplicates, rename the duplicate free table to the old table name.

The only easy day was yesterday!

Turin

Jeff,

I created an append query but don't know how to write the criteria to only
append unique customer numbers.

Thank you,
David

David

Another approach might be to create a new (empty) table pattered after your
"customer" table, with a unique index set on the fields that contain
"duplicate" information.

Then you could create an append query, from your existing table to this new
table. Because of the unique index on the new table, "duplicates" in the
existing table will not be written.

Of course, if the data contained in the non-unique fields is different, how
would you decide which of the duplicate records holds the correct additional
data?

Before doing any of this, backup!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with over 100,000 records. Only 26,740 are unique, meaning
that their customer number and customer name are the same. The reason they
are duplicated is that many have multiple account numbers. I have copied
all
of the customer numbers and all of the account numbers and put them in a
separate table along with an autonumber key field.

Now I need to delete every duplicated customer and then make the customer
number field a key field that will relate to the account number table. I
have
tried to follow the "Find, eliminate, or hide duplicate records in Access"
training method but to no avail.

The SQL of the "Distinct records" query is:

SELECT tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full],
First(tblPrivateClientPortfolios.CustID) AS FirstOfCustID
FROM tblPrivateClientPortfolios
GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full]
ORDER BY tblPrivateClientPortfolios.[M Name Full];

The SQL of the "Delete query" is:

DELETE tblPrivateClientPortfolios.*, distinct.[Rlof Cust Num],
distinct.FirstOfCustID
FROM tblPrivateClientPortfolios INNER JOIN [qryCustomers-DistinctRecords]
AS
[distinct] ON tblPrivateClientPortfolios.[Rlof Cust Num] = distinct.[Rlof
Cust Num]
WHERE (((distinct.[Rlof Cust Num])=[distinct].[Rlof Cust Num]) AND
((distinct.FirstOfCustID)<>[distinct].[FirstOfCustID]));

I have tried variation of the above but as written when I preview the
records that will be deleted I get no records as a result. I should be
getting around 75,000 records that will be deleted.

What am I doing wrong?

Thank you,
David Vollmer

 

[David Vollmer]

Turin and John,

Thank you your suggestions. I tried Turin's first and it was what I needed.
I now have my 26,740 records separated from the 100,544, and have them linked
to their 100,544 account numbers.

I don't know where I would be without people like you three to help me.
Since I have had some experience with Access some years ago, they assigned me
the task of converting 7 different Excel workbooks into relational databases.

I will, I am sure, be back with more questions!!

Thanks again,
David Vollmer

Hi David,
Here is what Jeff left out;

to delete duplicate records:
right click the table that contains the duplicates
select copy
right click the tables area and select paste
select structure only
click OK
click design view of the new table
in the key field, change the key to "indexed (no duplicates)"
save it
create an append query:
under objects select queries
click new
click design view
select the table that contains the duplicates
add all the fields by dragging the asterisk to the first column
from the menu bar select query
select append query
from the query desin bar select the exclamation point(run)
when it is finished you will get an error msg stating that it could not
append certain records due to key violations, click yes to continue
the msg "you are about to append XXX records..." click yes

now you will have a duplicate free table. delete the old table that contains
the duplicates, rename the duplicate free table to the old table name.

The only easy day was yesterday!

Turin

Jeff,

I created an append query but don't know how to write the criteria to only
append unique customer numbers.

Thank you,
David

David

Another approach might be to create a new (empty) table pattered after your
"customer" table, with a unique index set on the fields that contain
"duplicate" information.

Then you could create an append query, from your existing table to this new
table. Because of the unique index on the new table, "duplicates" in the
existing table will not be written.

Of course, if the data contained in the non-unique fields is different, how
would you decide which of the duplicate records holds the correct additional
data?

Before doing any of this, backup!

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a table with over 100,000 records. Only 26,740 are unique, meaning
that their customer number and customer name are the same. The reason they
are duplicated is that many have multiple account numbers. I have copied
all
of the customer numbers and all of the account numbers and put them in a
separate table along with an autonumber key field.

Now I need to delete every duplicated customer and then make the customer
number field a key field that will relate to the account number table. I
have
tried to follow the "Find, eliminate, or hide duplicate records in Access"
training method but to no avail.

The SQL of the "Distinct records" query is:

SELECT tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full],
First(tblPrivateClientPortfolios.CustID) AS FirstOfCustID
FROM tblPrivateClientPortfolios
GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num],
tblPrivateClientPortfolios.[M Name Full]
ORDER BY tblPrivateClientPortfolios.[M Name Full];

The SQL of the "Delete query" is:

DELETE tblPrivateClientPortfolios.*, distinct.[Rlof Cust Num],
distinct.FirstOfCustID
FROM tblPrivateClientPortfolios INNER JOIN [qryCustomers-DistinctRecords]
AS
[distinct] ON tblPrivateClientPortfolios.[Rlof Cust Num] = distinct.[Rlof
Cust Num]
WHERE (((distinct.[Rlof Cust Num])=[distinct].[Rlof Cust Num]) AND
((distinct.FirstOfCustID)<>[distinct].[FirstOfCustID]));

I have tried variation of the above but as written when I preview the
records that will be deleted I get no records as a result. I should be
getting around 75,000 records that will be deleted.

What am I doing wrong?

Thank you,
David Vollmer