12 Month Report

[Rags]

Looking thru these posts and not quite figuring out the best way to do what
I'm trying to do. I'd like to create a report (that measures analyst
productivity) that is a rolling 12 month (from the current month) report that
has the months as the column headings and grouped by analyst. I also have
about 15 categories that I would like to be shown in the 1st column going
from top to bottom. I'd like the report to show each category for each person
for each of the last 12 months. Sort of like this:

Analyst Name Mar06 Feb06 Jan06 Dec05 Nov05 Oct05 ........
Category 1 # # # # # #
Category 2 # # # # # #
Category 3 # # # # # #
Category 4 # # # # # #
..
..
Category 15 # # # # # #

There is a record in the database for each user for each month and each
category.

I've read about subreports and crosstab queries but getting kind of lost.

Any help would be appreciated to get me in the right direction.

Thanks.

 

[Allen Browne]

Crosstab query will be the go here. Steps:

1. Create a query using this table.

2. Change it to a Crosstab query (Crosstab on Query menu.)
Access adds a Crosstab row to the grid.

3. Drag the Analyst and Category fields into the grid.
Access Group By in the Total row, and choose Row Heading in the Crosstab row
under these fields.

4. Type this expression into the Field row in the next column:
MonthsAgo: DateDiff("m",Date(),[OrderDate])
Replace OrderDate with the name of your date/time field.
This expression will give 0 for the current month, -1 of last month etc.
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.
Choose Descending in the Sort row.

5. Drag your amount/number field in to the grid in the next column.
In the Total row under this field, choose Sum.
In the Crosstab row, choose Value.

6. Drag the date field into the grid again.
In the Total row, choose Where
In the Criteria row, enter:
Between DateSerial(Year(Date()-1),Month(Date()),1) And Date()
This limits the query to the last 12 months.

7. Open the Properties box (View menu.)
Looking at the properties of the query (not of a field), set the Column
Headings property to:
0,-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11
This ensures the query generates a column even if there is no data. (This is
necessary, as the report will look for the field.)

8. Save and test the query. You should see the analyst and categories down
the left, the numbers 0 to -11 (representing the number of months ago) as
column headings, and the value in the matrix.

9. Build a report based on this query. Use text boxes as the "labels" over
the column, and set properties like this:
ControlSource: DateAdd("m", -1, Date())
Format: mmmyy

 

[Rags]

Will try that. Thank you very much.
--
Rags

Crosstab query will be the go here. Steps:

1. Create a query using this table.

2. Change it to a Crosstab query (Crosstab on Query menu.)
Access adds a Crosstab row to the grid.

3. Drag the Analyst and Category fields into the grid.
Access Group By in the Total row, and choose Row Heading in the Crosstab row
under these fields.

4. Type this expression into the Field row in the next column:
MonthsAgo: DateDiff("m",Date(),[OrderDate])
Replace OrderDate with the name of your date/time field.
This expression will give 0 for the current month, -1 of last month etc.
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.
Choose Descending in the Sort row.

5. Drag your amount/number field in to the grid in the next column.
In the Total row under this field, choose Sum.
In the Crosstab row, choose Value.

6. Drag the date field into the grid again.
In the Total row, choose Where
In the Criteria row, enter:
Between DateSerial(Year(Date()-1),Month(Date()),1) And Date()
This limits the query to the last 12 months.

7. Open the Properties box (View menu.)
Looking at the properties of the query (not of a field), set the Column
Headings property to:
0,-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11
This ensures the query generates a column even if there is no data. (This is
necessary, as the report will look for the field.)

8. Save and test the query. You should see the analyst and categories down
the left, the numbers 0 to -11 (representing the number of months ago) as
column headings, and the value in the matrix.

9. Build a report based on this query. Use text boxes as the "labels" over
the column, and set properties like this:
ControlSource: DateAdd("m", -1, Date())
Format: mmmyy

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Looking thru these posts and not quite figuring out the best way to do
what
I'm trying to do. I'd like to create a report (that measures analyst
productivity) that is a rolling 12 month (from the current month) report
that
has the months as the column headings and grouped by analyst. I also have
about 15 categories that I would like to be shown in the 1st column going
from top to bottom. I'd like the report to show each category for each
person
for each of the last 12 months. Sort of like this:

Analyst Name Mar06 Feb06 Jan06 Dec05 Nov05 Oct05 ........
Category 1 # # # # # #
Category 2 # # # # # #
Category 3 # # # # # #
Category 4 # # # # # #
.
.
Category 15 # # # # # #

There is a record in the database for each user for each month and each
category.

I've read about subreports and crosstab queries but getting kind of lost.

Any help would be appreciated to get me in the right direction.

Thanks.

 

[Rags]

Having some trouble understanding the value field for this query. Not sure
what to use. Each of the 15 categories is a different numerical measurement
(ex. Financial accuracy, Success factors, attendance etc.). They aren't tied
into each other. I need all 15 categoreis to print for each month for each
analyst. There can only be 1 value field in the query but I'm not
understanding which field(s) to use for this particular setup. Basically this
is a monthly progress report for each analyst to show them how they are doing
over the past 12 months on their job performance based on pre-defined
measurements.


Analyst Name Mar06 Feb06 Jan06 Dec05 Nov05 Oct05 ........
Financial Accuracy # # # # # #
Success Factors # # # # # #
Attendance # # # # #
#
Time Off Earned # # # # # #
..
..

Hope that is clearer. Any other help would still be appreciated.

Thanks.

 

[Allen Browne]

Presumably your table has fields like this:
Analyst
Category
Date
Score

Analyst and Category are row headings, which gives you every category under
every analyst.

The Date field gives you the column headings, using the expression
suggested.

The Score field gives you the value. If this is only one score for each
combination of Analyst + Category + Date, you can use First in the Total
row, since the first thing Access finds will the the only one.

 

[Rags]

Database has 4 tables - Analyst, Date, Productivity, Supervisor. Productivity
contains 15 different numerical measurements. (Financial accuracy, Team
Player, Monthly productivity etc.) which are each based on the supervisor
rating of the employee for the month. My boss would like a monthly progress
report for each analyst to be printed at the beginning of each new month
showing them the last 12 months ratings.

The report would have the months on the top and the 15 different field
categories down the left hand side with each rating for each month. These are
not the same fields which is where my problem with the value field in the
crosstab query is happening. Is this scenario possible? I can set up the
report differently but my boss would really like all 12 months on the same
page.

Hopefully you understand this now. Sorry for the confusion.

Any ideas?

Thanks.

 

[Allen Browne]

The report is doable provided you build a normalized table structure.

Are you saying you have 15 *fields* in the Productivity table for these
scores? If so, that needs changing. You need tables like this:

1. Analyst table (one record for each staff member, with AnalystID primary
key)

2. ProductivityType table (15 records for the 15 types, with
ProductivityTypeID p.k.)

3. Evaluation table (one record for each time staff member each month):
EvaluationID AutoNumber primary key
AnalystID Number the analyst being reviewed.
EvaluationDate Date/Time first of the month

4. EvaluationDetail table (one record for each item in the review):
EvaluationID Number which evaluation this row belongs
to.
ProductivityTypeID which item is being measured
Score Number what score the person received
this time on this item.

The interface will be:
- a main form bound to the Evaluation form, with a combo for picking the
Analyst.
- a subform bound to the EvaluationDetail table, with a combo for picking
the ProductivityTypeID.

You can then generate the report you asked for. The query will contain all 4
of those tables, with:
- AnalystID as Row Heading;
- ProductivityTypeID as Row Heading;
- The expression based on EvaluationDate as the Column Heading;
- Score as the Value.