Can I add a sequence / autonumber field to my Make Table Query?

[KevinC]

Hi All,

I have a database with a table and a number of fields within it.

Every now and then I need to query the table and produce a subset of
the data.

I will often need a sequence number within the table (this does not
have to relate to rank and there is no preference as to what row
should be number 1 to n). At the moment:

- I run a make table query based on my criteria.
- Open the new table in design view.
- Add a new field called "ID" and give it an AutoNumber data type.

I would like to know if there is a way I can automatically add the
auto-number value in the original query so that I don't have to keep
going through the other steps!

Any help would be much appreciated.

Regards,

Kevin

 

[John Spencer]

If
-- the make table always creates the same fields
and
-- you don't care if the sequence starts with 1 or some other number then

Use a query to delete all the records in the table
Use a query to append the records instead of using a make table to
create the table.

Another solution would be to build the table with NO records and then
use VBA to copy the empty table and then populate the copy. That way
your autonumber field should always start with one.

All that said, there is seldom a good reason to use make table or to
append a set of records from one table to another. I don't know why you
are doing this, so I don't know if your reason(s) is good or not.

I have one case where I create a new table from existing data. It
involves a lot of complex analysis and takes 3 to 5 minutes to create
the table which is then used as a static source for reports. The
individual reports are almost instantaneous since they don't have to
redo the calculations, combining, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================