Date grouping by month

[deejay_totoro]

Dear all,

I have some date data that I need to manipulate and change. Date information has been entered as:

01/01/2004 50
05/01/2004 50
17/01/2004 50
12/02/2004 100
22/02/2004 100
17/03/2004 100

etc...

When I run a cross-tab query and group on the date, it treats individual dates separately. What I would like to see is all the data for one month calculated together. The above example would then be:

Jan 04 150
Feb 04 300

One way I can think off is the change all the dates to the first of each month? (How might I do that?)

Hope someone can help!

Thanks!

dj_T

 

[bradcccs]

Definitely do not change the underlying dates. Obviously these dates are relevant. Always store the most detail possible as you can always "tweak" the display later.

In the Field for your query, rather than using [YourDateField], you could use:

FormattedDate: Format([YourDateField],"mmm yyyy")

This will remove the leading dd portion from your date and allow for grouping as required.

HTH