How to count the number of duplicate records then delete

[Guest]

I would like to know how i can get a query/macro to enable me to count the
number of duplicate records in one table and put one copy of the duplicated
information into another table along with the total number of duplicated
records
I realise it would have something to do with make table query but can't seem
to find any information about adding the number of duplicate records found.

Many Thanks

 

[Tom Ellison]

Dear Yel:

One would think the term "duplicate record" would be unambiguous. I have
found that for many, it is not.

By "duplicate record" do you mean that there are two or more rows in which
every column is identical?

Now, if row X is a duplicate of row Y, is not then row Y a duplicate of row
X? The relationship is reflexive!

Now, if the table contains 4 columns, ColumnA, ColumnB, ColumnC, and
ColumnD, then you can summarize the dujplication:

SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM YourTable
GROUP BY ColumnA, ColumnB, ColumnC, ColumnD

The only information lost is how many copies of that information there were
beforehand. To add that:

SELECT ColumnA, ColumnB, ColumnC, ColumnD,
COUNT(*) AS DupCount
FROM YourTable
GROUP BY ColumnA, ColumnB, ColumnC, ColumnD

I do not recommend a make table query. Create the destination table
deliberately, keeping control of all the columns it will have, and append
the data instead. Just change the above query to an append query and you
have it.

Tom Ellison

 

[Guest]

Dear Tom,

Yes, in my table i have repeated row data that is exactly the same in every
column across the board.
ie
col 1 - type, col 2 - size, col 3 - value
and in the rows under these columns there is multiple repeated data. this
data comes from a text file import for a bill of materials. im using access
to store and count the number of times that i require.

Ill have a try of your query code and see how that goes

Thanks again
yelhsa