Running sum with two groupings (1 Viewer)

[ARK72]

Hi! I was wondering if someone could help me out with this. I have data that needs to have a running sum for it. The two criteria for re-setting the cumulative sum are: 1) LTA value and 2) fiscal year. Essentially, a query or report needs to sum up a data field called "ManHours" until LTA is greater than 0. It also needs to re-set the cumulative sum when the new fiscal year is reached (i.e. Nov 1). The ManHours and LTA data are updated on a monthly basis so the query or report needs to add up each month's data in the proper sequence. I've tried doing this using a report running sum over group but it doesn't work as expected (it separates out the LTA data completely). I then tried using DSum in a query but all I've been able to do is accumulate the data by calendar year. This is the DSum calcuation I have come up with so far: RunTot: DSum("HoursWorked","qryStats","DatePart('m', [MonthYr])<=" & [AMonth] & " And DatePart('yyyy', [MonthYr])<=" & [AYear] & ""). I've attached sample data. I haven't used the running sum feature in Access before so any help you can provide would be greatly appreciated!

 

[Pat Hartman]

Add a grouping level to the report to break by year and use the "over group" option rather than "over all".

 

[ARK72]

I did try that but didn't get the results I was looking for as the report would separate out the LTA data completely from the year data. That wasn't what I needed. I needed the data to be grouped by year and the sums needed to add up until an LTA value was greater than zero (the data would then began summing up again from zero until it hit the end of the fiscal year). I ended up coming up with a report that used VBA to sum up the totals but I'm not fond of this particular solution. I may need to pull the calculated values out of the report somehow down the road. But, I really appreciate your response and if you have other ideas, I'd love to hear them! Thanks!

 

[Pat Hartman]

When you group by year, you will get all the data for year 1 together. You will need a second sort level to order the LTA data so it can be summed correctly.

 

[Qazi]

i have two queries, the client name field is the same in both queries, how can i gather the two queries date with clientname. please help

 

[Pat Hartman]

I can't tell what you need from your explaination. You might need to union the two queries or join them. What do the queries contain and what do you want the resultset to contain?

 

[Qazi]

ok in queries,
[dailybook query] fields are head name, date, description, Dr., Cr., Total
[billquery] fields are head name, date, voucher#, Godown, quantity, rate, Total,
now i want to join these queries.

 

[Pat Hartman]

I don't think joining these two tables will produce the recordset you are expecting. Just because they have some common fields doesn't mean that it makes sense to join them. They look like two sets of independent transactions. Did you try the query yourself and find the problem?

 

[ARK72]

When you group by year, you will get all the data for year 1 together. You will need a second sort level to order the LTA data so it can be summed correctly.

I can only sort my data by Month and Year (field called "MonthYr"). If I sort by LTA, the data will be sequenced incorrectly. What I ended up doing is using VBA in a report to produce the running sums that I needed (I had to create three different running sums in all which is why the traditional route of using the built-in running sum feature in reports wouldn't work). However, I have two of the running sums on one report and a running sum on another report. I need to combine them on one report but have had no success with the subreport (it re-calculates my data so that it is incorrect). The only solution I can come up with is to try to extract the running sum data from the report and dump it into a table (I know this is not the proper way to work with a database but I see no alternative). Anyone know how to accomplish this via VBA? There are only 4 fields on my report: MonthYr, HoursWorked, LTA, Sum. If anyone could help, I'd appreciate it!

 

[Pat Hartman]

The only way you can create a running sum that always produces the same results is if your data includes a unique identifier and you include it in the order by clause. It doesn't have to be the primary sort field but it does need to be included. We can't help without knowing the columns in the table and what they mean as well as the relationship between the tables. Post the VBA you used to get the running sum so we can see the logic you used to order the records.