Create report converting year to months

[fergler]

I'm trying to create a report that takes an activity occuring over a specific period of time and show the individual months it applies to. For example, I have a person who has committed 50% of his time to a project with an effective start date of 1/1/07 and an effective end date of 12/31/07. I would like to be able to show the individual months in the report, along with the committment as below:

1/1/07 2/1/07 3/1/07 4/1/07 5/1/07 6/1/07
50% 50% 50% 50% 50% 50%

Is there a way to break the individual months out from the effective start and end date?

I hope my request makes sense.

Jennifer

 

[sportsguy]

yes, I do it all the time. The way that I have all my tables set up is by Fiscal Year Fiscal month, since my fiscal year ends in September. So all my tables have a field YYYYMM, since the dates are not the same year over year, nor do they equate to any particular calendar dates. So in your case, YYYY = Calendar year, MM = calendar month. the current month would be 200703

Now you need a table that stores the YYYYMM and date parameters:

mine is called tblPERIODS

YYYYMM Long Integer
strDescr Text
StartDate Date
EndDate Date

The key is that the EndDate has to include the last hour:min:sec with the date.

then in your queries, you add tblPERIODS to the query, but do not create any joins. Suppose the date field that you want to evaluate into YYYYMM is called anyDate

in the query, you add the YYYYMM field from tblPERIODS,
in the where clause, you add (tblPERIODS.StartDate <= anyDate) AND
(tblPERIODS.EndDate >= anyDate) and the query will pick the correct YYYYMM for you every time.

Now, why this is great is that you can create monthly trend reports that you can always get to the correct period title, always in the correct sequence from, with a bit of VB magic. . . I have reports that you select any YYYYMM you want, and the report creates a report in proper trend sequence for this month and the prior 12 months, without any cross tab queries. It runs much faster than a cross tab, and much less headache. . .

so create the table, and give the query a try.

If you can do that, then you can create the trend report with one VB function.

sportsguy

 

[sportsguy]

Added Sample Record

yes, I do it all the time. The way that I have all my tables set up is by Fiscal Year Fiscal month, since my fiscal year ends in September. So all my tables have a field YYYYMM, since the dates are not the same year over year, nor do they equate to any particular calendar dates. So in your case, YYYY = Calendar year, MM = calendar month. the current month would be 200703

Now you need a table that stores the YYYYMM and date parameters:

mine is called tblPERIODS

YYYYMM Long Integer
strDescr Text
StartDate Date
EndDate Date

The key is that the EndDate has to include the last hour:min:sec with the date.

Example:

200701 Jan 07 1/1/2007 1/31/2007 11:59:59 PM

then in your queries, you add tblPERIODS to the query, but do not create any joins. Suppose the date field that you want to evaluate into YYYYMM is called anyDate

in the query, you add the YYYYMM field from tblPERIODS,
in the where clause, you add (tblPERIODS.StartDate <= anyDate) AND
(tblPERIODS.EndDate >= anyDate) and the query will pick the correct YYYYMM for you every time.

Now, why this is great is that you can create monthly trend reports that you can always get to the correct period title, always in the correct sequence from, with a bit of VB magic. . . I have reports that you select any YYYYMM you want, and the report creates a report in proper trend sequence for the selected month and the prior 12 months, without any cross tab queries. It runs much faster than a cross tab, and much less headache. . .

so create the table, and give the query a try.

If you can do that, then you can create the trend report with one VB function.

sportsguy
EDUIT function did not work. . . sorry mods for posting twice