Getting the right criteria (1 Viewer)

[Dumferling]

Maybe it is just because it is the end of a pretty hectic week but I just can't think how to do this. I have a list of contract counterparties (the one side). On the many side we have contracts. Contracts can have a status of "Current" and "Terminated". A counterparty may, for example, have three contracts - two with a "Terminated" status and one with a "Current" status.

I want to show those Counterparties where all their related contracts are "Terminated". Any Counterparty with even one related contract with a "Current" status would be excluded from the list. I just can't think how to set up the query to handle this. I am pretty sure this is simple but I can't see it. Any suggestions most welcome!

 

[Pat Hartman]

Join the counterparties to the contracts. Do not select any columns from the contract tables. Add a where clause
Where Status = "Current"
Then change it to a totals queries and count the number of rows returned for each counterparty.
Add additional query
Where Count("*") = 0

 

[GPGeorge]

Off the top of my head:

SELECT ContractPartyID, ContractParty
FROM tblContractPary WHERE
ContractPartyID NOT EXISTS
(SELECT ContractPartyID FROM tblContracts WHERE ContractStatus ="Current")

Or use Pat's approach.

 

[Pat Hartman]

I use joins rather than subselects except where subselects are the only option because Access does not optimize subselects well. Obviously small recordsets are irrelevant. You can do whatever you want.

 

[ebs17]

I would write @GPGeorge's query like this

SELECT
   C.ContractPartyID,
   C.ContractParty
FROM
   tblContractParty AS C
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            tblContracts AS X
         WHERE
            X.ContractPartyID = C.ContractPartyID
               AND
            X.ContractStatus = "Current"
      )

because Access does not optimize subselects well

This doesn't necessarily have to be the case if the developer has already optimized the query.
If there is only one key in the correlation, the existing index on the key is used.

Or

SELECT DISTINCT
   C.ContractPartyID,
   C.ContractParty
FROM
   tblContractParty AS C
      LEFT JOIN
         (
            SELECT
               ContractPartyID
            FROM
               tblContracts
            WHERE
               ContractStatus = "Current"
         ) AS X
         ON C.ContractPartyID = X.ContractPartyID
WHERE
   X.ContractPartyID IS NULL

 

[plog]

1 SELECT---

SELECT CounterPartyName
FROM CounterParties
INNER JOIN Contracts on Contracts.CounterPartyID = CounterParties.CounterPartyID
GROUP BY CounterPartyName
HAVING MIN(ContractStatus)="Terminated")

If any of their contracts are "Current", then that would be the minimum value for all of their contract statuses and they would not be included in the above query. But if the minimum value is "Terminated" then that means all their contract statuses are "Terminated".

 

[Pat Hartman]

I'm sure there's at least one more way to do this