grouping quarters

[fishqqq]

HI can someone please tell me how to create a query that will group
the record [date] by calendar months & quarters ?

i will have the the [date] field in the query but i would like the
select query calculate how many records there were in each month of
the year. and also each quarter of the year.

Is this possible?

tks in advance

 

[John W. Vinson]

HI can someone please tell me how to create a query that will group
the record [date] by calendar months & quarters ?

i will have the the [date] field in the query but i would like the
select query calculate how many records there were in each month of
the year. and also each quarter of the year.

Is this possible?

tks in advance

It's simpler to use two queries, but you certainly can group by them; for
instance, you can use the builtin Month([date]) function to extract the month
number (1-12) and group by it, and/or use

Q: Format([date], "q")

to extract the quarter 1-4.

In a Report, you can include both fields, and use the sorting and grouping
dialog of the Report to create separate levels of group headers and footers.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[fishqqq]

HI can someone please tell me how to create a query that will group
the record [date] by calendar  months & quarters ?

i will have the  the [date] field in the query but i would like the
select query calculate how many records there were in each month of
the year. and also each quarter of the year.

Is this possible?

tks in advance

It's simpler to use two queries, but you certainly can group by them; for
instance, you can use the builtin Month([date]) function to extract the month
number (1-12) and group by it, and/or use

Q: Format([date], "q")

to extract the quarter 1-4.

In a Report, you can include both fields, and use the sorting and grouping
dialog of the Report to create separate levels of group headers and footers.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

This works well except it doesn't differentiate Q1 2009 from Q! 2010 -
is there a way to have the field separate like this?

tks

 

[John W. Vinson]

This works well except it doesn't differentiate Q1 2009 from Q! 2010 -
is there a way to have the field separate like this?

tks

Sure; just include the year in the format:

Format([date], "\Qq yyyy")

You'll want to sort by [date] not by this formatted expression (otherwise it
will sort all the Q1's before the Q2's)
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com