Hello,

I'm trying to get together some code that will randomly generate thousands
of test records in a Book Inventory style sample database I'm messing
around with. I'm using the Randomize statement. An Orders table and an
OrderDetails table are populated with the test data. The Orders table gets 
populated OK. Problem is, the primary key of the OrderDetails table is a 
composite key. It consists of an OrderID field from the Orders table, and 
a ISBNNumber field from the Books table. When I run the code, Randomize 
duplicates the primary key eventually, preventing the OrderDetails table 
from being populated. So I removed the primary key, thinking I could 
load the table with data, then go back and delete the duplicate records, 
then reestablish the primary key. But I can't figure out how to 
do this.

I ran a Find Duplicates query in Access and it generated the following
SQL:

SELECT 
  First(tblOrderDetails.OrderID) AS [OrderID Field], 
  First(tblOrderDetails.ISBNNumber) AS [ISBNNumber Field], 
  Count(tblOrderDetails.OrderID) AS NumberOfDups
FROM 
  tblOrderDetails
GROUP BY 
  tblOrderDetails.OrderID, tblOrderDetails.ISBNNumber
HAVING 
  (((Count(tblOrderDetails.OrderID))>1) 
AND 
  ((Count tblOrderDetails.ISBNNumber))>1));


The output looks like this (about 3,000 records):

OrderIDField     ISBNNumberField    NumberOfDups
4                1-55615-583-2      2
14               1-55615-481-X      2
14               1-55615-876-9      2
14               1-55615-894-7      2
etc...

The records are duplicates based on a comparison of both the OrderIDField 
and ISBNNumberField, right? Now I want to delete these 3,000 records, but
the Find Duplicates query generates a read-only recordset. Is that because
of the grouping? 

Can I create a delete query that deletes duplicate records when the 
duplication is based on a two field comparison like this? 

Sorry to run on a bit. Any thoughts appreciated.

Bob,

What you do is create a copy of the table with the duplicates in it, but 
only copy its structure, not the data.  Then on this copy, set the primary 
key as you want it.  Next write an append query to append the data from 
your original table to the copy.  What will happen is that the query will 
append all the unique values to the new table and will refuse to append 
the duplicates due to key violations.  Just accept this error and you get 
what you want.  The new table will only have unique values in it.

Greg

Hi Bob, 

Surprisingly enough, the above answer is the "official" way of doing it as 
well, I had the same problem and found that exact answer on MSDN.  Seems 
kinda 2-stage process of something that seems simple...but what can you do.

Dave

> Bob,

> What you do is create a copy of the table with the duplicates in it, but 
o> nly copy its structure, not the data.  Then on this copy, set the 
primary 
k> ey as you want it.  Next write an append query to append the data from 
y> our original table to the copy.  What will happen is that the query 
will 
a> ppend all the unique values to the new table and will refuse to append 
t> he duplicates due to key violations.  Just accept this error and you 
get 
w> hat you want.  The new table will only have unique values in it.

> Greg

You can write a query with "having max(something) = something" but this way
sounds a lot easier ;)

-----Original Message-----
From: Dave Loukola [mailto:dave.loukola@H...]
Sent: Friday, November 22, 2002 8:22 AM
To: Access
Subject: [access] Re: Deleting duplicate records


Hi Bob, 

Surprisingly enough, the above answer is the "official" way of doing it as 
well, I had the same problem and found that exact answer on MSDN.  Seems 
kinda 2-stage process of something that seems simple...but what can you do.

Dave

> Bob,

> What you do is create a copy of the table with the duplicates in it, but 
o> nly copy its structure, not the data.  Then on this copy, set the 
primary 
k> ey as you want it.  Next write an append query to append the data from 
y> our original table to the copy.  What will happen is that the query 
will 
a> ppend all the unique values to the new table and will refuse to append 
t> he duplicates due to key violations.  Just accept this error and you 
get 
w> hat you want.  The new table will only have unique values in it.

> Greg

Greg, Dave, Dave, et. al.

I am now the proud owner of 100,000+ line item records! Cigars for 
everybody. Ah, life's little triumphs ;). Thought I'd hit the wall on that 
one. Thanks.

Bob

>From: "Gregory Serrano" <SerranoG@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Deleting duplicate records
>Date: Fri, 22 Nov 2002 13:42:26
>
>Bob,
>
>What you do is create a copy of the table with the duplicates in it, but
>only copy its structure, not the data.  Then on this copy, set the primary
>key as you want it.  Next write an append query to append the data from
>your original table to the copy.  What will happen is that the query will
>append all the unique values to the new table and will refuse to append
>the duplicates due to key violations.  Just accept this error and you get
>what you want.  The new table will only have unique values in it.
>
>Greg


_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus