If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping
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. |
#2
|
|||
|
|||
FlyBoy wrote:
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. 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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
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. -----Original Message----- FlyBoy wrote: 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. 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. -- Marsh MVP [MS Access] . |
#4
|
|||
|
|||
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] FlyBoy wrote: 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? -----Original Message----- FlyBoy wrote: 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. Marshall Barton wrote: 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. |
#5
|
|||
|
|||
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] FlyBoy wrote: 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? -----Original Message----- FlyBoy wrote: 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. Marshall Barton wrote: 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. . |
#6
|
|||
|
|||
FlyBoy wrote:
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) 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 -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
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" wrote: 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] FlyBoy wrote: 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? -----Original Message----- FlyBoy wrote: 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. Marshall Barton wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Report grouping | [email protected] | Setting Up & Running Reports | 1 | August 25th, 2004 04:08 AM |
Worksheet grouping | Johnson Lukose | Worksheet Functions | 4 | August 20th, 2004 07:51 AM |
sorting and grouping | BonnieD | General Discussion | 3 | July 28th, 2004 12:57 AM |
Grouping, Expand/Hide, and Memo fields | Kyle B. | General Discussion | 0 | June 22nd, 2004 09:54 PM |
Using calculated controls as a grouping criteria | Setting Up & Running Reports | 3 | June 6th, 2004 11:03 PM |