Number Expression in Query

[Vinnie]

Hello all,

Trying to Figure how to go about adding an expression in a Query what will
Automatically Number the Results.

Example:
Query of the Employeee Table, of employees who live in NY:

John
Ralph
Jim
Doug

Would like to add a Number Field in there, so results would like this:
1 John
2 Ralph
3 Jim
4 Doug

Thanks As Usual !

Vincent

 

[Graham R Seach]

SELECT Count(EmpName) AS [Count], EmpName
FROM myTable
GROUP BY EmpName, LivesIN
HAVING LivesIn = "NY"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[John Spencer]

Easy to do in a report. Harder to do in a query.

In a query, you need to be able to specify an order to determine which item is
1, 2, etc.

SELECT Employees.EmployeeName,
(
SELECT Count(*)
FROM Employees as Temp
WHERE Temp.EmployeeName <= Employees.EmployeeName
AND Temp.State = Employees.State) + 1 As Counter
FROM Employees
WHERE Employees.State = "NY"
ORDER BY Employees.State, Employees.Name

Note that this will give you ties if two employees have the same name and reside
in the same state.

In a report you could skip the calculation in the query.

Group the report by State
Add a control
Control Source: =1
Running Sum: Over Group

 

[John Spencer]

small error in sample query.

SELECT Employees.EmployeeName,
(
SELECT Count(*)
FROM Employees as Temp
WHERE Temp.EmployeeName < Employees.EmployeeName
AND Temp.State = Employees.State) + 1 As Counter
FROM Employees
WHERE Employees.State = "NY"
ORDER BY Employees.State, Employees.Name

 

[Vinnie]

Thank You !

small error in sample query.

SELECT Employees.EmployeeName,
(
SELECT Count(*)
FROM Employees as Temp
WHERE Temp.EmployeeName < Employees.EmployeeName
AND Temp.State = Employees.State) + 1 As Counter
FROM Employees
WHERE Employees.State = "NY"
ORDER BY Employees.State, Employees.Name