Deleting Odd Numbered Rows in Query

[pshu]

If I do not have the row# as one of the columns, how do I remove the odd (or even) numbered rows of the query?

thanks

 

[ajetrumpet]

you should probably add an autonumber to the source table and then use that as the criteria for deletion

 

[Atomic Shrimp]

Dunno - there's no guarantee that an autonumber will yield a continuous sequence, or that the sequence will run in the order you want to present your query results - in either case, you could then end up removing multiple consecutive rows from the query results and leaving other sets of consecutive rows intact.

 

[ajetrumpet]

Dunno - there's no guarantee that an autonumber will yield a continuous sequence, or that the sequence will run in the order you want to present your query results - in either case, you could then end up removing multiple consecutive rows from the query results and leaving other sets of consecutive rows intact.

it'll work everytime if you do it right before you run the query. you cant obviously leave the autonumber in the table for any longer than that tho

 

[Atomic Shrimp]

agreed, it should create a continuous sequence in the table, in most cases if you add it to a populated table (although I bet there are still some exotic circumstances in which it might fail). But still, there's no way to guarantee that the autonumber will apply itself to the table in a way that is usefully consecutive within a query based on that table, given that the query may select only a subset of records, and may impose a different order upon them.

 

[Atomic Shrimp]

I think the way to do this is:

Find some way of consecutively numbering the results in a query - such as this one (that I haven't tried):
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20492559.html

Run another query based on the results of the one that imposes the numbering, testing the numbered field for oddness.

 

[DCrake]

However the most intruiging question is why? What are you trying to achieve by this?

David

 

[Atomic Shrimp]

My bet is fair division of work between two teams or individuals.

In which case... what happens when a third team/individual enters the scene?

 

[DCrake]

Yeh but you would not want to actually delete them would you? If it were me doing this exercise I would have a hidden listbox on a form that was populated by the recordset/query then enumerate through the list and flag every other one as being odd or even then get the delete button to delete all from table that appearing in the odd/even list.

David

 

[Atomic Shrimp]

I agree - I'm guessing that 'delete' means in this context 'do not show in the results'.

The big danger with anything like this is that as the table contents change over time the items included in or excluded from the list will probably be different each time.

 

[DCrake]

I totally agree with you that is why I am awaiting a response from the poster as to the methodology and logic behind the question.

It's a bit like "I want to walk barefoot across a lot of red hot coals, how do I do it?"

Because it's there?
For a bet?
Personal achievement?

David

 

[Atomic Shrimp]

It's a bit like "I want to walk barefoot across a lot of red hot coals, how do I do it?"

Yes, or "I've decided to drill a hole in my head, will a battery powered drill be OK?".

 

[DCrake]

Only if you use a masonary drill bit.