query by date

[trevor]

I would like to create a query that groups my data by month, qtr or whatever
time frame I choose and average my other fields in the query. For example I
have a series of depth readings for a number of lakes and would like to give
average depths over a month or qtr period.

 

[Ken Snell [MVP]]

Add a calculated field to a query that gives you the month, or quarter, or
whatever, and then use that field for grouping. For example:

MonthValue: Month([DateFieldName])

 

[trevor]

cheers,

I used that function to pull out the month and year and groupby them, great!
but I can't see how to pull out the quarter or any other random grouping eg
2months, 7 months or 2 years. Do I need to make a custom calculation? How
would I do this.

I have also used the DatePart function to similar effect.

Add a calculated field to a query that gives you the month, or quarter, or
whatever, and then use that field for grouping. For example:

MonthValue: Month([DateFieldName])

--

Ken Snell
<MS ACCESS MVP>

I would like to create a query that groups my data by month, qtr or whatever
time frame I choose and average my other fields in the query. For example I
have a series of depth readings for a number of lakes and would like to give
average depths over a month or qtr period.

 

[Ken Snell [MVP]]

You can use your own "custom" expression for those other things, if you know
the exact things you want. For example, to get the quarter (1, 2, 3, or 4):

QtrValue: ((Month([DateFieldName]) - 1) \ 3) + 1


--

Ken Snell
<MS ACCESS MVP>

cheers,

I used that function to pull out the month and year and groupby them, great!
but I can't see how to pull out the quarter or any other random grouping eg
2months, 7 months or 2 years. Do I need to make a custom calculation? How
would I do this.

I have also used the DatePart function to similar effect.

Add a calculated field to a query that gives you the month, or quarter, or
whatever, and then use that field for grouping. For example:

MonthValue: Month([DateFieldName])

--

Ken Snell
<MS ACCESS MVP>

I would like to create a query that groups my data by month, qtr or whatever
time frame I choose and average my other fields in the query. For

example
I

have a series of depth readings for a number of lakes and would like

to
give

average depths over a month or qtr period.

 

[trevor]

got ya.

works a treat.

You can use your own "custom" expression for those other things, if you know
the exact things you want. For example, to get the quarter (1, 2, 3, or 4):

QtrValue: ((Month([DateFieldName]) - 1) \ 3) + 1


--

Ken Snell
<MS ACCESS MVP>

cheers,

I used that function to pull out the month and year and groupby them, great!
but I can't see how to pull out the quarter or any other random grouping eg
2months, 7 months or 2 years. Do I need to make a custom calculation? How
would I do this.

I have also used the DatePart function to similar effect.

Add a calculated field to a query that gives you the month, or quarter, or
whatever, and then use that field for grouping. For example:

MonthValue: Month([DateFieldName])

--

Ken Snell
<MS ACCESS MVP>

I would like to create a query that groups my data by month, qtr or
whatever
time frame I choose and average my other fields in the query. For example
I
have a series of depth readings for a number of lakes and would like to
give
average depths over a month or qtr period.