Update Query Needed For Similar Excel Function Countif(B$1:B1,B1)

[adam6023]

I need a formula/query for column B in Access that would produce the result below. This would be in an update query where B would be the calculated field. In excel, the formula would be Countif(B$2:B2,B2) and would drag down so that the result would show the number of times that field has been seen above. I have millions of records so need a formula to produce this exact result.

A B
Ben 1
Adam 1


Chris 1
Ben 2
Ben 3
Chris 2

[June7]

Requires a unique ID field, autonumber field should serve.

One approach uses DCount domain aggregate function.

SELECT ID, A, DCount("*", "TableName", "A='" & [A] & "' AND ID<" & [ID]) + 1 AS GrpSeq FROM tablename;

However, with millions of records, I expect this will perform so slowly as to be non-functional. There is probably a way to do with a nested subquery but likely will also perform quite slowly.

Textbox in report has RunningSum property which can easily calculate the sequence number in Group header/footer section.

Now if you just want to know the total count and don't need to assign sequence number to each record, do an aggregate query.