Access: How can I generate an auto number for each record in a ta.

[TerryGB]

I have a make table query and wish to number the records generated with an
autonumber field. The query is based on filtered results from another table,
when I tried adding a count function to the field I wish to record the
autonumber in Access responded indicating the field would not be valid based
on the fact that criteria is defined in another field of the query. Any ideas?

 

[Jeff Boyce]

First, if you can generate the records with a query, are you certain you
need a table?

Second, instead of a Make Table query, another approach would be to define
the new table (running Make Table once can help, but you'll want to go in
and modify it, at least to include the Autonumber), then use Delete and
Append queries to re-load it.

Finally, the Access Autonumber is designed for use as a unique row
identifier. Since you are creating these new records, unless you are
planning to subsequently refer to the rows in other tables, you might not
need an Autonumber/primary key (oops?! you didn't say you were trying to
create a primary key -- are you?).

Good luck

Jeff Boyce
<Access MVP>

I have a make table query and wish to number the records generated with an
autonumber field. The query is based on filtered results from another table,
when I tried adding a count function to the field I wish to record the
autonumber in Access responded indicating the field would not be valid based
on the fact that criteria is defined in another field of the query. Any

ideas?