I free handed my SQL and missed it by one parenthesis (and a GROUP BY I almost edited before anyone caught). I downloaded your database and tested my SQL and got it right:
Now, the issue with doing it in the above way is that if your data has "duplicate records" (records with same OrderFK/StageFK/StageDate values) it could cause some false positives and false negatives. My method assumes that if an OrderFK has 3 records meeting the criteria that those 3 records must be unique and each meet a different criterion. But if you had one OrderFK with 4 records that match the criteria it will not be in the result. If you had one OrderFK that had 2 records meeting the first criterion, 1 record meeting the 2nd criterion and no records meeting the 3rd criterion it would still make it into the final results because it has a total of 3 records meeting the criteria even though it didn't have any meeting the last criterion.
I ran a test on your data on to see if you had any duplicates (same OrderFK/StageFK/STageDate) and you do. Not a lot, but you do. You can use this query to find them:
The alternative to my method is to make 1 query for each criterion and UNION those queries together and then do a count.
SELECT OrderFK
FROM tblProductionStage
WHERE ((StageFK=1) AND (IsNull(StageDate)=False))
OR ((StageFK=2) AND (IsNull(StageDate)=False))
OR ((StageFK=20) AND (IsNull(StageDate)=True))
GROUP BY OrderFK
HAVING COUNT(OrderFK)=3
Now, the issue with doing it in the above way is that if your data has "duplicate records" (records with same OrderFK/StageFK/StageDate values) it could cause some false positives and false negatives. My method assumes that if an OrderFK has 3 records meeting the criteria that those 3 records must be unique and each meet a different criterion. But if you had one OrderFK with 4 records that match the criteria it will not be in the result. If you had one OrderFK that had 2 records meeting the first criterion, 1 record meeting the 2nd criterion and no records meeting the 3rd criterion it would still make it into the final results because it has a total of 3 records meeting the criteria even though it didn't have any meeting the last criterion.
I ran a test on your data on to see if you had any duplicates (same OrderFK/StageFK/STageDate) and you do. Not a lot, but you do. You can use this query to find them:
SELECT tblProductionStage.OrderFK, tblProductionStage.[StageFK], IsNull([StageDate]), Count(tblProductionStage.ProductionStage) AS CountOfProductionStage
FROM tblProductionStage
GROUP BY tblProductionStage.OrderFK, tblProductionStage.[StageFK], IsNull([StageDate])
ORDER BY Count(tblProductionStage.ProductionStage) DESC;
The alternative to my method is to make 1 query for each criterion and UNION those queries together and then do a count.
Last edited: