Apparently simple queries which aren't (1 Viewer)

[Dumferling]

I am hoping someone can tell me what I am not seeing here. I have a contract register. In simplistic terms one table tracks the counterparty. Each counterparty has a Current field which has a Yes/No list. There is also a contract table which lists the contracts and which has a one to many relationship with the counterparty table - one counterparty many contracts. Each contract also has a status - Active or Terminated. A counterparty can have many contracts - some Active, some Terminated.

At some point all the contracts related to a counterparty will be terminated. I am trying to create a query that tells me when this is the case so that I can change the status of the counterparty to "No" and keep the list of counterparties with only those counterparties who actually have "Active" contracts

I am trying to filter a list of current (Yes) counterparties where all the contracts are Terminated. It is easy to see which counterparties have a terminated contract but I cannot figure out how to check that they also have no Active contracts.

The simple concept is "Show me all the counterparties with a Current status of Yes where there are no Active Contracts". Putting this into practise is causing a headache. If I filter for "Terminated" then I get a list of possible counterparties whose Current status might need to be changed to "No". I cannot figure out how to also check if that same counterparty has no "Active" contracts.

This is proving intensely irritating as I feel that this is a simple problem and I just can't see it.

 

[Minty]

I think, whilst your description is quite readable, what normally helps with these things is demo data and real field names.
If you can change the names, please post up a sample of your tables with enough data to cover the expected outcomes.

I do wonder about the necessity of your counterparty current field, in theory, you could simply count the active contracts and if 0 then they are not current.

 

[arnelgp]

use inner join counterparty to your contract table (query1).

SELECT tblCounterParty.Current, Count(Contract.fkField) AS TotalContract, Count(IIf([Status]="Active",1,Null)) AS TotalActive, Count(IIf([Status]="Terminated",1,Null)) AS TotalTerminated
FROM tblCounterParty INNER JOIN Contract ON tblCounterParty.pkField = Contract.fkField
GROUP BY tblCounterParty.Current;

from the above query you create Anothe query that will Filter [Current] = True,
and TotalContract = TotalTerminated

 

[Dumferling]

I don't unfortunately have test data I can put up. The results of this query will show me a count of counterparties with a "Yes" status and the count will all show a number 1 or higher. If I change the status to "Active" it will still show numbers of 1 or higher. What I need is to show all those that are "Terminated" and do not appear in a query on the "Active"