Converting a Date into Months

[Joseph31]

Ok, This might sounds simple to you, but I have been tring to figure this one out:

I have a field on my database called "Date_Created". This date is on every record. On every record I have fields like Purchased / not Purchased.

I need to make a report that groups each record by month:

Janurary

Feburary

March

April

May

etc...

On each month it has a count of how many records were purchased.

How do you get the report to list months?

Can some one help me figure this out.

Joseph

Edited October 26, 2005 by Guest

[Breezer]

do you want to group by date created or the purchased date? If by date created, I would create a new calculation field that gets the month of the date created. Let's call this field "MonthCreated". The calculation field is

"MonthCreated = MonthName (Date Created)"

and make this stored.

Create your report and use MonthCreated as your break field in sub-summary when sorted by MonthCreated.

I am sure somebody else has a better way but this is how I would approach it.

[LaRetta]

The thing is ... by using Month Name alone, it will not sort properly, by either Month Name or Year. You can circumvent this by converting each date to another date - the FIRST day of each month. Then sorting on this date will keep records in correct order (including year). I learned this trick from Comment.

Calculation (result of date) would be:

date - Day(date) + 1

This makes every record the first day of the month and thus 'groups' them. Place this calc as leading part and sort on it. Change its date display (right-click calc field) to only display month and year by using Custom format.

LaRetta :wink2:

[Joseph31]

What would be the full Calculation for this?

Also, Is there a way to list every month on a report even though it might not have any data to it?

Example:

Month Files

January 5

Feburary 10

March 1

April None

May 3

[LaRetta]

The full calc was listed. It's: date - Day(date) + 1

Listing a filler record is another issue. Since you only want the Month and its resulting count AND since you want a filler record for month, I might suggest another approach here. But I don't know enough about your structure nor about your future reporting needs. I think you will find that this will come up repeatedly for you ... the need to group records by day/week/month/quarter/year. AND the need to show the 'holes' also.

To display 'holes' in a Report, your holes must be (I believe) records. Which means one record for each month/year combination. Our reporting needs require displaying records as rows, with each 'group' (month, quarter, year etc) as columns (which shows the blanks). So we went with a dynamic relationship based upon these groups which eliminates the need for Month/Year filler records. Whether you need this complexity (and flexibility) or not, only you can decide.

Easy way out ... tell them to get over it; that if no records exist for a month, the month will not display. Medium way out ... create one record for every single month year combination. It works but I've found that they'll next ask for quarter groups (or weekly groups etc). And you'll be having to go back and create records for all the quarters, etc. It will get on your nerves. Best way out ... take the time to learn and create a good cross-tab functionality (* using summary count) or even Count(). If you are interested in the third option (best way out), I can point you to some great threads on the subject.

* vs. 8 summary behavior may vary from vs. 7. vs. 7 allowed using summaries in a cool way (undocumented feature) which vs. 8 may not allow. I have read a few threads which imply different behavior). I have not tested it yet.

LaRetta