Totals for year. (Ended June...Not December)

[kennedymr2]

I am trying to create a report which totals the sales for each year (Ended 30th June) by client.)

ie 1999 AAAAAA 333.333
1999 ZZZZZZ 455.55
2001 AAAAAA 99.00
My detail file only contains scattered dates. eg some clients do not allways have an entry for june in each year.

Is there a simple formula to get a report or query to add by client by year (1st July-30th June)

I can see how to do it by createing a seperate table of from/to dates, but don't like the idea.!!

Appreciate any help., i imagine it is probably SIMPLE!

 

[dhookom]

You can group by
FiscalYear: Year(DateAdd("m",6,[ScatteredDatesField])
This will calculate the year as if the dates were all 6 months later.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kennedymr2]

dhookom,

Thanks very much for your help., much appreciated.

Its supprising that access and vb do not have a built in function to cater for different end of year periods.


Regards kennedymr2

 

[dhookom]

Any function that they write would require about the same number of arguments as the expression I suggested.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kennedymr2]

dhookom ,
I agree, this did enter my mind!! after entering the comment.

Thanks for your help

Regards kennedymr2