Final Status

[Annette]

I have a one to many relationship between a work order and the people
assigned to the work order. Is there a way for me to determine the
open/closed status of the work order by the "many" side's completion
date? In other words. I have a work order #123 that is assigned to
worker 1, 2 and 3. Worker 1 completed their portion of the assignment
and added a closed date. Worker 2 completed their portion of the
assignment and add a closed date, however worker 3 did not complete
and the closed date for worker 3 is still blank. I would like to get a
query of all work orders with the status of open and closed. In this
case, this work order # 123 would be still open as worker #3 didn't
have a completion date.

 

[John W. Vinson]

I have a one to many relationship between a work order and the people
assigned to the work order. Is there a way for me to determine the
open/closed status of the work order by the "many" side's completion
date? In other words. I have a work order #123 that is assigned to
worker 1, 2 and 3. Worker 1 completed their portion of the assignment
and added a closed date. Worker 2 completed their portion of the
assignment and add a closed date, however worker 3 did not complete
and the closed date for worker 3 is still blank. I would like to get a
query of all work orders with the status of open and closed. In this
case, this work order # 123 would be still open as worker #3 didn't
have a completion date.

Finding the incomplete work orders is easier: create a Query by joining
WorkOrders to the assignments table, select the ClosedDate field, and put on a
criterion of

IS NULL

This will find all WO's for which at least one worker has been assigned but
not closed.

Finding complete orders is a bit more work - it's usually harder to find the
absence of information than its presence! There are a couple of ways, but one
is to create a Query on the assignments table to find all records where
ClosedDate is NULL (this will be a list of all open tasks). Now use the query
wizard to create an Unmatched query, finding all records in WorkOrders that do
not have a match in this open-tasks query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com