How to show only the missing acitvity

[Sunflower]

I have a form that that lists Jobs with a [Status] field of :
"Not Started", "In Process" or "Completed".

A subform with 2 fields, [Activity] and [Date].
The [Activity] selections are: "Assigned", "Out on Proof" and
"Completed"

What I need is a Report ...
that shows ALL "Completed" Jobs
that are missing the "Completed" [Activity] and the [Date].


All help is appreciated

 

[Scott]

you need to base your report on a query that jons the two tables and has the
status field listed as "complete" and the activity field as "null"

 

[Sunflower]

you need to base your report on a query that jons the two tables and has the
status field listed as "complete" and the activity field as "null"

I have a form that that lists Jobs with a [Status] field of :
"Not Started", "In Process" or "Completed".

A subform with 2 fields, [Activity] and [Date].
The [Activity] selections are: "Assigned", "Out on Proof" and
"Completed"

What I need is a Report ...
that shows ALL "Completed" Jobs
that are missing the "Completed" [Activity] and the [Date].

All help is appreciated- Hide quoted text -

- Show quoted text -

Every Job has an Activity of "Assigned" with a "Date" as well as an
Activity of "Out on Proof" with a "Date.
So the query with the activity of null will not give me what I need in
my report.

I need to make sure that when the job status has changed to
"Completed" that the activity of "Completed" with a "Date" is also
created.
I need to have a report that basically lets me know which jobs have
not been closed out properly with a activity of "Completed" with a
"Date".

 

[John Spencer]

Do you have a Jobs table?
Do you have a JobActivities table?

First step is to construct a query that list all JobActivities that are
"Completed" and that have a date in some field.

Then use that query with the jobs table to get those jobs that are
completed using the Unmatched query wizard.

Would you care to share some details on the tables and field names and
the data stored in the fields?

Generic sample for this could be:

SELECT Jobs.*
FROM Jobs
WHERE JobID Not IN
(SELECT JobActivities.JobID
FROM JobActivities
WHERE Activity = "Completed" and ActivityDate is Null)
AND Jobs.Status = "Completed"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

you need to base your report on a query that jons the two tables and has the
status field listed as "complete" and the activity field as "null"

I have a form that that lists Jobs with a [Status] field of :
"Not Started", "In Process" or "Completed".
A subform with 2 fields, [Activity] and [Date].
The [Activity] selections are: "Assigned", "Out on Proof" and
"Completed"
What I need is a Report ...
that shows ALL "Completed" Jobs
that are missing the "Completed" [Activity] and the [Date].
All help is appreciated- Hide quoted text -

- Show quoted text -

Every Job has an Activity of "Assigned" with a "Date" as well as an
Activity of "Out on Proof" with a "Date.
So the query with the activity of null will not give me what I need in
my report.

I need to make sure that when the job status has changed to
"Completed" that the activity of "Completed" with a "Date" is also
created.
I need to have a report that basically lets me know which jobs have
not been closed out properly with a activity of "Completed" with a
"Date".