Delete Empty Rows

[DLee]

I have created a database, initially done in Excel. However, the person who
typed it left quite a number of empty rows between some of the text.

I would like to remove these empty rows without it removing the auto
numbering ie when I delete the row the number sequence is also deleted eg row
1, 2, 3 would become row 1, 3 (if I deleted row 2).

Thanks

 

[Ofer]

create a temp table, append to it all the records that are not null, then
delete the original table, run compact to the MDB so the counter will start
again from 1, and run another query to append all the records from the temp
table to the original table

So Append, Delete, Compact , Append
when you append the records sort it by the counter, and don't forget don't
try to append values to the counter.

And please don't forget to make a back up.

 

[Rick B]

An autonumber field is simply used to create a unique record identifyer.
You should not care if gaps appear. If you do, you are in trouble.... If
you start adding a record, then decide not to and back out, an auto-number
is burned. If you enter a record in error then delete it, and autonumber is
burned.

Autonumbers should not be used or seen by the user (in general). If this
number is an invoice number, customer number, etc. then you should use a
non-autonumber field and use code to increment it.

For more details on this, do a search and read the many many prior posts
that relate to auto-numbers and gaps.

Good Luck,