N00b needs help!!!

[JaySquared]

I am creating a query which pulls data from my table and displays a count of the data depending on the month they fall in.

I need to pull dates from beginning of FY to end (April - March).

I have this for april:

SELECT [Project Table].[Handover or Closed Date], Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
GROUP BY [Project Table].[Handover or Closed Date], Year([Handover or Closed Date]), DatePart("m",[Handover or Closed Date])
HAVING (((Year([Handover or Closed Date]))=Year(Now())-1) AND ((DatePart("m",[Handover or Closed Date]))=4));

Which will work fine up until April 2014, as the "Year(Now())-1)" will ensure the query returns values for April 2013!!

How do I work around this to show April 2014 after March 31st 2014??

 

[CJ_London]

you need to create a formula which calculates your financial year based on today

So for example

year(date())+month(date())<=3

so if the current year/month is march 2014 it will return 2013, otherwise it will return 2014

but you will also need to apply this to your Handover or Closed Date field so the code becomes

HAVING year([Handover or Closed Date])+month([Handover or Closed Date])<=3=Year(Date())+month(Date()<=3

 

[namliam]

Wouldnt you want this query to read:

SELECT Year([Handover or Closed Date]) as FYYear
      , DatePart("m",[Handover or Closed Date]) as FYMonth
      , Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
GROUP BY Year([Handover or Closed Date]), DatePart("m",[Handover or Closed Date])
HAVING (((Year([Handover or Closed Date]))=Year(Now())-1) 
    AND ((DatePart("m",[Handover or Closed Date]))=4));

The having dont make sence either IMHO.... why not simply list the year/month as per above and "adjust" the year kindoff like so:

SELECT Year(Dateadd("M", -3, [Handover or Closed Date])) as FYYear
      , DatePart("m",[Handover or Closed Date]) as FYMonth
      , Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
GROUP BY Year(Dateadd("M", -3, [Handover or Closed Date])) , DatePart("m",[Handover or Closed Date])
HAVING (((Year([Handover or Closed Date]))=Year(Now())-1) 
    AND ((DatePart("m",[Handover or Closed Date]))=4));

This shifts the year back 3 months, faking March 2014 into "March 2013"

I will forgo the usual, you shouldnt use spaces in column names speech :banghead:

 

[JaySquared]

Hi namliam,

This looks like what I need.

Excuse me but I am totally new to access and SQL in general.

Have tried using your second code but access returns warning: 'you tried to execute a query '...' as part of an aggregate function.'

Not sure what this means??

PS. thank you for sparing me 'the spaces in names talk'.. I dont know why but I like spaces and they have worked for me so far

 

[namliam]

spaces work, but if you get to used to it... it wont work in most other databases and give you grief there.... Not to mention the need for [] and the like nonsense which can give you grief as well.

SELECT Year(Dateadd("M", -3, [Handover or Closed Date])) as FYYear
      , DatePart("m",[Handover or Closed Date]) as FYMonth
      , Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
GROUP BY Year(Dateadd("M", -3, [Handover or Closed Date])) 
            , DatePart("m",[Handover or Closed Date])
HAVING (((Year(Dateadd("M", -3, [Handover or Closed Date])))=Year(Now())-1) 
    AND ((DatePart("m",[Handover or Closed Date]))=4));

I am guessing the problem is in the Having so I changed that a little bit...

If that too fails I will need a copy of your database to debug it. or you can try fiddling with it yourself.

 

[JaySquared]

Thanks namliam,
That's great but I need the query to only show dates in the current financial year. Excluding all other dates. This did not seem to work.

Cant figure out why this doesn't work:

SELECT [Project Table].[Handover or Closed Date], Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
GROUP BY [Project Table].[Handover or Closed Date], [Project Table].[Handover or Closed Date]
HAVING ((([Project Table].[Handover or Closed Date]) 
BETWEEN (([Project Table].[Handover or Closed Date])=Year(Now())-1 And DatePart("m",[Handover or Closed Date])>=4) And (([Project Table].[Handover or Closed Date])=Year(Now()) And DatePart("m",[Handover or Closed Date])<4)));

 

[CJ_London]

@JaySquared - Did you try my suggestion in post #2?

 

[namliam]

to show the current fiscal year I usually trick a bit using Dateadd("M", -3, Date())

Where Year([Handover or Closed Date]) = Year(Dateadd("M", -3, Date()))

 

[JaySquared]

Thanks so much namliam.

So glad that there are communities like this for novices like me

My final code is:

SELECT [Project Table].[Handover or Closed Date], Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
WHERE Year([Handover or Closed Date]) = Year(Dateadd("M", -3, Date()))
GROUP BY [Project Table].[Handover or Closed Date], Year([Handover or Closed Date]), DatePart("m",[Handover or Closed Date])
HAVING (((Year([Handover or Closed Date]))=Year(Now())-1) AND ((DatePart("m",[Handover or Closed Date]))=4));

This works just as it should because when I change my date on windows to 1st April 2014, query does not show any results. Spot on.

 

[JaySquared]

@CJ_London. I tried this but it was not correct. Doesn't limit my query to the financial year just displays all dates before April.

Thanks for your suggestion though.

JaySquared.

 

[namliam]

The where and the having are kindoff double here, suggest you move the where to the having and remove the current having

 

[JaySquared]

I also realised that my query doesn't work as it should after new years day so added an OR function to the 'WHERE Year' part:

SELECT [Project Table].[Handover or Closed Date], Count([Project Table].[Handover or Closed Date]) AS [CountOfHandover or Closed Date]
FROM [Project Table]
WHERE (((DatePart("m",[Handover or Closed Date]))=4) AND ((Year([Handover or Closed Date]))=Year(DateAdd("m",-3,Date()))) AND ((Year([Handover or Closed Date]))=Year(Now()) Or (Year([Handover or Closed Date]))=Year(Now())-1))
GROUP BY [Project Table].[Handover or Closed Date], Year([Handover or Closed Date]);

Very happy with this now. Thanks again Mailman

 

[namliam]

I am glad you are happy and probably shouldnt be pushing a dead horse... but...
Perhaps bar the fact that you may want a specific month, ie. the part of:
1)

((DatePart("m",[Handover or Closed Date]))=4)

However if you want a FY, then why only select a specific month?

These two parts are "kind off" duplicating
2)Select current fiscal year (2013 in this case)

((Year([Handover or Closed Date]))=Year(DateAdd("m",-3,Date())))

3) Select this year or last year

AND (
           (Year([Handover or Closed Date]))=Year(Now()) 
        Or (Year([Handover or Closed Date]))=Year(Now())-1
          )

So if you only want the fiscal year you only need part 2)
If you want a specific month, you also need 1)...
However part 3) is most definately not needed at all and only convolutes the query.

Mailman

WTF is this, someone actually reads my footnote and gets the pun?

 

[JaySquared]

The 'dead horse' being me I take it??

You're not far from the truth and I have quickly seen the error of my ways as I am getting last FY years results also. URRRGH!!!

I do need the specific months, as I plan to graph these to show the rate of change over the FY.

In simple terms I want the number of 'Handovers' for each month over the current FY only.

When the FY ends I need my query to begin all over again from scratch.

I'm sure you are giving me enough to go on but just cant seem to get my head around this one.:banghead:

WTF is this, someone actually reads my footnote and gets the pun?

If you are a postal worker, with the birth name of Liam, this is just sublime Lol.

 

[namliam]

The 'dead horse' being me I take it??

No didnt mean to upset you and surely didnt mean you. Dead horse in this case revering to "perfecting a solution that has already been solved"

All the sql you should need is just the 2) from what I posted earlier...
and a grouping/select on year month... Something along the lines of:

SELECT Year([Handover or Closed Date]) as raey
     , month([Handover or Closed Date]) as htnom 
     , Count([Project Table].[Handover or Closed Date]) AS tnuoc 
FROM   [Project Table]
WHERE  Year([Handover or Closed Date])=Year(DateAdd("m",-3,Date()))
GROUP BY Year([Handover or Closed Date])
       , month([Handover or Closed Date])

Do note that if you run this query on April first, it will not yield any information.

 

[JaySquared]

I think that is exactly what I need... in just a few lines of code.. WOW.

I have wrote around 13 queries to try and get the same results. I feel pathetic

I have been using the GUI previously but don't think I will go back to it. SQL is far superior!!

Knaht Uoy Namliam!!

 

[namliam]

I stilll use the GUI/query designer many times every day, but a good understanding of SQL can help you in a lot of situations

Also having code readable and spaced so its easy on the eyes helps a lot, when you get stuck with something or want to show your code to others...

 

[JaySquared]

Thank you I will take your advice on board.

Just one more thing to help my understanding of SQL - how does the query know to select dates within the current financial year?

You did not specify year([Handover or Closed Date]) = Year(Date()).

So how does the query manage to do this. It is brilliant!!

 

[namliam]

You did not specify year([Handover or Closed Date]) = Year(Date()).

But I did?

WHERE Year([Handover or Closed Date])=Year(DateAdd("m",-3,Date()))

 

[JaySquared]

Ok the only problem I have now is that the query only returns values for one year:

raey	htnom tnuoc
2013	1	11
2013	2	13
2013	3	17
2013	4	20
2013	5	21
2013	6	25
2013	7	31
2013	8	10
2013	9	21
2013	10	15
2013	11	18
2013	12	6

and also includes Jan-March of that year instead of the following year.

I need to have Jan-March 2014 not 2013.