Grouping

  • Thread starter Thread starter FlyBoy
  • Start date Start date
F

FlyBoy

In Access 2002, I'm writing a report based on a query
that hits
two linked MS Project Server 2003 SQL tables,
dbo_MSP_PROJECTS
and dbo_MSP_Tasks. They are joined at the PROJ_ID field.

The report I'm writing has to semi-emulate the outlining
found
in an MS Project Plan. My report needs to have The
following format:

PROJECT NAME
SUMMARY TASK
PROJECT TASK
PROJECT TASK
PROJECT TASK

I use the following SQL statement to create the query:
(line breaks used
to fit in this NG window)
SELECT dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
GROUP BY dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
HAVING (((dbo_MSP_PROJECTS.PROJ_ID)=429));

How can I set up my report to Group by Project (that I
got), then group by
summary task and insert the task name, then by task?

The field in dbo_MSP_TASKS for summary tasks is
TASK_IS_SUMMARY, and
contains either a -1 if the task is a summary or 0 if it
is not.

Thanks.
 
FlyBoy said:
In Access 2002, I'm writing a report based on a query
that hits
two linked MS Project Server 2003 SQL tables,
dbo_MSP_PROJECTS
and dbo_MSP_Tasks. They are joined at the PROJ_ID field.

The report I'm writing has to semi-emulate the outlining
found
in an MS Project Plan. My report needs to have The
following format:

PROJECT NAME
SUMMARY TASK
PROJECT TASK
PROJECT TASK
PROJECT TASK

I use the following SQL statement to create the query:
(line breaks used
to fit in this NG window)
SELECT dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
GROUP BY dbo_MSP_PROJECTS.PROJ_ID,
dbo_MSP_PROJECTS.PROJ_NAME,
dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
dbo_MSP_TASKS.TASK_START_DATE,
dbo_MSP_TASKS.TASK_FINISH_DATE,
dbo_MSP_TASKS.TASK_WORK
HAVING (((dbo_MSP_PROJECTS.PROJ_ID)=429));

How can I set up my report to Group by Project (that I
got), then group by
summary task and insert the task name, then by task?

The field in dbo_MSP_TASKS for summary tasks is
TASK_IS_SUMMARY, and
contains either a -1 if the task is a summary or 0 if it
is not.
Click to expand...


As best I can tell, the grouping in your query does nothing
(you do not have any aggregate expressions in the select
field list).

Unless I'm missing something important, get rid of the
qyery's GROUP BY clause and change HAVING to WHERE. Then go
into the report's design view, open the Sorting and Grouping
window (View menu) and specify the report's grouping fields
there. Specify Yes in the Header property of the PROJECT and
SUMMARY groups.

Now, you can move the desired controls to the appropriate
header sections to get the effect you want.
 
Thanks Marsh.

The problem I have is that the TASK_NAME value has to
appear in both the Summary and Task Field on the report,
depending on its corresponding value in the
TASK_IS_SUMMARY field. In other words, if TASK_IS_SUMMARY
=-1, then it needs to be in the Summary field on the
report, and if the TASK_IS_SUMMARY value =0, then it
needs to be indented beneath the Summary field.

I'm thinking that I'm going to have to produce multiple
queries, one to pull out the summary tasks, and another
to pull out the regular tasks, then a thrid to produce a
report.

I realize I didn't do a very good job of explaining the
output I was after. What would someone need to know to
make this clearer?

Thanks again.
 
Multiple queries may be needed, but at this point I don't
see it being required. It's possible that a little code can
be used to hide or show the task name where needed.

If you would provide a little sample of how you want the
report's output to look along with a few comments to explain
the tricky parts, it might help me better understand your
problem.
 
Marsh:

I used a rather inelegant approach, but got the desired
results. In a query field, if the task evaluated to a
summary task, it placed the task name. If it didn't
evaluate to a summary task, I added twenty spaces and
then the name. It got me the task indenting I was after.

Project Name

1 Summary Task
2 Regular Task
3 Regular Task

I'll include the expression if anyone needs something
like this:
Task: IIf(dbo_MSP_TASKS!
TASK_IS_SUMMARY=0," " &
dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME,dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME)

Thanks for your help.
-----Original Message-----
Multiple queries may be needed, but at this point I don't
see it being required. It's possible that a little code can
be used to hide or show the task name where needed.

If you would provide a little sample of how you want the
report's output to look along with a few comments to explain
the tricky parts, it might help me better understand your
problem.
--
Marsh
MVP [MS Access]


The problem I have is that the TASK_NAME value has to
appear in both the Summary and Task Field on the report,
depending on its corresponding value in the
TASK_IS_SUMMARY field. In other words, if TASK_IS_SUMMARY
=-1, then it needs to be in the Summary field on the
report, and if the TASK_IS_SUMMARY value =0, then it
needs to be indented beneath the Summary field.

I'm thinking that I'm going to have to produce multiple
queries, one to pull out the summary tasks, and another
to pull out the regular tasks, then a thrid to produce a
report.

I realize I didn't do a very good job of explaining the
output I was after. What would someone need to know to
make this clearer?


PROJECT
and
Click to expand...

.
Click to expand...
 
FlyBoy said:
I used a rather inelegant approach, but got the desired
results. In a query field, if the task evaluated to a
summary task, it placed the task name. If it didn't
evaluate to a summary task, I added twenty spaces and
then the name. It got me the task indenting I was after.

Project Name

1 Summary Task
2 Regular Task
3 Regular Task

I'll include the expression if anyone needs something
like this:
Task: IIf(dbo_MSP_TASKS!
TASK_IS_SUMMARY=0," " &
dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME,dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!
TASK_NAME)
Click to expand...


Ah ha, now I see what you wanted. And that's not inelegant,
it's simple and straghtforward.

The calculated field expression could be slightly simplified
with this expression:

Task: IIf(dbo_MSP_TASKS!TASK_IS_SUMMARY = 0,
" ", "") &
dbo_MSP_TASKS!TASK_ID & " " & dbo_MSP_TASKS!TASK_NAME
 
I am trying to do the same thing as described in this posting; however, I
cannot seem to figure it out. When I run my query, I get only the records of
which have a "-1" for summary task. The records with "0" for summary task do
not show up when I add the " " for the spaces/indentation.
The records with "0" for the summary task do show up if I leave out the
spaces. Here's my code:
IIf([Summary]=0," ",[Name]) Or IIf([Summary]=-1,[Name])

Marshall Barton said:
Multiple queries may be needed, but at this point I don't
see it being required. It's possible that a little code can
be used to hide or show the task name where needed.

If you would provide a little sample of how you want the
report's output to look along with a few comments to explain
the tricky parts, it might help me better understand your
problem.
--
Marsh
MVP [MS Access]


The problem I have is that the TASK_NAME value has to
appear in both the Summary and Task Field on the report,
depending on its corresponding value in the
TASK_IS_SUMMARY field. In other words, if TASK_IS_SUMMARY
=-1, then it needs to be in the Summary field on the
report, and if the TASK_IS_SUMMARY value =0, then it
needs to be indented beneath the Summary field.

I'm thinking that I'm going to have to produce multiple
queries, one to pull out the summary tasks, and another
to pull out the regular tasks, then a thrid to produce a
report.

I realize I didn't do a very good job of explaining the
output I was after. What would someone need to know to
make this clearer?
Click to expand...
Click to expand...
 
Back
Top