group by financial year

[Happy YN]

What is the quickest way of querying records to group them (sum them) by financial year i.e. from 1 july till 30 june each year thanks

 

[David R]

Depending on which way fiscal years go, you could probably do a little math trickery with Year(DateAdd("m",(+/-)6,[RealDateField])) and group by that field. The parenthetical part is of course up to you to determine, I'm not an accountant.

[This message has been edited by David R (edited 05-30-2002).]

 

[Happy YN]

Not sure what you mean but I'll tryt it
Thanks
Happy YN

 

[David R]

Year(DateAdd("m",(+/-)6,[RealDateField])) will convert your "fiscal year data" into a Jan1-Dec 31 situation and only return the Year. Then you can group by that in a Totals Query and Sum the other field as needed.

You could probably use DatePart("q" to get the Quarter as well, that might be a little more foolproof.

 

[Happy YN]

Hi all that function does is add on 6 (or other no.) months and then give the year. I want to know which dates are between jul 1st and jun 30th of EACH year so I can then perform calculations on the records of each financial year seperately
Thanks

 

[KKilfoil]

In your last post, you basically said "All that function did was give me a unique value per financial year. How do I use this?"

Define a field in a query like
FinYear: Year(DateAdd("m",(+/-)6,[RealDateField]))
and ensure this query includes your table primary key (PK) to match the new field with your data (or include every field in your table with the * operator, and just use this new query as your subsequent datasource.)


You can then make a summary query to group by FinYear and perform whatever calcultion you want, or a select query with a parameter to filter out all but one year to get the details, or....

 

[Happy YN]

Maybe I am not being clear enough. I did exactly that in my query BUT
firstly the query would not let me write (+/-)6 it said that there was a syntax error
secondly how does that extract from each[transactionDate] field , the year in which it happened. How does it know when I count the beginning and end of year? Is it that it groups everything from 6 months before and after? If so why isn't it working
thanks for all your continued help

 

[David R]

I couldn't remember which way financial years worked, that's why I put the +/- in there.

If Fiscal Year 2001 goes from July 1 2000 to June 30 2001, then DateAdd("m",6... will give you a result of "2001", which is correct, right?
If on the other hand Fiscal Year 2001 goes from July 1 2001 to June 30 2002, then you want -6. Make more sense now? Now you've got a calculated field that lists what fiscal year you're in, so you can sum the transactions in that year. I hope.

Post back if you need more help,
David R

 

[Happy YN]

No dateadd just adds 6 months onto [myDate] and the gives the year but I found this in an article in ms knowledgebase q210249 and it seems to be working. Basically it keeps the current year but if it is before jun 30th then it subtracts 1 from that year figure so it automatically becomes considered as previous year. Here then is the query expression
year([mydate])-if([mydate]<dateSerial(year9[mydate]),6,30),10)

It seems to be working so I''ll stick with that thanks awfully for your help you certainly pointed me in the right direction1

 

[Happy YN]

OK I am ready to go back and admit I now understand your way. after putting yours and my expressions side by side in a query i see they give the same result and I finally understand what you have done. Rather than measuring jan till dec you have said everything from july onwards moves on 6 months and it measures the year then so everything within 6 months before jan 2001 now becomes 2001 and everything after jul 2001 now becomes 2002 Very Clever!!
my way allows me to specify an exact date but I suppose it could be done your way aswell??
thanks awfully again
A now much happier Happy YN!

 

[David R]

I would go with your way if you have an exact dating system. My method may have some error blurring around the edges, for example Dec. 31st.

 

[raskew]

To return all records within a specified FY, you can use a variation of this (example based on Northwind's Orders table, but it'll work with any table with a date field):

PARAMETERS [enter FY start] DateTime;
SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate)>=[enter FY start] And (Orders.OrderDate)<DateAdd("yyyy",1,[enter FY start])));