Return every other row?

[Clif McIrvin]

Working on cleaning up existing data where duplicated records exist in
production tables.

A2003.
Select query with order by clause which returns duplicate records from
one table.

I'd like a query that returns every other row from the above query. Is
such a thing possible without VBA?

Even better would be a query that returns all but one row from each set
of duplicates, as I have found cases with multiple duplicates. (Not
strictly necessary, because deleting alternate rows from the original
query in successive iterations would always end up with the correct
result after n-1 iterations, where n is the number of duplicates in the
largest set.)

The factors contributing to the generation of these duplicates are still
being identified (and data integrity rules defined) so this will be a
recurring process.

 

[Douglas J. Steele]

As far as I'm aware, it cannot be done through a SQL statement, at least not
efficiently.

Have you tried putting DISTINCT after the SELECT to eliminate duplicates?

 

[mscertified]

I'd do a SELECT DISTINCT and then in a separate query do a DELETE FROM...
WHERE... NOT IN (First Query)
It could be very slow if the tables are large.

-Dorian.

 

[John Spencer]

Depends on how you identify duplicate records - All fields the same?.
All fields except the primary key? Some fields the same others
different but doesn't matter? Does the table have a primary key?

How many fields are involved in determining the duplication? If ten or
less and there is no primary key, the easiest solution might be to build
a NEW table with the same structure as the existing table.

Build a compound primary key by selecting the ten fields at once and
making the ten fields the primary key.

Then build an append query to add all the records from the source table
into the destination table.
Run the append query

Ignore the error message about duplicate primary keys and append the
records - this should keep one of the duplicate records and drop all the
others.

Rename the destination table to the name of the source table


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Douglas J. Steele]

Sorry, but that makes no sense.

If SELECT DISTINCT only returns a subset of the rows, then they truly are
duplicates, and WHERE NOT IN (...) won't do anything.

 

[Clif McIrvin]

Thanks, John. I identified the duplicates by using the Find Duplicates
query wizard, and then modifying the SQL to add the remaining fields (13
total) -- so sounds like with a bit of work to get within the 10 field
limit I can use this idea. Since I have guaranteed EXACT duplicates this
should be a fail safe procedure.

 

[Clif McIrvin]

As far as I'm aware, it cannot be done through a SQL statement, at
least not efficiently.

Have you tried putting DISTINCT after the SELECT to eliminate
duplicates?

Thanks, Doug -- I was focusing on *returning* duplicates and never even
considered trying DISTINCT.

I'll see what I can do with it.

 

[John Spencer]

If you post the query you used to find duplicates and tell us the
primary key of the table, there may be a better solution. However, if
you develop the solution on your own you will more likely remember how
to do it and enhance develop your skill set and understanding of Access.

Good Luck and if you get stuck post back.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Clif McIrvin]

If you post the query you used to find duplicates and tell us the
primary key of the table, there may be a better solution. However, if
you develop the solution on your own you will more likely remember how
to do it and enhance develop your skill set and understanding of
Access.

Good Luck and if you get stuck post back.

Advice noted.

It's going to be a few days before I'm able to get back to this task.

Thanks for helping!