Rolling Sum/Calculation Using DSum

[kjohns]

I have read a previous thread on rolling sums using DSum (http://www.access-programmers.co.uk/forums/showthread.php?t=112221), but I am running into issues with writing the DSum query I want.

Below are a few of the fields I have in table ImportData. (Note that these fields are named as they are when I import the data from a data dump.)
Fiscal Year (CPD) - like 2007, 2008
Month Name (short) - like Jan, Feb
Fiscal Period (CPD) - like 1, 2, 3, where July (or month 1 of the FY) = 1
Non Bad Debt Payments
Bad Debt Payments
Charges

I need to run the following calculation on a rolling three month period:
(Non Bad Debt Payments + Bad Debt Payments)/Charges

I'm not too worried about overlapping Fiscal Years, so for July, it'll be July's NBDP + July's BDP/July's charges. For August, add in August's values. For September, it'll be the sum of July, August, and September's NBDP and BDP/charges for July, August, and September. For October, it'll be the sum of August, September, and October's NBDP and BDP/charges for August, September, and October. (Have I lost you yet?)

I liked where namliam was going in the post dated 08-04-2006, 02:21 AM on the thread above, but I'm getting stuck on just using the Fiscal Year and Fiscal Period instead of a date.

Can anyone help? Could you even get me started with just a rolling sum of Non Bad Debt Payments?

Thanks in advance!
Kate

 

[pbaldy]

If you don't care about the fiscal year, I think you can concatenate the year and month so July is 200901, August is 200902, etc. Then I think you can just subtract 3 for your 3 rolling months.

By the way, rather than referring to a previous post's date and time, I'd use the post # to the right. The date/time is user specific, so the post you referred to shows as 8/3 at 11:21 PM to me, as I'm 3 hours behind you.

 

[kjohns]

Paul,

That sounds like it would work as long as there's a way to force the 0 in 01 to concatenate, as the raw data pulls over without leading zeroes.

If I were to do that, would I use the example that was in the cited thread and treat it like a date, in the format yyyymm?

In that case would I be looking at something like this, assuming I name the concatenated field FiscYearPeriod?
DSum("[Non Bad Debt Payments]","[Import Data]", TheDate<=#" & Format([FiscYearPeriod],"yyyymm") & "# and TheDate>=#" & Format([FiscYearPeriod]-3,"yyyymm") & "#") AS 3MonthSum

Thanks for the tip about the post numbers. I wondered about that.
Thanks.
Kate

 

[pbaldy]

I'm thinking treat it like a number, since subtracting 3 from 200901 won't pull any 2008 records, which sounds like what you want. You can use the Format function to force the month to 2 digits:

Format(FieldName, "00")

 

[kjohns]

Okay, I've got the Fiscal Year and Period concatenating so that they look like 200901, but I'm still struggling with the DSum.

Can anyone help with the DSum statement?

 

[kjohns]

I've been playing with this more and using SQL instead of DSum. I'm getting closer using the query below.

SELECT A.[Fiscal Period (CPD)], A.[Fiscal Year (CPD)], A.[Provider Name], A.[Department Name], A.Charge, (SELECT Sum(B.Charge)
FROM ImportData AS B
WHERE (B.[Fiscal Period (CPD)]) Between [A].[Fiscal Period (CPD)]-3 And [A].[Fiscal Period (CPD)]) AS RollingCharge
FROM ImportData AS A
GROUP BY A.[Fiscal Period (CPD)], A.[Fiscal Year (CPD)], A.[Provider Name], A.[Department Name],A.Charge
ORDER BY A.[Fiscal Year (CPD)], A.[Provider Name], A.[Department Name];

The problem is that the RollingCharge is summing ALL Period 1s together and repeating it for each Fiscal Year/Provider Name/Department Combination. What I really want is to have a running sum for each Fiscal Year/Provider Name/Department combination. Then I'd want to group it that way.

Any suggestions on either this approach or the DSum approach?

 

[pbaldy]

Can you post a sample db?

 

[Roblogic]

Can you post a sample db?

please, i think this could help me with my current problem.

 

[kjohns]

I'm still working on stripping out data from my database to post it, but I'm swamped right now. However, a co-worker and I did figure out how to pull the rolling data in a field in the query, so I thought I'd post that in case it might help anyone.

RollingNonBadDebtPayments: (SELECT Sum(B.[Non Bad Debt Payments])
FROM ImportData AS B
WHERE (B.[Fiscal Period (CPD)]) Between [A].[Fiscal Period (CPD)]-2 And [A].[Fiscal Period (CPD)]
AND B.[Fiscal Year (CPD)]=A.[Fiscal Year (CPD)] AND B.[Provider Name] = A.[Provider Name] AND B.[Department Name] = A.[Department Name])

I'll keep working on the database.

 

[Roblogic]

that does help some.


i'm looking to have a rolling 29 day total for cashier variances (unique ID, all positive intergers from tblVariance) displayed in my subform (sfrmVariance) when the information for the employee is pulled up (from tblMasterStaffList) and then updated when a new variance is added. (to frmVariance)


i'm so close but so far....