grouping by month

[Colin Basterfield]

Hi,

I am trying to generate a query which groups by month, where the month has
to be extracted from a Date/Time field in a table, my knowledge of SQL is
good, but not how to achieve it in Access...

Any ideas?

TIA
Colin B

 

[Duane Hookom]

You can create a column in your query like:
YrMth: Format([YourField], "yyyymm")
Then group by this calculated column.

 

[Colin Basterfield]

Duane,

thanks for that, it works...

Colin B

You can create a column in your query like:
YrMth: Format([YourField], "yyyymm")
Then group by this calculated column.

--
Duane Hookom
MS Access MVP

Hi,

I am trying to generate a query which groups by month, where the month has
to be extracted from a Date/Time field in a table, my knowledge of SQL is
good, but not how to achieve it in Access...

Any ideas?

TIA
Colin B

 

[John Vinson]

Thanks for that, and it works, as you would no doubt expect, but you're
right about the alpha sort when you use "mmm-yyyy" it doesn't come out in
chrono order, any ideas how to get around that? Ill have aplay and see if I
can do it some way now I have a better idea.

Include the date field twice: once using the Format yyyymm (or some
other format giving a text string which does sort chronologically),
and ALSO in a separate field include the date field itself. Set that
field's format to mmm-yyyy. Uncheck the "show" checkbox on the yyyymm
field (it's only there for grouping and sorting) and leave it on for
the "human readable" field.