subquery?

  • Thread starter Thread starter clyde
  • Start date Start date
C

clyde

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?
 
clyde said:
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?
Click to expand...


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.
 
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]<Expr>Not<Expr>=[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.
 
clyde said:
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]<Expr>Not<Expr>=[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.
Click to expand...


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.
 
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 said:
clyde said:
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]<Expr>Not<Expr>=[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.
Click to expand...


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)
Click to expand...
 
clyde said:
Since this my first attempt at creating SQL, I'm very thankful you came to
my rescue!!
Click to expand...


You're welcome. For a first attempt, you did a pretty good job!
 
Back
Top