Dumferling
Member
- Local time
- Today, 04:43
- Joined
- Apr 28, 2020
- Messages
- 102
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.
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.