Eliminating Duplicate Records Via Summing Them

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I brought some 11,000 records in from Excel and for some reason about 5,000
records repeat, once each. So I have a mix of duplicates in sets of two and
some single entry records in my table.

I have seen (or think I've seen) peole run Sort or Add queries such that the
duplicate record is converted to a single entry and in a column to the right,
the number of occurances is provided.

Example: Table Data

ABC123
ABC123
ABZ123
ABZ123
RRR111
SSS333
SSS333

Gets converted to:

ABC123 2
ABZ123 2
RRR111 1
SSS333 2

Can you please tell me how this is done in Office 97?

Thanks.
 
Create a new query, and select your table.

Add the ID field you're showing to the grid twice.

Convert the query to a Totals query by going under the View menu and
selecting Totals or by clicking on the Sigma button on the button bar.

A new row "Total:" will appear on the grid, set to "Group By" for each
column. Change one of the two to "Count"

The SQL for the query will look something like:

SELECT Field1, Countr(Field1)
FROM Table1
GROUP BY Field1
 
Back
Top