How to create "Auto Number" field in a Query

Here this article is to guide you to resolve a problem to towards creating an auto number field inside a query.

Here's given a simple example to create 'Auto number' field in the query, none of the fields has unique values, and I am trying to add a field with 'Auto Number'.

Here created a table with two fields and none of them is having unique key. Fig: - 1.1 shows the table.

Fig:-1.1- Design view of table


Here a query is created; the query is using the same table with two aliases. There is not any unique key defined in the table. As shown in Fig:- 1.2.

Fig:-1.2- Solution Query created


The above query will generate the following result, shown in Fig: - 1.3. The first generated is an Auto number field.

Fig:-1.3- Query Result


SQL for the above Query is given below:-

SQL QUERY: SELECT Count (E1.emp_name) AS Rank, E1.emp_name
FROM emp AS E1, emp AS E2
WHERE ((([E1].[e_id]+[E1].[emp_name])>=[E2].[e_id]+[E2].[emp_name]))
GROUP BY E1.emp_name;


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.