?
Solved

Create Autonumber column in query

Posted on 2005-05-11
10
Medium Priority
?
529 Views
Last Modified: 2008-03-17
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?
0
Comment
Question by:cherryjari
  • 4
  • 3
  • 3
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 13981455
SELECT test.Name, test.DDate, test.Qty,dCount("*",test) as AutoNumber
FROM test;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13981481
Sorry, I was too quick.  That will give you the recordcount in each record.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13981485
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:cherryjari
ID: 13981545
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.  
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 375 total points
ID: 13981598
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.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 225 total points
ID: 13981652
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.name & test.DDate <= a.Name & a.DDate) AS myCount FROM test;

If this gives incorrect results, try reversing the WHERE clause.
0
 
LVL 1

Author Comment

by:cherryjari
ID: 13981666
Can you tell me how can I create a delete query?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13981686
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.
-Jim
0
 
LVL 1

Author Comment

by:cherryjari
ID: 13981796
GRayL, the qurey doesn't work, so I'll have to go with Jim.  Thanks both of u!!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 13982021
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.  
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question