Auto Number On Running Query

[Jay]

Thanks in advance.

MyTable has a 'start' and 'stop' field.
I need to write a query to append a third column (ID).
ID should be auto-number.

How can I?
SELECT start, stop, ID???
FROM MyTable

 

[John W. Vinson]

Thanks in advance.

MyTable has a 'start' and 'stop' field.
I need to write a query to append a third column (ID).
ID should be auto-number.

How can I?
SELECT start, stop, ID???
FROM MyTable

You'll have to explain. An Autonumber isn't a datatype you can use in a query;
it's a permanent, static unique value in a Table.

Where do you want this ID stored? What does it have to do with the start and
stop?

 

[Jay]

Thank you, John

All I want is just add any unique number in each row when I run query (make
table query).
Start and stop are arbitray columns.

 

[John W. Vinson]

Thank you, John

All I want is just add any unique number in each row when I run query (make
table query).
Start and stop are arbitray columns.

MakeTable queries are very, very rare in well designed Access applications.
There is almost nothing that you can do with a new table which cannot also be
done with a select query. Could you explain what you are trying to
*ACCOMPLISH*?

Either with a make-table or a select query, you can probably get what you want
with the aid of a little auxiliary table. I use a table named Num with one
long integer field N, filled with values from 1 to however many will be needed
(be generous; a 10000 row table is TINY). Depending on how your select query
works you should be able to use Num in conjunction with it to sequentally
number the records, if that's what's needed.

 

[Jay]

Thank you so much, John.

Thsi is my table.
Name Job_Number
John 109998
Tom 198967
Mary 198967

I want to have a query that adds ID field as follows.
Name Job_Number ID
John 109998 1
Tom 198967 2
Mary 198967 3

The reason why I am doing this is because my table unfortunately does not
have any good primary key.

Thanks

 

[John W. Vinson]

Thank you so much, John.

Thsi is my table.
Name Job_Number
John 109998
Tom 198967
Mary 198967

I want to have a query that adds ID field as follows.
Name Job_Number ID
John 109998 1
Tom 198967 2
Mary 198967 3

The reason why I am doing this is because my table unfortunately does not
have any good primary key.

Then your best bet is to create a new, empty table with fields for FirstName
(Name is a reserved word and will cause problems if used as a fieldname) and
JobNumber; include an Autonumber ID field. Create an Append query selecting
the records from your current table and append the existning records (sorted
however you want it done).

A couple of concerns: you really should have a People table with a unique
PersonID, LastName, FirstName, etc. and any linked table should have just the
PersonID. Names (particularly just first names!) are NOT unique, or stable for
that matter (people do change their names after all). Similarly, you should
have (and perhaps do have) a Jobs table with Job_Number as the primary key.