counting like autonumber

[qaccess]

how can i create a row in a query that acts the same as autonumber. I have a column named goals that only contains the number 1.

For example the query contains 3 rows. What I want is that another column counts from 1 to 3.

autonumber goals count
1 1 1
2 1 2
3 1 3

ThanXs

 

[Pat Hartman]

Unless you have a unique identifier for each row, you cannot do this in a query. It is quite easy to do in a report and doesn't even require any code. Just create a control and set its RecordSource to =1. Then set its running sum properto to Overall or OverGroup depending on what suits your requirements.

 

[qaccess]

Thanxs

Thanks for your answer. But it is not what I want. Even it is not possible. I create a table with soccer results. like

personA goal minute
john 1 30
bob 1 35


i like to create a query where number is not autonumber but a formula which start with 1 and then counting 2,3,4,5 etc.

personA goal minute number
john 1 30 1
bob 1 35 2

 

[Pat Hartman]

As I said earlier, you need a unique identifier to do this without duplicates. Here's a sample query that ranks by date but keep in mind that it produces duplicates when HireDate is not unique.

SELECT Emp1.LastName, Emp1.HireDate, (Select Count (*) from Employees Where [HireDate] < [Emp1].[HireDate])+1 AS Seniority
FROM Employees AS Emp1
ORDER BY Emp1.HireDate;

 

[meboz]

Could you reference the Microsoft Excel Object Library and use the RANK() function within a query?

I can see that your query works Pat, just wondering if RANK would work also

 

[Pat Hartman]

No, Rank() is a workbook function. You don't pass it a recordset to evaluate, you pass it a reference to a range in a spreadsheet.

 

[qaccess]

Thanks

Dear Pat,

Is it possible that you send me that piece of access with code you wrote in a mdb file. I am not so experiend with access. Thank you.

 

[Pat Hartman]

If you are talking about the "Select ...", that is SQL and it is created by the query builder. Go to the query tabl and click new. Close the table add box and change to SQL view. Copy the SQL statement that I posted and paste it here. Change all the table and column names so that they are your own.

Sending you the example I posted won't help you since it has nothing to do with your database. If you want to see the example in its own database, you can download it from the Microsoft downloads site. I posted a link to it in this post - Links to Samples - you are looking for the sample queries database. I think that both the A97 and A2K versions are referenced in the link. All of these example databases are worthwile downloading and looking at. You will be amazed at how many useful examples they contain.

 

[qaccess]

Thanks Pat

Pat, thanks for the information