Full Version: Calculate Total For Each School Year
sugarcane
Hi There,

I haven't been using access as much as I use to so if this is quite simple, please forgive me.

The code below output the total for each calendar year. What I need is the total for each school year. So it should show the total from September 2006 - August 2007, September 2007 - August 2008, etc...

Here's the Code:

SELECT DISTINCTROW Format\$(qryReports.InvoiceDate,'yyyy') AS [InvoiceDate By Year], Sum(qryReports.GrandTotal) AS [Sum Of GrandTotal]
FROM qryReports
GROUP BY Format\$(qryReports.InvoiceDate,'yyyy'), Year(qryReports.InvoiceDate);
theDBguy
Maybe something like:

SELECT Year(InvoiceDate)-(InvoiceDate>DateSerial(Year(InvoiceDate),9,1)) As FY, Sum(GrandTotal) As Total
FROM qryReports
GROUP BY Year(InvoiceDate)-(InvoiceDate>DateSerial(Year(InvoiceDate),9,1))

(untested)
Hope that helps...
sugarcane
Untested...but works! Thanks so much.

Just one more question...In the query it shows the year as 2004, 2005, 2006, etc... next to the total
How can I have it show the year like this: Sept. 2004 - June 2005, Sept. 2005 - June 2006.

Thanks again!
theDBguy
You can try:

SELECT "Sept. " & (Year(InvoiceDate)-(InvoiceDate>DateSerial(Year(InvoiceDate),9,1)))-1 & " - June " & Year(InvoiceDate)-(InvoiceDate>DateSerial(Year(InvoiceDate),9,1)) As FY, Sum(GrandTotal) As Total
FROM qryReports
GROUP BY "Sept. " & (Year(InvoiceDate)-(InvoiceDate>DateSerial(Year(InvoiceDate),9,1)))-1 & " - June " & Year(InvoiceDate)-(InvoiceDate>DateSerial(Year(InvoiceDate),9,1))

(air code)
Hope that helps...