Building an accumulator

[Victoria]

Sorry for the crosspost- trying to get this question to the most appropriate
group.

I am working on a project where I need to build an accumulator in access.
Basically, it would accumulate visits on a subid, memno level by date of
service. So if a subid/memno combination had 50 rows, each with a variable
amount of visits, it would create or populate a new column with the summation
of visits to that point based on date of service. Thus, the value would be
different at each row because it would continue to accumulate by date of
service.

My guess is that this can be done with a module, I just don't know how.

Please help!!!

Thanks,
Victoria
(e-mail address removed)

 

[John Spencer MVP]

Normally this would be handled in a query and the data would not be stored in
a field in a table.

If you search for running sum you will probably find some examples of what you
are seeking.

The query might look something like

SELECT A.SubID, A.MemNo, Sum(B.Cost) as Total
FROM TableName as A INNER JOIN TableName as B
ON A.subID = B.SubID
AND A.Memno = B.MemNo
AND A.DateField <= B.DateField
GROUP BY A.SubID, A.MemNo

Or you could use a subquery to do the same thing

SELECT A.SubID, A.MemNo
, (SELECT Sum(Cost) From TableName as Temp WHERE Temp.SubID = A.SubID AND
Temp.Memno = A.Memno AND Temp.DateField <= A.DateField) as Total
FROM TableName as A


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County