Auto number field and sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of Names & addresses etc. a want a numbers field to keep count
of the number of rows. I sort the "last name" field "A to Z". Then when I
create the auto number field, the sorting of the names reverts back to the
default order. When I sort the names A to Z again, of course this throws off
my number count of the rows. Whats the best way to do this. I am fairly new
and have access2007 Please help

Bob Levin
 
I have a table of Names & addresses etc. a want a numbers field to keep count
of the number of rows.
Click to expand...

No. You DON'T want to store counts in your table! That's what Queries are for.

Storing a count in your table not only throws off the count when you sort the
data; it really messes you up if you add a name in the middle of the list,
delete a name, or *anything*.

Counts of records can be generated any time you wish, by using a Query, by
using a report with a textbox =Count(*) in the report header or footer, by
using the DCount() function, etc. An autonumber is not a count; its ONLY
function is to provide a meaningless, guaranteed unique identifier.


John W. Vinson [MVP]
 
An Autonumber field should not be used for counting records. It is used as
the Primary key field of a table and should be invisible to users.

One reason for this is that you cannot rely on it being sequential. For
example, if your last rcord was 10 and you create a new record, it may be 11
but if you subsequently delete it, the next record will be 12 (11 is gone
forever) so you may have 11 records but the highest autonumber is 12. As the
database gets used your record count will become way out of sync with the
autonumber field.

If you want to display the count of records in your form, you could use a
DCount() function or check the RecordCount of your form's Recordset.

Steve
 
An Autonumber field should not be used for counting records. It is used as
the Primary key field of a table and should be invisible to users.

One reason for this is that you cannot rely on it being sequential. For
example, if your last rcord was 10 and you create a new record, it may be 11
but if you subsequently delete it, the next record will be 12 (11 is gone
forever) so you may have 11 records but the highest autonumber is 12. As the
database gets used your record count will become way out of sync with the
autonumber field.

If you want to display the count of records in your form, you could use a
DCount() function or check the RecordCount of your form's Recordset.

Steve
 
Back
Top