Code:
SELECT Chemist.ChemShortCode,
Chemist.ChemistName,
Chemist.ChemShortCode,
Chemist.Active,
Count(Scripts.ScriptID) AS CountOfScriptID
FROM (Chemist
LEFT JOIN ScriptBatch ON Chemist.ChemShortCode = ScriptBatch.ChemistID)
LEFT JOIN Scripts ON ScriptBatch.ScrBatchID = Scripts.ScrBatchID
WHERE (((Scripts.PaymentID) IS NULL)
AND ((Scripts.InvoiceID) IS NOT NULL))
OR (((Scripts.PaymentID) IS NULL))
GROUP BY Chemist.ChemShortCode,
Chemist.ChemistName,
Chemist.ChemShortCode,
Chemist.Active,
Chemist.ChemistName
HAVING (((Chemist.Active)=TRUE))
OR (((Chemist.Active)=FALSE))
ORDER BY Chemist.ChemistName;
Now what is happening is too mayn chemists are there. the INACTIVE chemists are there even if they HAVE payment ID