create Query in access-CodePudding

Customer Table

ECR Table

I have two tables(they are related in cif) and i want to know how to create those two query

first one to show all customer id as cif in my table (1) whose status= closed and they have ECRstatus from second table = active

second one to show all customer whose status closed doesn't have any active ECR if he has one or more doesn't appear in this query and only appear in first one

CodePudding user response：

Add these queries as SQL:

ECR Status Active:

SELECT Customer.CIF, Customer.CUS_STATUS, ECR.ECRStatus
FROM Customer INNER JOIN ECR ON Customer.CIF = ECR.ID_cif
WHERE (((Customer.CUS_STATUS)="closed") AND ((ECR.ECRStatus)="active"));

ECR Status Inactive:

SELECT Customer.CIF, Customer.CUS_STATUS, ECR.ECRStatus
FROM Customer INNER JOIN ECR ON Customer.CIF = ECR.ID_cif
WHERE (((Customer.CUS_STATUS)="closed") AND ((ECR.ECRStatus)<>"active"));