When I design an access report which can be used by multiple clients I code the report headings and the data selection for the months to allow the client to change this if required.
In the sample above the client financial year starts on the 1st of March. They can set the column heading to reflect their year start and end.
In the report design the header is defined using a custom function =rpthths(gperiod(1)), the second column is defined as =rpthths(gperiod(2)).
For the Total by ticket type I use the following function =Sum(IIf([month]=gperiod(1),[Value],0)) and this is incremented across the columns.
In the recordset I have Month which extracts the month number from the transaction date and value is the field which represents the values to be totaled.
Global myPeriod(12)
Function Calcmth()
myPeriod(1) = GetPref("First Month No")
If (myPeriod(1) + 1) > 12 Then myPeriod(2) = 1 Else myPeriod(2) = myPeriod(1) + 1
If (myPeriod(2) + 1) > 12 Then myPeriod(3) = 1 Else myPeriod(3) = myPeriod(2) + 1
If (myPeriod(3) + 1) > 12 Then myPeriod(4) = 1 Else myPeriod(4) = myPeriod(3) + 1
If (myPeriod(4) + 1) > 12 Then myPeriod(5) = 1 Else myPeriod(5) = myPeriod(4) + 1
If (myPeriod(5) + 1) > 12 Then myPeriod(6) = 1 Else myPeriod(6) = myPeriod(5) + 1
If (myPeriod(6) + 1) > 12 Then myPeriod(7) = 1 Else myPeriod(7) = myPeriod(6) + 1
If (myPeriod(7) + 1) > 12 Then myPeriod(8) = 1 Else myPeriod(8) = myPeriod(7) + 1
If (myPeriod(8) + 1) > 12 Then myPeriod(9) = 1 Else myPeriod(9) = myPeriod(8) + 1
If (myPeriod(9) + 1) > 12 Then myPeriod(10) = 1 Else myPeriod(10) = myPeriod(9) + 1
If (myPeriod(10) + 1) > 12 Then myPeriod(11) = 1 Else myPeriod(11) = myPeriod(10) + 1
If (myPeriod(11) + 1) > 12 Then myPeriod(12) = 1 Else myPeriod(12) = myPeriod(11) + 1
End Function
Function rptHths(x As Integer)
Select Case x
Case 1
rptHths = "Jan"
Case 2
rptHths = "Feb"
Case 3
rptHths = "Mar"
Case 4
rptHths = "Apr"
Case 5
rptHths = "May"
Case 6
rptHths = "June"
Case 7
rptHths = "July"
Case 8
rptHths = "Aug"
Case 9
rptHths = "Sept"
Case 10
rptHths = "Oct"
Case 11
rptHths = "Nov"
Case 12
rptHths = "Dec"
End Select
End Function
Function gPeriod(x As Integer)
gPeriod = myPeriod(x)
End Function
Let me know if this works for your reports support@anythingaccess.com
Check out Mary McCathy post for a more detailed option