function for a counter

[Guest]

i have a simple function that i use in an update query to add a counter for a
"project number" field. results as follows:
1530000 1
1530000 2
1530000 3

i now need to have the counter start over again with a new project number
and can't get it to work. thanks!

 

[John Spencer]

It might help if you posted the SQL for the update query and the code for
the simple function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

If your table has a primary key, pk, then try:



SELECT a.projectNumber, COUNT(*) AS rank
FROM myTable As a INNER JOIN myTable AS b
ON a.projectID=b.projectID AND a.pk>= b.pk
GROUP BY a.projectNumber




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

sql for query:
UPDATE tblPgm SET tblPgm.ACROSS = Qcntr([SPD_KEY_CODE]);

Function Qcntr(x) As Long
Cntr = Cntr + 1
Qcntr = Cntr

End Function