Update Qry (running sum)

[johnb23]

I need to update a ytd-budget field using the curmo-budget field. The table contains one record for each month. The ytd-budget for Jan would be the same as curmo-bud for Jan. Feb's ytd would be the sum of Jan + Feb...etc. Is there a way to update the ytd fields with a 'running sum' feature as found in the report options?
(... unfamiliar with VB). Thanks

 

[Cosmos75]

See if this helps you get started

Running (or moving) Calculations (Sum/Total, Count, Average)

 

[neileg]

Are you sure you need this running sum? You can calculate the cumulative budget whenever you need it, so why do you want to store it?

 

[johnb23]

The link to MS Access samples was encouraging...looked just like what I needed. However, there must be something unique about the Dsum function in an Update query...it updated each record selected with the Grand Total of all records...instead of a running sum. The update query was set up as follows:

Field: YtdBud
Table: Goals
Update to: DSum("[CMBud]","[Goals]")
Criteria: [CMAct]> 0

Thanks for taking a look at this.
jb

 

[Jon K]

DSum("[CMBud]", "[Goals]") will always return the total from the whole table.


Unlike displaying running sum in a report, to calculate running sum in an Update query (or a Select query), the table needs to have a field which can uniquely identify each record and which can be properly sorted in ascending order.

Hence, if the months are stored in a date field eg 1/1/2006, 1/2/2006, 1/3/2006, etc or in a numeric field such as 1, 2, 3, etc, you can modify the DSum expression like these:-

DSum("[CMBud]", "[Goals]", "[DateField]<=DateValue('" & [DateField] & "') and [CMAct]>0")

DSum("[CMBud]", "[Goals]", "[NumericField]<=" & [NumericField] & " and [CMAct]>0")
.

 

[johnb23]

Thanks, JohnK, I appreciate your suggestions.
I tried the DSUM using the DateValue example (which, due to inexperience, I don't understand...DateField<=DateField ???)...but, it failed to yield the correct result. I tried substituting a date string i.e. #4/30/2006# but got a syntax error.

I think the problem has to do with grouping. The table has budget info for each month (Jan - Dec)...for each of six divisions (01 - 06). The running total needs to start over with each division. I thought by selecting one division at a time (i.e., 01) I could DSUM those records only. Apparently not.

 

[Jon K]

The DSum syntax depends very much on the data types of the table fields.

It would be easier for us to suggest a solution if you could post a database (preferably in Access 2K or 97 format) with the table filled in with factitious data.
.

 

[johnb23]

I included all tables in the Budget mdb...the one in question is Goals. (I do not see the results of the upload...let me know if it was not successful).

 

[EMP]

No attachment.

You have to compact the database and then zip it. The file size limit of a zip file for upload here is 393KB.

^

 

[johnb23]

Thanks for the info...I think it worked this time.

 

[Jon K]

See the Select query in the attachment.

In the [YTDBud for each Division] field, I used the following DSum expression:-

YTDBud for each Division: DSum("[SBETOT]", "[Goals]", "[SBLOC_ID]=" & [SBLOC_ID] & " and [SBEDT]<=DateValue('" & [SBEDT] & "') and [CMAct]>0") +0


The field was formated as Standard in the Properties sheet in query Design View. Unfortunately, the Properties sheet didn't allow me to set decimal places. So, on my system, the field is displayed with default 2 decimal places.

The final +0 in the expression forces the DSum expression to return numbers rather than text strings.

The DateValue function enables the expression to work properly on systems using m/d/yyyy or d/m/yyyy date format even if both m and d are < 13. On m/d/yyyy systems, you can use the # signs instead of the DateValue function: ... and [SBEDT]<=#" & [SBEDT] & "# and ...
.

 

[johnb23]

Thanks Jon K...your solution worked perfectly. I really appreciate your explanation as I am not only a 'how' person; but a 'why' guy also.

Thanks to Cosmos75, neileg, and EMP as well.

Keep up the good work.