-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Yes! Far too much ;-)
Have you tried it yet. I wasn't sure it would work.
To get the FY info you'll have to use the DateAdd() and DatePart()
functions. The following shows that 15 Nov 2004 is in your 2005 FY, 6th
month. Just change the date to find out which FY the date is in.
FY: datepart("yyyy",dateadd("m", 7, #11/15/04#)) = 2005
FM: DatePart("m",DateAdd("m", 7, #11/15/04#)) = 6
SELECT Dist, DatePart("m",DateAdd("m", 7, RTL_Date)) AS [Month],
DatePart("yyyy", DateAdd("m", 7, RTL_Date)) AS [Year],
Count(*) AS ProjCount ,
(SELECT Count(*)
FROM WebCharts
WHERE Dist=WC.Dist
AND DatePart("yyyy", DateAdd("m", 7, RTL_Date) =
DatePart("yyyy", DateAdd("m", 7, WC.RTL_Date)
AND DatePart("m",DateAdd("m", 7, RTL_Date)) <=
DatePart("m",DateAdd("m", 7, WC.RTL_Date))
) AS RunningCount
FROM WebCharts As WC
GROUP BY Dist,
DatePart("m", DateAdd("m", 7, RTL_Date)),
DatePart("yyyy", DateAdd("m", 7, RTL_Date))
Instead of putting this nested function in the query you could write a
VBA function, in a common module, that does the same thing & just call
that function w/ the date you want to convert to FY.
Public Function toFY(strDatePart As String, dte As Date) As Integer
Const OFFSET = 7 ' The number of months after Jan 1, on which
' the fiscal year starts.
' Only works if the FY starts on
' the 1st of the month.
toFY = DatePart(strDatePart, DateAdd("m", OFFSET, dte))
End Function
In the query use like this:
SELECT Dist,
toFY("m", RTL_Date) AS [Month],
toFY("yyyy", RTL_Date) AS [Year],
Count(*) AS ProjCount ,
.... etc. ...
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQjfolIechKqOuFEgEQKGUgCeNkjHAUb3fhFja9NSjdtEoTTi8TIAoLSz
VmO31Md7KrRgePvIoC6BIVFJ
=9IBZ
-----END PGP SIGNATURE-----
This is Much Further than I could get,Thank You. But the added twist is that
our Fiscal Year is 7/1/yy00 to 6/30/yy01, so technically 11/15/2004 is part
of the same year as 4/1/2005.
Is this too much?
Thank you
:
Iman wrote:
I am trying to have a running total by Dist, By Month By Year. I can Not
figure it out. Please help Thanks
SELECT WebCharts.Dist, DatePart("m",[RTL_Date]) AS [Month],
DatePart("yyyy",[RTL_Date]) AS [Year], Count(WebCharts.key) AS ProjCount
FROM WebCharts
GROUP BY WebCharts.Dist, DatePart("m",[RTL_Date]),
DatePart("yyyy",[RTL_Date]);
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
A running total of what? As a guess, try this:
SELECT Dist, Month([RTL_Date]) AS [Month],
Year([RTL_Date]) AS [Year], Count(*) AS ProjCount ,
(SELECT Count(*)
FROM WebCharts
WHERE Dist=WC.Dist
AND Year(RTL_Date) = Year(WC.RTL_Date)
AND Month(RTL_Date) <= Month(WC.RTL_Date)
) AS RunningCount
FROM WebCharts As WC
GROUP BY Dist, Month([RTL_Date]), Year([RTL_Date])
This will keep a running total for a year for each Dist. The total will
return to zero at the beginning of a new year or a new Dist, which ever
comes first. If you want to have the running total for all years then
change this:
AND Year(RTL_Date) = Year(WC.RTL_Date)
to this
AND Year(RTL_Date) <= Year(WC.RTL_Date)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQjez5oechKqOuFEgEQIRGwCgv8pYRMLoaZ8Bl2+MZzOQc5xIH3cAn0as
l/LXKnYs/u/uSiUYSD5bNQQQ
=Vy2Q
-----END PGP SIGNATURE-----
