Running Sums Over a Group in a Query

[cbridgman]

I am trying to calculate, via a query, a "line number" for each row in an access table. The line number needs to break and be reset to 1 for each "group" of records in the table. A group of records in the table is defined as records that have the same value in a field entitled "account number". For example, the first three rows in the table have an account number of 100232000. Those records would be assigned line numbers 1, 2, and 3 respectively. The next two records in the table have an account number of 100433000. Those records would get line numbers 1 and 2 respectively. Etc., etc., etc.

This is a fairly simple operation in a report. You just set a control's "Running Sum" property to "Over Group". The problem with using a report to do this is that it doesn't actually update the table's values with the calculated line number. It just displays it in a report.

Does anyone know how to do this in an update query? I have tried, unsuccessfully, to use the DSUM function. Maybe I'm just using the wrong set of criteria or something.

Any ideas would be greatly appreciated.

Thanks.

Chip

 

[willsaunders]

Haven't really got time to look arounf for you matey at the moment but one idea that pops into my head is to try DCOUNT.

Just thought I'd give you one idea as no one else has replied. Haven't given it much thought so it could lead you completely the wrong way.

Let us know how you get on,

Will.