Grouping a report by month

[jbelf]

Hi - first, thanks to all for the consistent great advice!.

Now, the big Q...

I'm trying to create a report that captures how many projects were opened, closed and canceled in a given month, grouped by each month in the year.

Fields I have include start date, end date and status.

I can create a report that will group each month by either start date or end date, but my concern is that the counts in each grouping won't be accurate. For example, if I have a group based on 'start date' that will include only months where a new project was 'started'. Does that make sense?

What I'd ideally like to do is have the report grouped by month, but based on values that aren't specific to data. Just a list of the 12 months, with my data grouped within there.

Any thoughts??

Many thanks!

 

[scriverb]

First of all the status is a concern. Obviously, the Opened status would be associated with the StartDate month/year. And, conversely the Cancelled or Closed status would be associated with the EndDate. What you are looking for is an individual record count of StartDate month/Year and a seperate individual record Count of Cancelled or Closed based on EndDate month/year. If this is what you want try using the following UNION query:

Select A.StartDate, A.EndDate, Year(A.[StartDate]) & Month(A.[StartDate]) as YearMonth, A.Status
FROM tblYourTableName as A
WHERE A.Status = "Opened"
UNION
Select A.StartDate, A.EndDate, Year(A.[EndDate]) & Month(A.[EndDate]) as YearMonth, A.Status
FROM tblYourTableName as A
WHERE A.Status = "Cancelled"
UNION
Select A.StartDate, A.EndDate, Year(A.[EndDate]) & Month(A.[EndDate]) as YearMonth, A.Status
FROM tblYourTableName as A
WHERE A.Status = "Closed";

Save this query as qryOpenedCancelledAndClosed

Now let's create another Select query using this query as input and perform a Totals and a Sort to put the data in the order you need for your report.

Select A.[YearMonth], A.[Status], Sum(1) as StatusCount
FROM qryOpenedCancelledAndClosed as A
Group By A.[YearMonth], A.[Status]
Order By A.[YearMonth], A.[Status] Desc;

Since I don't have data to test this I will have to rely on you doing that. Could be a few typos but just let me know if you have any problems.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[yulius]

Add 1 more field on your record source on the report
, called : Mth : month(startdate)

and add Sort and group by on the report, Mth (Group header =yes)


Hope it work


Yulius