Is it Possible To add what quarter to an existing Query

[ardy]

Hello All.
I have an existing query that will give me the number of dates for given month and year in Fiscal Calendar Year.
QUERY:

SELECT Count(Sch_Sampling_Date.Sch_Sampling_Collection_Date) AS CountOfSch_Sampling_Collection_Date, Year([Sch_Sampling_Collection_Date]) AS CalenderYear, Month([Sch_Sampling_Collection_Date]) AS CalenderMonth, IIf([CalenderMonth]<6,[CalenderYear]-1,[CalenderYear]) AS FY
FROM Sch_Sampling_Date
GROUP BY Year([Sch_Sampling_Collection_Date]), Month([Sch_Sampling_Collection_Date]), IIf([CalenderMonth]<6,[CalenderYear]-1,[CalenderYear]);

I have been stuck on two things........
1- Every time I run this query I get the Enter Parameter Value input dialog box for CalendarMonth and CalenderYear, I just puxh ok and the query runs. Is there a way to not get Enter Parameter Value Box...?

2- would I be able to add in column 5 that gives me which Quarter it is i.e Q1, Q2 etc.....(See Query-2.jpg)

Thnak You All

 

[CJ_London]

you can't reference a calculated value by its alias when grouping

try

IIf(Month([Sch_Sampling_Collection_Date])<6,Year([Sch_Sampling_Collection_Date]) -1,Year([Sch_Sampling_Collection_Date]))

or more simply

Year([Sch_Sampling_Collection_Date]) +Month([Sch_Sampling_Collection_Date])<6

 

[ardy]

Thanks....... learn new everyday....... No more dialog box......

Is there a way of adding the 5th column......

 

[CJ_London]

what is your starting quarter? Jan?

 

[ardy]

Fiscal starts:
7/1/2016 - 9/30/2016 = Q1
10/1/2016 - 12/31/2016 = Q2
1/1/2017 - 3/31/2017 = Q3
4/1/2017 - 6/30/2017 = Q4

 

[CJ_London]

try

datepart("q",dateadd("m",-6,[Sch_Sampling_Collection_Date]))

 

[ardy]

CJ,
This works great.... Works like a charm........

Thank you.......