Unique Number for each row

[General Fear]

I would like assign a number to each row in the table. The number can
be row number. For example, the first record in the table can be number
1.

I can do this thru VBA code. Can I do this via SQL ? If so, how?

...

[pietlinden]

Set the field's type as autonumber at the table level...

[Larry Linson]

<pi********@hotmail.comwrote

Set the field's type as autonumber at the table level...

However, do not expect this to be the equivalent of "Row Number" as that
term is used in some other databases. Autonumbers are for internal use,
especially as surrogate keys, and to be referenced by the foreign key fields
in related tables.

But, by definition, Records in relational DB Tables are _unordered_ and
ordering must be done when the data is retrieved via a Query or SQL
statement.

Larry Linson
Microsoft Access MVP

[General Fear]

Thanks for the reply.

Now is it possible to do this with an update query?

pi********@hotmail.com wrote:

Set the field's type as autonumber at the table level...

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:xO8Eg.55645$zc2.25436@trnddc06:

><pi********@hotmail.comwrote

Set the field's type as autonumber at the table level...

However, do not expect this to be the equivalent of "Row Number"
as that term is used in some other databases. Autonumbers are for
internal use, especially as surrogate keys, and to be referenced
by the foreign key fields in related tables.

It's important to be sure you do *not* think of AutoNumbers as
either identity fields or as record numbers. An AutoNumber is really
just a special kind of default value. Most fields have a default
value field. You could set a date type field to a default value of
Date() so that today's date would be inserted when the record is
created. An AutoNumber is a similar type of default value, but an
incremented AutoNumber is a default vaulue of Max()+1 (it can also
be set to random, which picks a number from the entire range of
possible values storable in a long integer field).

But, by definition, Records in relational DB Tables are
_unordered_ and ordering must be done when the data is retrieved
via a Query or SQL statement.

This is something that people who've never actually studied the
theory behind SQL don't get. Data can have an ordering in the
physical storage, but SQL manipulates that data at a level that
ignores the physical ordering entirely -- all SQL resultsets must be
assumed to be unordered until you've placed an ORDER BY clause on
them. What that means is you can't count on a SQL statement with no
ORDER BY clause returning the data in the same order every time.

Secondly, record numbers make sense *only* when you're always
retrieving or navigating the entire table. In a networked
environment, that's very inefficient, so you'd be retrieving single
records or subsets of records. What good would an absolute record
number in the whole table do you when you're retrieving only one
record or a dozen?

In general, those who demand record numbers are trying to make
Access and SQL work the way the tools they've used in the past
worked (usually xBase-derived programming practices). They need to
get away from those assumptions ASAP.

Also, those who are asking for record numbers are often trying to
process data sequentially, when SQL UPDATES, which operate on sets,
would be much more efficient.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/