grouping by month

  • Thread starter Thread starter Colin Basterfield
  • Start date Start date
C

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
 
You can create a column in your query like:
YrMth: Format([YourField], "yyyymm")
Then group by this calculated column.
 
Duane,

thanks for that, it works...

Colin B

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

--
Duane Hookom
MS Access MVP


Colin Basterfield said:
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
Click to expand...
Click to expand...
 
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.
Click to expand...

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.
 
Back
Top