Group every 3 months

[bobfin]

We have a report that shows monthly records (no day in date). It's based on a query where the user enters a date range (e.g. Feb-2009 to Dec-2010). We want the report to show the month records in calendar order, but group totals for each 3-month range (not the calendar quarter). But the grouping keeps breaking at calendar quarters. The date field values are like "Feb-2009". We group on that field (GroupOn = Month, GroupInterval = 3, group footer) and then group on that field from a 2nd query column (GroupOn = Each, GroupInterval = 1). Other combinations are worse. How do we force the gruoping to be every 3 months no matter the starting month?

 

[Rokhi]

Have you tried the Partition function?

Look here http://office.microsoft.com/en-us/access/HA012288921033.aspx

 

[bobfin]

Basically, we want to sort by date and have a group footer every third record. We've tried at least a dozen variations, including adding a row number to the source data and grouping on that (Group On = Interval, Group Interval = 3). That last variation gave us a 3-record break at the start only if our date range started with March. All the rest of the breaks were at 3-record intervals. Starting at January gave us a 2-record break at the beginning. There must be a way to have section breaks every nth record!

 

[Rokhi]

Sorry, I misunderstood.
To get your report to break on every third record:
Put a hidden text box in the detail (Text1 for this example) with its control source set to '=1' and running sum set to 'over all' (no quotes).
Put a pagebreak under the detail controls (pagebreak1 for example)

In the detail format event put the code:

pagebreak1.visible = (text1 mod 3 = 0)

The visible property will not appear in the intellisense properties list for the break , but it is available so type it in anyway.

 

[bobfin]

We need totals every 3rd record, not page breaks. And we need as many groups to show on each page as possible. The group footers are occurring at the wrong times when we grouped on the month with a group interval of 3 (especially the 1st group). The date range can start on any month. Would changing the Running Sum property to Over Group work on something other than a page break?

 

[namliam]

I would probably do something with a subquery...

If you add a subquery to get the MIN(YOURMONTH) beeing 2008-11 then you can base of that the datediff in months and use a group on that to get every third month.

Note though... if you have a result like
2008-11
2009-01
2009-02
2009-04
2009-05
2009-06

You would still get totals after January and April, as those are "third months" as opposed to "third records".

If you want "third records" simply doing a "row number" will do the trick.
Make a function:

Public YourRowNumber As Long
Public RunTime As Date
Function fnMakeRownumber() As Integer
    If Now() - Nz(RunTime, Now() - 1) > #12:00:10 AM# Then YourRowNumber = 0
    RunTime = Now()
    YourRowNumber = YourRowNumber + 1
    fnMakeRownumber = YourRowNumber
End Function

Sub test()
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
    Debug.Print fnMakeRownumber
End Sub

Simply call this funciton in your query to make the rownumber(s) ....
Run the test function to see it work

Edit: Oops, Rokhi's solution is easier offcourse

 

[Rokhi]

Keep the hidden row count textbox text1 set to =1, with running total set as before.
Put a textbox txtTotal with no label in position under your summing column (txtMthlyTotal) with a height of 0 and cangrow = true, right aligned with txtMthlyTotal

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static lngTotal As Long
Static intTotalCount As Integer
Const intTotalMax As Integer = 10
 
lngTotal = lngTotal + txtMthlyTotal
 
If (Text1 Mod 3 = 0) Then
 
    ' Populate the total and it will grow
    Text3 = "Total " & lngTotal
    lngTotal = 0
 
    ' count the number of totals you have printed
    intTotalCount = intTotalCount + 1
Else
    ' clear total to shrink it
    Text3 = Null
End If
 
' new page if the number of totals reaches max
PageBreak1.Visible = (intTotalCount = intTotalMax)
 
' reset totals count when pagebreak switched on
If intTotalCount = intTotalMax Then
    intTotalCount = 0
End If
 
End Sub

 

[bobfin]

Attached is a snapshot of the report output. The date range entered was Feb-2009 to Dec-2009. In the Sorting and Grouping box, the first line has the date field with Footer = Yes, Group On = Month, Group Interval = 3, Keep Together = Whole Group. The second line (to force the rows into calendar order) has the date field, no footer, Group On = Each Value, Group Interval = 1. We want the first group to be Feb, Mar, Apr and the second group to be May, Jun, Jul, and so on. the whole problem is that the group footers don't always appear at 3-month intervals. There is no need to code for page breaks.

 

[Rokhi]

The group interval property splits the year into months starting at 1, so a group interval of 3 used on a date/time field grouped on month would give you Jan/Feb/Mar and Apr/May/Jun etc. starting at feb will give you feb/mar then apr/may/jun.
Similar concept to if you grouped a date/time field on hour and set the group interval to 12. The first group would be 00:01 to 12:00, second group would be 12:01 to 24:00. A report using values starting at 09:00 and ending at 22:00 would show 09:00 to 12:00 in the first group and 12:01 to 22:00 in the second.

Since you are starting at a non-zero interval position (feb), I'm suggesting you dispense with the report's native grouping functions and control the grouping and page breaks manually. You stand to get a finer control of the grouping and page breaks that way and will be able to group on a definite 3-record interval regardless of where the group starts.

If the data field names are going to be different from time to time, you could load the data manually in the report open method, instead of using the record source property, and populate the text boxes manually at that point, keeping subtotals via global or static variables in the report details format method.

Just a thought.

 

[Rokhi]

An alternative might be to pass in the year and month as numbers in the source query, then sort on the numbers 'year' and 'month', and you should be able to group on the number 'month' in group intervals of 3. I havn't tried it, but it might be worth a crack.

 

[bobfin]

thanks to you, we finally know to avoid using date fields for grouping. We tried your alternate method. We added 2 columns (Year & Month) to the report source query, then sorted the report first by the Year, then by the Month, then by the Date. The group footer was for Month with an interval of 3. Unfortunately, each group only had 1 record in it.

The general problem seems to be generating an event (a group footer) for every 3rd record, no matter what's in the recordset. Maybe a function could be coded to generate a value 3 times in a row, then another value 3 times in a row, etc.

 

[Rokhi]

Which group has the interval of 3 set on month? the numeric month field or the date field ?
If the date field you are sorting by is a full date, that may be upsetting things. If you need to subsort by day, return a separate day field too but still do the 3 record interval on the numeric month field.
Maybe you could post a subset of the data? Perhaps a csv subset generated using your query? Include a field for day as well as year and month generated from the date field.
You may want to post it in the private message area if you don't want it public.

cheers

 

[HiTechCoach]

Pardon me for jumping in ....

The way I have found to handle this is to use a "periods" table that has an numeric primary key and two fields for the start of the periods data and the end of the period date.

In your query, get the period's primary key value and group on that value.