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
|
|||
|
|||
subquery?
I'm trying to create a status report that has a task field and a status field
that may have multiple entries under a task before the status becomes 'complete'. At the point the status is complete, I'd like to omit that task and all those task entries in the report. I have been trying to use a query to compare all the tasks with the completed tasks, but cannot get the syntax right. Is this the best way to do this? -- thanks ~ mahalo |
#2
|
|||
|
|||
subquery?
"clyde" wrote in message
... I'm trying to create a status report that has a task field and a status field that may have multiple entries under a task before the status becomes 'complete'. At the point the status is complete, I'd like to omit that task and all those task entries in the report. I have been trying to use a query to compare all the tasks with the completed tasks, but cannot get the syntax right. Is this the best way to do this? I don't know about anyone else, but it's not clear to me what it is that you are trying to do. Could you give some examples of what you have in your tables and what you would want to see in your output, and why those data should give that output? That would help clarify your intentions. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
subquery?
The status report is comprised of three tables, project (project & project
id); tasks (task, date assigned, priority, project id, comment id); and comments (comment date, comment status, comment, comment id, task id). Each project has several tasks and each task has several comments associated with it until the task is completed, at which time the comment status is populated with complete. I need to retain all tasks and comments in the database, but want to report and update only those tasks that are not complete. For example: Project A has a task with eight comments that was completed today. Project B has a task with five comments that is continuing. When I run a report, I would like the report to display only the task and comments under project B. I would run the completed query prior to running the report to ensure it is up to date. My original thought was to develop a query of the tasks that are complete and compare the completed tasks with the overall tasks to provide a report that will be active tasks. However, I am struggling with how to do that. I have been able to create the query that display the completed tasks and thought the expression should be: [Tasks]![Task]ExprNotExpr=[completed]![Task] (invalid syntax) which didn't work, so then I tried: SELECT * Task FROM Tasks WHERE NOT IN (SELECT * Task FROM completed); (subquery syntax incorrect) and SELECT * FROM [SELECT Tasks.Task] WHERE NOT IN (SELECT * FROM [SELECT completed.Tasks]); (subquery syntax incorrect) completed is the name of the query and Task is derived from the same Tasks table. I hope this helps. -- thanks ~ mahalo "Dirk Goldgar" wrote: "clyde" wrote in message ... I'm trying to create a status report that has a task field and a status field that may have multiple entries under a task before the status becomes 'complete'. At the point the status is complete, I'd like to omit that task and all those task entries in the report. I have been trying to use a query to compare all the tasks with the completed tasks, but cannot get the syntax right. Is this the best way to do this? I don't know about anyone else, but it's not clear to me what it is that you are trying to do. Could you give some examples of what you have in your tables and what you would want to see in your output, and why those data should give that output? That would help clarify your intentions. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#4
|
|||
|
|||
subquery?
"clyde" wrote in message
... The status report is comprised of three tables, project (project & project id); tasks (task, date assigned, priority, project id, comment id); and comments (comment date, comment status, comment, comment id, task id). Each project has several tasks and each task has several comments associated with it until the task is completed, at which time the comment status is populated with complete. I need to retain all tasks and comments in the database, but want to report and update only those tasks that are not complete. For example: Project A has a task with eight comments that was completed today. Project B has a task with five comments that is continuing. When I run a report, I would like the report to display only the task and comments under project B. I would run the completed query prior to running the report to ensure it is up to date. My original thought was to develop a query of the tasks that are complete and compare the completed tasks with the overall tasks to provide a report that will be active tasks. However, I am struggling with how to do that. I have been able to create the query that display the completed tasks and thought the expression should be: [Tasks]![Task]ExprNotExpr=[completed]![Task] (invalid syntax) which didn't work, so then I tried: SELECT * Task FROM Tasks WHERE NOT IN (SELECT * Task FROM completed); (subquery syntax incorrect) and SELECT * FROM [SELECT Tasks.Task] WHERE NOT IN (SELECT * FROM [SELECT completed.Tasks]); (subquery syntax incorrect) completed is the name of the query and Task is derived from the same Tasks table. I'm not sure why there's a [Comment ID] field in table Tasks. It seems Project is related one-to-many with Tasks (based on the foreign key [Project ID] in Tasks), and Tasks is related one-to-many with Comments (based on the foreign key [Task ID] in Comments). Does the [Comment ID] field in Tasks serve any purpose? Is there no [Task ID] field in the Tasks table? I'm going to guess that there is. To build the query for your status report, let's consider how first to identify tasks that are not completed. Here's a query that would return the [Task ID]s of all the completed tasks. It's probably very similar to your "completed" query: SELECT [Task ID] FROM Comments WHERE [comment status]='complete' Note, by the way, that I am doing my best to get the names of the various tables and fields correct. If I make a mistake, please correct it. Now, to get all the tasks that are not completed, we can use the subquery structure you were reaching toward: SELECT * FROM Tasks WHERE Tasks.[Task ID] NOT IN ( SELECT [Task ID] FROM Comments WHERE [comment status]='complete' ) For your report, as I understand it, you'll also want data from the other tables. So we need to join those tables to Tasks appropriately. My guess is that you don't want to see any projects without tasks, but you do want to see tasks without comments. That means we need an inner join between Project and Tasks, and a left join between Tasks and Comments: SELECT Project.[Project ID], Project.Project, Tasks.[Task ID], Tasks.Task, Tasks.[Date Assigned] Tasks.Priority, Comments.[Comment ID], Comments.[Comment Date], Comments.[Comment Status], Comments.[Comment] FROM ( Project INNER JOIN Tasks ON Tasks.[Project ID] = Project.[Project ID] ) LEFT JOIN Comments ON Comments.[Task ID] = Tasks.[Task ID] WHERE Tasks.[Task ID] NOT IN ( SELECT [Task ID] FROM Comments WHERE [comment status]='complete' ) Now, that is "air SQL", and I don't really know the structure of your tables, but I think something very like that is what you're looking for. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#5
|
|||
|
|||
subquery?
Thanks so much for your help!!! After discovering that the comment status
was actually Comments.[status] in my table (which you couldn't have known), your syntax on the 'Air SQL' was perfect. I threw the Comment ID in the Task Table because I wasn't sure if I needed it or not, seems like I don't. And yes there is a Task ID in the Task Table. Since this my first attempt at creating SQL, I'm very thankful you came to my rescue!! -- thanks ~ mahalo "Dirk Goldgar" wrote: "clyde" wrote in message ... The status report is comprised of three tables, project (project & project id); tasks (task, date assigned, priority, project id, comment id); and comments (comment date, comment status, comment, comment id, task id). Each project has several tasks and each task has several comments associated with it until the task is completed, at which time the comment status is populated with complete. I need to retain all tasks and comments in the database, but want to report and update only those tasks that are not complete. For example: Project A has a task with eight comments that was completed today. Project B has a task with five comments that is continuing. When I run a report, I would like the report to display only the task and comments under project B. I would run the completed query prior to running the report to ensure it is up to date. My original thought was to develop a query of the tasks that are complete and compare the completed tasks with the overall tasks to provide a report that will be active tasks. However, I am struggling with how to do that. I have been able to create the query that display the completed tasks and thought the expression should be: [Tasks]![Task]ExprNotExpr=[completed]![Task] (invalid syntax) which didn't work, so then I tried: SELECT * Task FROM Tasks WHERE NOT IN (SELECT * Task FROM completed); (subquery syntax incorrect) and SELECT * FROM [SELECT Tasks.Task] WHERE NOT IN (SELECT * FROM [SELECT completed.Tasks]); (subquery syntax incorrect) completed is the name of the query and Task is derived from the same Tasks table. I'm not sure why there's a [Comment ID] field in table Tasks. It seems Project is related one-to-many with Tasks (based on the foreign key [Project ID] in Tasks), and Tasks is related one-to-many with Comments (based on the foreign key [Task ID] in Comments). Does the [Comment ID] field in Tasks serve any purpose? Is there no [Task ID] field in the Tasks table? I'm going to guess that there is. To build the query for your status report, let's consider how first to identify tasks that are not completed. Here's a query that would return the [Task ID]s of all the completed tasks. It's probably very similar to your "completed" query: SELECT [Task ID] FROM Comments WHERE [comment status]='complete' Note, by the way, that I am doing my best to get the names of the various tables and fields correct. If I make a mistake, please correct it. Now, to get all the tasks that are not completed, we can use the subquery structure you were reaching toward: SELECT * FROM Tasks WHERE Tasks.[Task ID] NOT IN ( SELECT [Task ID] FROM Comments WHERE [comment status]='complete' ) For your report, as I understand it, you'll also want data from the other tables. So we need to join those tables to Tasks appropriately. My guess is that you don't want to see any projects without tasks, but you do want to see tasks without comments. That means we need an inner join between Project and Tasks, and a left join between Tasks and Comments: SELECT Project.[Project ID], Project.Project, Tasks.[Task ID], Tasks.Task, Tasks.[Date Assigned] Tasks.Priority, Comments.[Comment ID], Comments.[Comment Date], Comments.[Comment Status], Comments.[Comment] FROM ( Project INNER JOIN Tasks ON Tasks.[Project ID] = Project.[Project ID] ) LEFT JOIN Comments ON Comments.[Task ID] = Tasks.[Task ID] WHERE Tasks.[Task ID] NOT IN ( SELECT [Task ID] FROM Comments WHERE [comment status]='complete' ) Now, that is "air SQL", and I don't really know the structure of your tables, but I think something very like that is what you're looking for. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#6
|
|||
|
|||
subquery?
"clyde" wrote in message
... Since this my first attempt at creating SQL, I'm very thankful you came to my rescue!! You're welcome. For a first attempt, you did a pretty good job! -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|