Group reports on odd year

[EricE]

Similar to the fiscal year, I'm trying to group items in a report on a contract year.



It starts at June 16, 2009 which (using Wolfram Alpha) is day 168 and runs to June 15, 2010. It's June 16, 2010 to June 15, 2011 for the next contract year - so forth and so on.

So in the footer of the group in the report I added a text field, put this in the Control Source for the text field:

=Year(DateAdd("d",+168,[Invoice Date]))

And it is grouping by multiple years - it just isn't grouping from June 16 to June 15 of the next year like I desire. It's also not grouping on the standard calendar years - I haven't been able to figure out what date range it is grouping since I don't have enough rows - that's probably going to be my next step to try to narrow down exactly what the heck it is doing.

Obviously I'm doing something wrong - anything obvious jump out an anyone? easier way to do this perhaps?

Thanks!

[hertfordkc]

available in the query. Your post suggests that you expect the report to group on a value which you are calculating in the footer. I've reread your post and wonder if it is really grouping on any value.

[EricE]

available in the query. Your post suggests that you expect the report to group on a value which you are calculating in the footer.

I'm not sure where at this point Access decides to do grouping from - the header, footer, report properties - I've yet fo find any documentation, tutorials or guidance that has any kind of overview or direction on reporting and grouping in reporting that makes sense.

I've reread your post and wonder if it is really grouping on any value.

It is grouping, but it doesn't have value since it's not grouping in a way that's useful. Let's assume I know nothing about grouping in reports

I have three things I need to group on:

A) An organization code
B) Invoice date
C) Inividual line items that among other things have a unique part number

I'd like the report to group based on:

1) Organization
2) Group the line items, by invoice date yearly according to the contract year (not calendar year)
3) Group multiple line tiems by part number and then sub-total each grouping of the same line items

I have the grouping by organization (group 1) working. I have the grouping by line items with the sub-totaling the line items in the header for that group (group 3) working.

What I can't get to work is the grouping of the line items by contract year (group 2). Out of frustration I ended up figuring out how to set up date range for the report; I ended up running three seperate reports to cover the three contract years I need. But I know that there has to be a way to do it with grouping in Access, I'm just not up to speed enough with how grouping actually works.

It also doesn't help that the interface in Access 2010 doesn't mach 95% of the tutorials out there that are based on previous versions of the product

If you do decide to take pity on me and help me out I would be most grateful! Even if it's referring me to an Access 2010 based tutorial on grouping.

Thanks!

[hertfordkc]

but since I have Access 2007, I don't know if I could open anything which you sent to me.
Let me pose a few hypotheses and see if they help.
1) You have a query which includes fields for Organization Code and Invoice date.
2) From that query, you can use the report wizard to group and total things.
3) As a further grouping, you would also like to group by contract year.

Perhaps including a calculated field in your queries which generates "Contract Year" based on the date formula which you posted would allow you to specify that field for grouping in the report wizard.

I'm not sure that the report wizard will give the final answer, but I think it will help you down the road if you can use it as a basis.