Running sum with two groupings

[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!

 

[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!

 

[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

 

[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.

 

[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!