function for a counter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magick - 20 Nov 2007 15:30 GMT

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!

Reply to this Message

John Spencer - 20 Nov 2007 16:39 GMT

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

Signature

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

>i have a simple function that i use in an update query to add a counter for
>a
[quoted text clipped - 5 lines]
> i now need to have the counter start over again with a new project number
> and can't get it to work. thanks!

Reply to this Message

Michel Walsh - 20 Nov 2007 16:40 GMT

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

Reply to this Message

magick - 20 Nov 2007 17:04 GMT

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

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

End Function

> i have a simple function that i use in an update query to add a counter for a
> "project number" field. results as follows:
[quoted text clipped - 4 lines]
> i now need to have the counter start over again with a new project number
> and can't get it to work. thanks!

Reply to this Message