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.

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.
Show quoteHide quote

Show quote Hide quote 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.

Need report to only select the "yes" based on month
Sorting based on selected value...Please help!
Importing dates from fixed-width text files to Access 2007
TransferDatabase help...totally lost
Tab Control
Linking to selected fields in an external table
Data Transfer
Remove Spaces
Time Conversion
Query finding minimum date