One to many help

[Opal]

Using Access 2003, I am not certain how to approach
this. I have two tables with a one-to-many
relationship. The "many" table contains
countermeasures related to a single problem
detailed in the "one" table. These countermeasures
have a status of either 'open' or 'closed'. I want to
create a notification function when ALL countermeasures
are closed, but each problem will have a different
number of countermeasures. How can I set up the
capability via a query (?) that lets me know when all
countermeasures are closed. The c/m data is input
into the table via a bound continuous subform.

Any help / direction would be appreciated.

 

[John Spencer]

As long as at least one countermeasure is open then the problem is still open.

So This query would return all problems that are open
SELECT *
FROM Problems
WHERE Exists
(SELECT * FROM Countermeasures Where Status = "Open"
AND CounterMeasures.ProblemID = Problems.ProblemID)

You can use Not Exists if you want to show a list of problems that are closed
because all countermeasures are closed.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Opal]

thank you John, that helps get me partway there....

So, trying to wrap my brain around this....

User is updating the status on the countermeasures

He changes one to closed, but there is still 2 open,
I need to check to make sure that the ProblemID he
is working on is still in that query list and no action
is taken.

He goes to another problem and updates another
countermeasure and now all countermeasures are
closed for this ProblemID so now I need to send a
notification to his supervisor to review.

So I need an IF statement to check if the ProblemID
exists in the query (above) and if it does, do nothing
and if it doesn't send notice......

ah......I'm drawing a blank..... :-(

 

[John Spencer]

If you are talking code in a form.
Assumption:
ProblemID is a number field
Status is a text field

I would use the AfterUpdate event of the form where you are changing the data

Private Sub Form_AfterUpdate()
If DCount("*","[CounterMeasures]", _
"ProblemID =" & Me.ProblemID & " AND Status ='Open')= 0 Then
'Send the message to the supervisor since all countermeasures are closed
'for this problemID
END IF

End Sub

There is a small problem with this approach if someone is entering
countermeasure records and marks the status as closed while entering the
records. You could be sending messages at the wrong time.

Record one entered for problem 252 and status is set as closed. Out goes the
message

Record two entered for problem 252 and status is set as open. Oh! We already
told the boss this problem is closed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Opal]

Thanks John,

I actually inherited this database so updates are
completed on a separate form so this suggestion will
work well. Thank you for your help.