Can the Group By function be brkoen down to a Quarterly basis?

[Guest]

Is it possible to use the Group By function to group results from a query on
a Quarterly basis (Jan - Mar, etc)? I have sucessfully used the Group By
function with Year and Month, but not Quarter.

I am using Access 2003 in cordination with some graphing software. I want
to illustrate the results I receive from a Select Count (*) query on a
quarterly basis into the graph. However I am having problems figuring our if
this is even possible.

Here is my current query:

sqlCmd.CommandText = _
"Select count(*), Year([Submit Date/Time]) " & _
"From Table1 [Submit Date/Time]>= " & strYTD1 & " " & _
"And [submit date/time]< " & strYTD2 & " " & _
"Group By Year([Submit Date/Time]) Order By Year([Submit Date/Time])"

When I change the "Year" text within the Group By Functions to "Quarter", I
receive an undefined function error message.

Any feedback on if this is possible or how I might get started on making
this work is appreciated!

Thanks,
Keith

 

[Guest]

You could give the following a try

sqlCmd.CommandText = _
"Select count(*), Switch(Month([Submit Date/Time])<4, 1, Month([Submit
Date/Time])<7, 2 ,Month([Submit Date/Time])<10, 3, Month([Submit
Date/Time])>9, 4) As Quarter" & _
"From Table1 [Submit Date/Time]>= " & strYTD1 & " " & _
"And [submit date/time]< " & strYTD2 & " " & _
"Group By Switch(Month([Submit Date/Time])<4, 1, Month([Submit
Date/Time])<7, 2 ,Month([Submit Date/Time])<10, 3, Month([Submit
Date/Time])>9, 4) Order By Switch(Month([Submit Date/Time])<4, 1,
Month([Submit Date/Time])<7, 2 ,Month([Submit Date/Time])<10, 3,
Month([Submit Date/Time])>9, 4)"

Hope This Helps
Gerald Stanley MCSD

 

[John Spencer (MVP)]

Take a look at the DatePart Function or try the Format function.

....
Group By Format([Submit Date/Time],"yyyyQ")

 

[Guest]

Thanks for the all the input! I will look at both solutions and let everyone
know how it turns out.

Thank You,
Keith

Take a look at the DatePart Function or try the Format function.

....
Group By Format([Submit Date/Time],"yyyyQ")

Is it possible to use the Group By function to group results from a query on
a Quarterly basis (Jan - Mar, etc)? I have sucessfully used the Group By
function with Year and Month, but not Quarter.

I am using Access 2003 in cordination with some graphing software. I want
to illustrate the results I receive from a Select Count (*) query on a
quarterly basis into the graph. However I am having problems figuring our if
this is even possible.

Here is my current query:

sqlCmd.CommandText = _
"Select count(*), Year([Submit Date/Time]) " & _
"From Table1 [Submit Date/Time]>= " & strYTD1 & " " & _
"And [submit date/time]< " & strYTD2 & " " & _
"Group By Year([Submit Date/Time]) Order By Year([Submit Date/Time])"

When I change the "Year" text within the Group By Functions to "Quarter", I
receive an undefined function error message.

Any feedback on if this is possible or how I might get started on making
this work is appreciated!

Thanks,
Keith

 

[Guest]

I went with the DatePart and it worked great! Below is an example of my
query in case any body searches these forums:

sqlCmd.CommandText = _
"Select count(*), DatePart('q',[Submit Date/Time]) " & _
"From table1 where [Submit Date/Time]>= " & strYTD1 & " " & _
"And [submit date/time]< " & strYTD2 & " " & _
"Group By DatePart('q',[Submit Date/Time]) Order By DatePart('q',[Submit
Date/Time])"

Thanks again for the advice!
Keith

Take a look at the DatePart Function or try the Format function.

....
Group By Format([Submit Date/Time],"yyyyQ")

Is it possible to use the Group By function to group results from a query on
a Quarterly basis (Jan - Mar, etc)? I have sucessfully used the Group By
function with Year and Month, but not Quarter.

I am using Access 2003 in cordination with some graphing software. I want
to illustrate the results I receive from a Select Count (*) query on a
quarterly basis into the graph. However I am having problems figuring our if
this is even possible.

Here is my current query:

sqlCmd.CommandText = _
"Select count(*), Year([Submit Date/Time]) " & _
"From Table1 [Submit Date/Time]>= " & strYTD1 & " " & _
"And [submit date/time]< " & strYTD2 & " " & _
"Group By Year([Submit Date/Time]) Order By Year([Submit Date/Time])"

When I change the "Year" text within the Group By Functions to "Quarter", I
receive an undefined function error message.

Any feedback on if this is possible or how I might get started on making
this work is appreciated!

Thanks,
Keith