Crosstab report

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Help

I need to create a summary report for task work on for
the year to date that is first group by department and
then group on their status counting all tasks that have
not been completed and by how long it has been worked on
for. For how long a task has been worked on I have group
then as less then three months, between 3 and 6 months
and over 6 months. I also need to show the sum of all
hours work on each task.

Tasks that departments are working on have status of A -
active, R - Review, S - Suppended or C-Complete.

My problem is I would like to show the report with the
three status as column headings as in a crosstab query
but how do I include the department column to group by
and Total the hours worked for each status in each
department.

Please help!
 
Please provide your table structure. How are we supposed to know "how long a
task has been worked on"? Can you type a few sample records (or paste) into
a reply as well as your desired final display in a report?
 
sorry of couse you would need to table structure.

Task Table has Task name, Startdate, TimeWorked( this is
type in my the user)Status and department is a lookup
from the department table.

Example would be
FINANCE
A R S
Less then 3 months 5 2 1
Between 3 - 6 7 1 1
Greater then 6 months 1 2 3
Total hours worked 48hr 63hr 10hr
 
You still didn't provide any sample records. Is the TimeWorked a numeric
field that stores the number of months? I don't see how you are calculating
the Total hours worked. Are you calculating Less then 3 months as tasks that
began less than 3 months ago?
 
The time worked is just a numeric field that is the total
time staff worked on each task. This is manual type in my
staff. The total I am trying to find is the sum of
timeworked for each status for each each department. ie
sum of time worked for each active task for each
department, sum of time worked for each supend task for
each department and sum of time worked for each review
task for each department.

For the months I used datediff(Date()-[StartDate]) and
then used If function to decide which of the three month
category it belongs to.

I can get the report to work if I list the Status down
the page but I do not how to get it to work if I list the
Status across the page and include the extra
information.
 
Create your crosstab with Status as the Column Heading and then add all the
possible Status values into the Column Headings property of the query.
Use Count of Task as the Value and set the Row Heading to an expression:
Switch(datediff("m", [StartDate], Date()) < 3 ,"Less then 3 months",
datediff("m", [StartDate], Date())<=6,"3-6 Months",-1,"Greater Than 6
months")

--
Duane Hookom
MS Access MVP


The time worked is just a numeric field that is the total
time staff worked on each task. This is manual type in my
staff. The total I am trying to find is the sum of
timeworked for each status for each each department. ie
sum of time worked for each active task for each
department, sum of time worked for each supend task for
each department and sum of time worked for each review
task for each department.

For the months I used datediff(Date()-[StartDate]) and
then used If function to decide which of the three month
category it belongs to.

I can get the report to work if I list the Status down
the page but I do not how to get it to work if I list the
Status across the page and include the extra
information.
-----Original Message-----
You still didn't provide any sample records. Is the TimeWorked a numeric
field that stores the number of months? I don't see how you are calculating
the Total hours worked. Are you calculating Less then 3 months as tasks that
began less than 3 months ago?

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
Thanks Duane.

That worked great. You make look so easy

-----Original Message-----
Create your crosstab with Status as the Column Heading and then add all the
possible Status values into the Column Headings property of the query.
Use Count of Task as the Value and set the Row Heading to an expression:
Switch(datediff("m", [StartDate], Date()) < 3 ,"Less then 3 months",
datediff("m", [StartDate], Date())<=6,"3-6 Months",- 1,"Greater Than 6
months")

--
Duane Hookom
MS Access MVP


The time worked is just a numeric field that is the total
time staff worked on each task. This is manual type in my
staff. The total I am trying to find is the sum of
timeworked for each status for each each department. ie
sum of time worked for each active task for each
department, sum of time worked for each supend task for
each department and sum of time worked for each review
task for each department.

For the months I used datediff(Date()-[StartDate]) and
then used If function to decide which of the three month
category it belongs to.

I can get the report to work if I list the Status down
the page but I do not how to get it to work if I list the
Status across the page and include the extra
information.
-----Original Message-----
You still didn't provide any sample records. Is the TimeWorked a numeric
field that stores the number of months? I don't see
Click to expand...
how
you are calculating
the Total hours worked. Are you calculating Less then
Click to expand...
3
months as tasks that
began less than 3 months ago?

--
Duane Hookom
MS Access MVP


sorry of couse you would need to table structure.

Task Table has Task name, Startdate, TimeWorked(
Click to expand...
this
is
type in my the user)Status and department is a lookup
from the department table.

Example would be
FINANCE
A R S
Less then 3 months 5 2 1
Between 3 - 6 7 1 1
Greater then 6 months 1 2 3
Total hours worked 48hr 63hr 10hr

-----Original Message-----
Please provide your table structure. How are we supposed
to know "how long a
task has been worked on"? Can you type a few sample
records (or paste) into
a reply as well as your desired final display in a
report?

--
Duane Hookom
MS Access MVP


message
Help

I need to create a summary report for task work
Click to expand...
on
for
the year to date that is first group by
Click to expand...
department
and
then group on their status counting all tasks
Click to expand...
that
have
not been completed and by how long it has been worked
on
for. For how long a task has been worked on I have
group
then as less then three months, between 3 and 6 months
and over 6 months. I also need to show the sum of all
hours work on each task.

Tasks that departments are working on have status of
A -
active, R - Review, S - Suppended or C-Complete.

My problem is I would like to show the report
Click to expand...
with
the
three status as column headings as in a crosstab query
but how do I include the department column to
Click to expand...
group
by
and Total the hours worked for each status in each
department.

Please help!



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top