Creating Fiscal Quarters and Year in queries

[sk88]

So far, in my query, I have a column for Quarters and under the field of that column I have put a statement:

Quarter: IIf(Month([VisitDate]) In (4,5,6),"1",IIf(Month([VisitDate]) In (7,8,9),"2",IIf(Month([VisitDate]) In (10,11,12),"3","4")))


The statement works fine but the problem is it does not tell me which year of does that quarter belongs to. Example: whether it is Quarter 1 of 1999 or Quarter 1 of 2000. Right now it only output either 1 or 2 or 3 or 4.

Therefore, is there a way where I can command to give me an output like "Quarter 1, 1999", "Quarter 2, 1999", "Quarter, 1, 2000". Basically with respect to the Year that I have in my data?

I hope someone knows a solution to it.
Thanks!

[Robeen]

Try Adding another field called 'QuarterYear' in Query Design - like this:

QuarterYear: [Quarter] & ", " & Year([YourDateField])

I tried it in a simple query & it worked.

[sk88]

Try Adding another field called 'QuarterYear' in Query Design - like this:

QuarterYear: [Quarter] & ", " & Year([YourDateField])

I tried it in a simple query & it worked.

Hi Robeen,

I tried that and it works well but still when I am running the query it would pull Quarter 4 of the previous year.

For example:
Cost Date Quarters Year
20 12/31/2009 3 2009
30 3/21/2010 4 2010
40 2/21/2009 4 2009
50 6/20/2009 1 2009




With the above example, if I run a query for 2009 only, Access will every event that happened in 2009. But 2/21/2009 is something that I do not need as it is Q4 for 2008



I hope you understood what I am trying to explain



Thanks

[rpeare]

Quarter: IIf(Month([VisitDate]) In (4,5,6),"Q1",IIf(Month([VisitDate]) In (7,8,9),"Q2",IIf(Month([VisitDate]) In (10,11,12),"Q3","Q4"))) & iif(cint(month(visitdate) ) >=7, datepart("yyyy", Visitdate)+1, datepart("yyyy", visitdate))

[Robeen]

If you want to control which dates show up, you can put a date range under your 'VisitDate' field in the 'Criteria' row in Query Design.

Try putting something like this in Criteria for VisitDate:
>#12/31/2009# And <#01/01/2011#

[sk88]

Quarter: IIf(Month([VisitDate]) In (4,5,6),"Q1",IIf(Month([VisitDate]) In (7,8,9),"Q2",IIf(Month([VisitDate]) In (10,11,12),"Q3","Q4"))) & iif(cint(month(visitdate) ) >=7, datepart("yyyy", Visitdate)+1, datepart("yyyy", visitdate))

Thanks rpeare!

I just tried that and it is giving me error. The error message says the following: "the expression you entered contains invalid syntax. You may have entered an operand without an Operator"

[sk88]

If you want to control which dates show up, you can put a date range under your 'VisitDate' field in the 'Criteria' row in Query Design.

Try putting something like this in Criteria for VisitDate:
>#12/31/2009# And <#01/01/2011#

Thanks Robeen! I would do that if I do not have any option left. I am hesitated to put that in to the 'criteria' row as I would have to put that each time I want to do some query.
I wanted in such a way where I can easily do cross tab query years after years without me going back to the design view and changing the YEAR.

[rpeare]

I have this table

tblTest
VisitDate
1/1/2011
2/1/2011
3/1/2011
...
11/1/2011
12/1/2011

This is the query I'm running

SELECT tblTest.VisitDate, IIf(Month([visitdate])<=3,"Q3",IIf(Month([visitdate])<=6,"Q4",IIf(Month([visitdate])<=9,"Q1","Q2"))) & "-" & IIf(Month([visitdate])>=7,DatePart("yyyy",[visitdate])+1,DatePart("yyyy",[visitdate])) AS Quarter
FROM tblTest;

And it's giving me the correct results.

[Robeen]

nice - I like how you did that rpeare!
I didn't realize that's what sk88 needed till I saw what you did.

[sk88]

I have this table

tblTest
VisitDate
1/1/2011
2/1/2011
3/1/2011
...
11/1/2011
12/1/2011

This is the query I'm running

SELECT tblTest.VisitDate, IIf(Month([visitdate])<=3,"Q3",IIf(Month([visitdate])<=6,"Q4",IIf(Month([visitdate])<=9,"Q1","Q2"))) & "-" & IIf(Month([visitdate])>=7,DatePart("yyyy",[visitdate])+1,DatePart("yyyy",[visitdate])) AS Quarter
FROM tblTest;

And it's giving me the correct results.

I am so sorry to what I am about to ask. I am pretty new to Access so I would need help. My question is -- Where do I insert that SQL code in the query? *embarrassed**

[rpeare]

You don't insert it into your query, you build it like I have (because I don't know the structure of your tables) Create a table with the same name I have with a field of the same name and put in the 1 day for each month of any given year, then save the SQL code I gave you as a query and run it

[Robeen]

sk88,

You can create a new query in Design View,
choose a Table,
and then click 'View' from the top left,
and then 'SQL View'

You will see the skeleton of an SQL statement in the SQL View window.

That's where you might put the SQL that rpeare posted.

[sk88]

You don't insert it into your query, you build it like I have (because I don't know the structure of your tables) Create a table with the same name I have with a field of the same name and put in the 1 day for each month of any given year, then save the SQL code I gave you as a query and run it

sorry! it works now! Thanks rpeare!!

[sk88]

sk88,

You can create a new query in Design View,
choose a Table,
and then click 'View' from the top left,
and then 'SQL View'

You will see the skeleton of an SQL statement in the SQL View window.

That's where you might put the SQL that rpeare posted.

Thanks again Robeen,
That was really helpful information!

[brownsugar7210]

I figured it out. You have been a tremendous help...Thank you so much!!!!