If you write an append query that adds a record to a table that has an
autonumber field, the you could say that the query has generated an
autonumber field. Just set the other fields and let Access generate the key
for you.
If you know that, and that is not what you want, you can generate your own
unique sequential number by using the DMAX function and adding 1. If you are
generating a key that will be used by the users, such as an invoice number,
that is the approach that you should use.
I have no idea if this would work (never tried), but if you create a
function with static variables and increment a counter each time the
function is called, calling that function from the query and returning the
counter may give you what want.
You'd have to reset the counter before calling the query and possibly it
would get messed up if you requeried/filtered etc.
Definitely with SQL server:
Create a table variable in a stored procedure with an ID column
(autonumber), insert your records there and then select them etc. Not very
efficient though.
I don't think that David meant using a query to add the autonumber to your
table, but you can with a Data Definition Language query. Open an SQL window
in a new query and type in:
ALTER TABLE MyTableName ADD COLUMN ID COUNTER;
change MyTableName and ID to your names. This willl add an autonumber
column to a table using SQL in a query. You can also do it with DAO, or
through the interface with the table design view. I think David meant to
build an empty table which matches your query output, then append the query
output to it. That's OK for a temporary table, but will junk up your
database in no time if you do it regularly without deleting the temp tables
each time.
It would help if you told us exactly what you are trying to accomplish.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.
