Cumulative total column issue in query

[Alp54]

Hi experts,

I have managed to include a "cumulative total" column to my query but with a problem which I hope someone can point me in the right direction.

Here's what I need to achieve: My data starts relatively in 3rd quarter of any given year and ends approximately in 2nd quarter of the following year. I am trying to get weekly cumulative totals on items like number of contracts secured.

Due to the nature of my data, even though I am able to get these cumulative totals, when the year part goes into the following year the week number naturally starts from 1 and my cumulative total starts from week 1.

I need the total to continue from week 52 of the previous year. Unfortunately I do not have a sequential data field to base the cumulative total column on.

Thus I need to either find a solution to "continuing" from week 52 figure or to add a sequential column to my base query to overcome this problem.

Any help would highly be appreciated. Thanks in advance.

Alp

 

[plog]

This is my favorite topic. Advance search the forum for my user name and the keyword 'weeks'. Cumulatively I've written a thesis on this.

You are trying to fit weeks into larger entities that don't always contain full weeks. Weeks have 7 days, all your other entities don't always have a number of days that is divisible by 7 (e.g. Year 2014=365 days, Quarter 1 2014 = 90 days). So when try to overlay weeks onto them you get remainders and those remainders cause unexpected results. Run a query to determine the week number of 12/31/2000 to see what I mean.

From a practical standpoint, you probably need to build custom functions that you pass a date to and the function will return the appropriate value for what date entity it falls into (i.e. getFiscalYear(10/1/2014) returns 2015, getFiscalQuarter(8/31/2014) returns 4).

But because of the way date entities aren't always perfectly divisible you really need to think about what the rules of each function are and test it for edge cases.

 

[Alp54]

Hi plog,

Thank you for responding. In the mean time I managed to solve my problem, or so it seems. I still need to evaluate the results in detail.

It took 4 queries to get what I need: I collect all info I need with the first query(trz_base), I get my week totals in the second, where it starts from week 9 of 2012 ends in week 21 of 2013 (trz_result).

Then I made a third query where I added 52 to the week number if the year is less than the current one in a new field through:
wk: IIf([yr]<DMax("yr";"trz_result";"");[week];([week]+52)
This works fine as long as the year span is only two years.

The fourth is where I added my cumulative fields referencing to this last field I added in query3, namely "wk". It seems like its done, but I might be losing cents part on the cumulative currency field which I need to check.

This may not seem like the best way to do this, i.e. 4 separate queries to achieve the result, but it seems to have served the purpose. But I'm open to suggestions.

I will be using this data in 3 separate charts on a report which I guess will be yet another challenge with Access since the charts shall include data from the previous term as well for comparison. So I might end up posting several more questions in reports thread.

Alp

 

[Alp54]

@ plog As a followup I found out about your getweeknum function, added to a module and made a small modification which I think will prove better in my case. Modified code is:
....
If (WeekNum = 53) And (YearNum Mod 4 > 0) Then
' if WeekNum is 53 and not leap year, rolls over return value to next year
YearNum = YearNum + 1
WeekNum = 1
End If
'Modified by Alp Bekisoglu 06/04/2014
If Len(CStr(WeekNum)) < 2 Then 'test if single digit
YearNum = YearNum * 10 'to add a 0 preceeding signle digit week number
End If
'getWeekNum = WeekNum & "-" & YearNum
getWeekNum = YearNum & WeekNum
End Function

This way I get week numbers as 201340, 201401, etc. so I believe it can easily be used even over a 2 or 3 year span.

Thank you for the function and also making me think!

Alp

 

[plog]

Test it against 12/31/2000

 

[Alp54]

Test it against 12/31/2000

That yields 200054