'Group by' in reports

[LesWilcock]

Dear forum users

In terms of difficulty I don’t expect this to stretch the abilities of the group but hey you have to start somewhere.

I have created a report that lists financial transactions by date. What I would like to do is have them grouped by financial year (1st April to 31st March).

I know how to ‘group by’ a field that is in tables but how do ‘group by’ something that doesn’t exist.

I have a horrible feeling the answer will involve the writing of VBA code but I won’t know till I ask

So in summery my report currently looks like this:

Transaction date
11/04/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
15/06/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
10/10/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
15/12/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
04/01/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
12/02/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
11/03/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
03/04/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
15/04/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
16/05/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
22/05/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
30/06/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx

I would like it to look like this

Transaction date
2007-2008
11/04/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
15/06/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
10/10/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
15/12/2007 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
04/01/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
12/02/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
11/03/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx

2008-2009
03/04/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
15/04/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
16/05/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
22/05/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx
30/06/2008 xxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx xx

As ever, all help will be gratefully received

Les W

 

[pbaldy]

You should be able to add a fiscal year field in the underlying query, and group on that. One way:

http://support.microsoft.com/kb/210249/en-us

It appears you just need to test the month of the transaction date.

 

[Singh400]

In the qry the rpt is based on, under the field where the dates are stored. In criteria put BETWEEN #01/04/07# AND #31/03/08#, then sort that in Ascending order. More of a quick work around, than a proper solution (proper solution - see pbaldy's post)

@pbaldy: God I wish I had known about that before today, did things the long way round today at work. Doh!

 

[LesWilcock]

To Pbaldy and Singh400

Many thanks for responding.

I will go with the easy solution initially and get this job done but instinctivly I feel I should dig my old VB books out and dust them off for future requirements

Regards and thanks again