Running Count

[MaDoyle]

I'm attempting to do a running count on records in a query and reset the count starting again at 1 for each group of records displayed. Following are my fields and sample date.

QualityRef - primary key AutoNumber
Name- Product reference

I do have other fields in the query, 4 in total but the two fields above are the ones I'm interested in working with.

Sample Data

QualityRef Name Running count
100 AdvisorA 1
101 AdvisorA 2
102 AdvisorB 1
103 AdvisorC 1
104 AdvisorC 2
105 AdvisorC 3

I have found the following code im trying to use " DCount("*","[tblServiceData]","Name = " & [Name] & " AND QualityRef <= " & [QualityRef])" but the field is just returning #error#

Any help would be appreciated

 

[vbaInet]

[FONT=Verdana][COLOR=black]DCount("*","[tblServiceData]","[COLOR=Red][[/COLOR]Name[COLOR=Red]][/COLOR] = [COLOR=Red]'[/COLOR]" & [Name] & "[COLOR=Red]'[/COLOR] AND QualityRef <= " & [QualityRef])[/COLOR][/FONT]

The amended bits highlighted in red.

Fyi: Name is a reserved keyword in Access and shouldn't be used as a field name.

 

[MStef]

Look at "DemoRuningCountA2000.mdb" (attachment, zip).
Look at Table1, Query1 (column 3), Module1.
Run Query1.

 

[MaDoyle]

Thanks for you help Mstef that works perfect
Thanks