sequential count field in query by Id

[rbremer]

I need to create the gift counter field in a query field that counts the number of gifts by Id and starts over when it detects a new ID. The ID and gift_date fields are already sorted in ascending order. I have experimented with DCount. This has proven not to work Nz(DCount("*","All_Gifts","[Constituent ID] <= " & [Constituent ID]),0)+1 I would prefer to do this as a field within the same query rather than have to create another query and join it back since the dataset is large. These are the results I am looking for:

Constituent ID	Gift_date	Gift_counter
1112	3/15/2015	1
1112	4/15/2015	2
1112	5/15/2015	3
1225	1/12/2015	1
1225	2/15/2015	2
1225	3/15/2015	3
1225	4/15/2015	4
1381	2/15/2015	1
1381	3/15/2015	2

Thank you

[June7]

You want to rank records by group. This is not easy in query or form. Textbox in report has RunningSum property so can be done in report with little effort.

DCount() Is one method. You need the Gift_date field in the expression.

DCount("*", "All_Gifts", "[Constituent ID] = " & [Constituent ID] & " AND Gift_date <#" & [Gift_date] & "#") + 1

Also nested subquery http://allenbrowne.com/ranking.html

Either way, such queries can be slow performers.

[rbremer]

Thanks for the feedback, but pasting this formula in to my query results in an #Error message for every row.

[June7]

Make sure field names in the expression are spelled correctly. If [Constituent ID] does not have a space, remove it.

Otherwise, it works for me.

[rbremer]

You're right, it does work if you are not trying to sort the Constituent ID and gift_date variables within the same query. However, I tried it on a sample of 1000 records and it ran too slow. I need it to run on 30,000 records, so I think I will try it in a report instead. Thanks for your help.