Re: Cannot make delete duplicates query work

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Jeff Boyce" wrote:

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" <DavidVollmer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E5ACCE7F-C811-4107-AD9D-646FEEB48B35@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... If there's a duplicate, ... I used the query wizard to create a "find duplicates" query, now I need to adjust the results of that query to add an occurence counter/number, and I'd be set. ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... I was surprised to see duplicate entries in some of the static tables for dropdown selections, so I have to solve this one now. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: Cannot make delete duplicates query work
    ... would you decide which of the duplicate records holds the correct additional ... that their customer number and customer name are the same. ... GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num], ...
    (microsoft.public.access.queries)
  • Re: Unique Values query
    ... I've got round it by exporting then importing to another table and running ... an append query onto a copied structure only table with primary key set for ... >> This almost gives me the result I want however I am getting duplicate ... >> would I rectfy this so I can get one result for the customer? ...
    (microsoft.public.access.queries)
  • Re: Cannot make delete duplicates query work
    ... append unique customer numbers. ... Then you could create an append query, from your existing table to this new ... would you decide which of the duplicate records holds the correct additional ... GROUP BY tblPrivateClientPortfolios.[Rlof Cust Num], ...
    (microsoft.public.access.queries)