Full Version: Calculate Total For Each School Year
sugarcane
Hi There,
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...
Thank you in advance.
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...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.