Access 2007

[dawnd3]

I am wondering if there is a way to number records in a table or query, 1,2,1,2 or yes, no, yes, no, something like that? This would be a list of duplicates where I would want to separate out one set to keep and one set to delete. (I have a little procedure that gets rid of dups and updates the related tables.) I can do this through Excel, just wondering if I can save myself a step.

Dawn

 

[jrbarnett]

To number them in a table add a field of type autonumber with an increment of 1.

The number won't get reset if you delete rows, just bear in mind to ignore this field as part of your duplicate checking as no two rows will have the same number, so all will be unique.

John

 

[dawnd3]

I want them to just have a 1 or a 2, no unique numbers.

 

[dhookom]

You need to provide a method that determines which record in the duplicate is numbered 1 and which is 2. Records are like marbles in a bag with no order so you must have some type of primary key field or fields that identify individual records.

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

I don't care which is 1 and which is 2, I just want it to alternate all the way down the column. The records will be sorted so that they are next to their duplicate. I am probably just not thinking clear on this one. I should just stick to the method I have using Excel, LOL.

 

[dhookom]

If you don't have a primary key (or won't create one) that allows you to uniquely identify records, then you might want to stick to Excel.

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

There is a primary key.

 

[dhookom]

Then there is a solution.

If you want assistance, you should provide the table name, significant fields, data types, and primary key field(s).

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya dawnd3 . . .

Lets say you accomplish your alternate 1,2 sequence. [blue]How will you determine wether to delete 1 or 2?[/blue] [green]It is this determination that needs to be incorporated in the WHERE Clause of a Delete query or SQL![/green]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]