Create Autonumber column in query

I need to create autonumber column in the query.  I don't have any ID in the query.  

SELECT test.Name, test.DDate, test.Qty
FROM test;

Name      DDate      Qty
xxx      1/2/2005      1
xxx      2/5/2005      3
xxx      3/4/2005      2
yyy      1/3/2005      2
yyy      1/5/2005      3
yyy      2/6/2005      4
yyy      3/4/2005      1
zzz      1/6/2005      2
zzz      3/5/2005      1
zzz      5/8/2005      1

Is there an easy way to create unique ID or autonumber in the query?
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Perhaps an easier way would be to create a table with the fields you want + the AutoNumber field, run a delete query to delete all records from it, then run an append query to populate it.  The AutoNumber field will generate automatically.
SELECT test.Name, test.DDate, test.Qty,dCount("*",test) as AutoNumber
FROM test;
Sorry, I was too quick.  That will give you the recordcount in each record.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I tried the above, and for the AutoNumber field it returned the table.recordcount for every record.

Just out of curiosity, if this is in a query, therefore not in a table, what good does this do you?
cherryjariAuthor Commented:
This is only the example table.  I have many tables and queries (with calculation).  I combined all of them to 1 query.  Now I would like to create a unique key for each record.  
GRayLConnect With a Mentor Commented:
If the query is always ordered by Name and DDate and there is never a duplicate of that combination try:

SELECT test.Name, test.DDate, test.Qty, (SELECT Count(*) FROM test a WHERE & test.DDate <= a.Name & a.DDate) AS myCount FROM test;

If this gives incorrect results, try reversing the WHERE clause.
cherryjariAuthor Commented:
Can you tell me how can I create a delete query?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SQL: DELETE * FROM YourTableName

(1)  In the Database Window click on the query tab, then New button.
(2)  In Query Design, in the Add Tables dialog, click on the table in which you want to delete records, and hit Ok.
(3)  In the View toolbar button, click once on the down-arrow to the right, and change query type to Delete.
(4)  Double-click on the * in the table grid, and it will appear below.
(5)  Save the query, and give it a name.

Warning:  Executing this will delete ALL records from your table.  

You can also include criteria (i.e. a WHERE clause) so it deletes only certain records.

Hope this helps.
cherryjariAuthor Commented:
GRayL, the qurey doesn't work, so I'll have to go with Jim.  Thanks both of u!!
I just checked and it worked on my test data. As I had written it, it gave me a reverse order so I reversed the WHERE clause and it provided the correct count order.  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.