create autonumber field in select query

[alhanna]

Hi,

I have seen the posting for ID counter when creating a table. How do I create a new autonumber field in a select query?
My original table has a list of all cities. I just need a new field with the autonumber.

I have tried all combos of ID counter, but I can't define field type in a SELECT query.

Results should be:

pkey city

1 houston
2 new york
3 boston
4 etc.

Using Access 97...

 

[alhanna]

OK, slight clarification.

I have a few tables I am joining with a select into statement from the query builder. I just want the results in a new table with an additional autonumber field populated called pkey.

 

[dhookom]

You can't create an autonumber field with a maketable query. You can add an autonumber field from an existing table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

You can, however, PROGRAMATIQACLLY Create a table in code which includes an Autonumber field and the fields from your Select Into (a.k.a. Make Table) query and then change the Select Into query to either an Update or Append Query.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[alhanna]

thanks for suggestions, made me think about it. I figured out I really only need to number the rows. I just created a function that I call to add row numbers after table creation.