Missing Records

[John Duchowski]

I've got a fairly large Access file with 4899 records. The ID field
runs sequentially but misses two rows: it skips 3727 and 3740.
Consequently, the dbase indicates that I have 4901 rows where in
reality I only have 4899. I've inherited this problem from someone
else who imported an Excel file into Access. I can't delete rows or
renumber the rows to reflect the proper number of entries. I've tried
Help and On-line Help but I can't find the right answer. Can someone
please help me out? Thanks. Regards, John

[Larry Linson]

Sure, I can help you out, John. Don't use AutoNumber fields for any purpose
where a human sees them. If you run a query against that table, down at the
bottom, it will give you the actual number of rows.

AutoNumbers are for internal use, as surrogate keys, to simplify the joining
of related tables. They are not, repeat NOT, to be shown to users nor to
indicate how many records exist in a table.

If you simply want to list the records with a sequential number... do it in
a report, and include a text box with a control source of "=1" and set its
RunningSum property to OverGroup or OverAll as you desire.

Larry Linson
Microsoft Access MVP

"John Duchowski" <jd******@pall.com> wrote in message
news:69**************************@posting.google.c om...

I've got a fairly large Access file with 4899 records. The ID field
runs sequentially but misses two rows: it skips 3727 and 3740.
Consequently, the dbase indicates that I have 4901 rows where in
reality I only have 4899. I've inherited this problem from someone
else who imported an Excel file into Access. I can't delete rows or
renumber the rows to reflect the proper number of entries. I've tried
Help and On-line Help but I can't find the right answer. Can someone
please help me out? Thanks. Regards, John

[John Duchowski]

So what you're telling me is to simply ignore the AutoNumber (AN) field?
You're right, the number at the bottom displays the correct number of
entries. I a newbie here, so pls bear with me :-) - but the end users are
confused and purturbed by the numbers missing from the sequence. Everything
has to jive. So the number in the AN has to agree with the number at the
bottom. Or, as you suggest, I'd be very happy just to prevent the AN field
from displaying but I don't know how to do it...

"Larry Linson" <bo*****@localhost.not> wrote in message
news:fR*****************@nwrddc02.gnilink.net...

Sure, I can help you out, John. Don't use AutoNumber fields for any purpose where a human sees them. If you run a query against that table, down at the bottom, it will give you the actual number of rows.

AutoNumbers are for internal use, as surrogate keys, to simplify the joining of related tables. They are not, repeat NOT, to be shown to users nor to
indicate how many records exist in a table.

If you simply want to list the records with a sequential number... do it in a report, and include a text box with a control source of "=1" and set its
RunningSum property to OverGroup or OverAll as you desire.

Larry Linson
Microsoft Access MVP

"John Duchowski" <jd******@pall.com> wrote in message
news:69**************************@posting.google.c om...

I've got a fairly large Access file with 4899 records. The ID field
runs sequentially but misses two rows: it skips 3727 and 3740.
Consequently, the dbase indicates that I have 4901 rows where in
reality I only have 4899. I've inherited this problem from someone
else who imported an Excel file into Access. I can't delete rows or
renumber the rows to reflect the proper number of entries. I've tried
Help and On-line Help but I can't find the right answer. Can someone
please help me out? Thanks. Regards, John

[Deano]

John Duchowski wrote:

So what you're telling me is to simply ignore the AutoNumber (AN)
field? You're right, the number at the bottom displays the correct
number of entries. I a newbie here, so pls bear with me :-) - but the
end users are confused and purturbed by the numbers missing from the
sequence. Everything has to jive. So the number in the AN has to
agree with the number at the bottom. Or, as you suggest, I'd be very
happy just to prevent the AN field from displaying but I don't know
how to do it...

Larry's advice stands. When making forms to present data to the user
there's no need to bind any control to the AN field. Thus they will not see
it.
If they've got direct access to a table (that might not be a good idea) then
you could just hide the column.