Query with multiple Criterion

[xPaul]

Hi all,

I have a log table, which is essentially a dumping ground of checks that I and others have completed. Each person has a set amount of checks to complete, there are three checks, Check 1, Check 2, Check 3.

There must be three Checks of Check 1, two of Check 2, and 1 of Check 3. Please bear in mind that the amount of these checks are dependant on another table in the database which allows me to change the amount of checks each person needs to do on the fly.

I am trying to develop a query that will return the names and email addresses of those who:

• Have not completed any checks
• OR have started but not finished the allocation outlined above.
• AND are scheduled to complete these checks

In the end up I want to email these users to remind them that they have not completed their checks.

I have been chipping away up until this point where I have hit a brick wall. So far I have managed the following in SQL:

SELECT tblEmployee.[EmpName], tblEmployee.[EmpEmailAddress]
FROM tblEmployee
WHERE NOT EXISTS (SELECT tblCheckLog.[CompletingEmp], tblCheckLog.[DateCompleted]                        
                                   FROM tblCheckLog                                    
                                   WHERE tblCheckLog.[CompletingEmp] = tblEmployee.[EmpName]
                                   AND ((DatePart("ww",[DateCompleted])=DatePart("ww",Date()) And Year([DateCompleted])=Year(Date()))))
AND tblEmployee.[CompletesChecks] = "Yes";

This shows me who hasn't completed a check whatsoever this week.

How do I progress it from here to include those who have started but not finished? I.e. Count of Check 1 < 3.

 

[Guus2005]

1 - First create a query where Count of Check 1 < 3.
2- Then create a query which gives you all the fields you are interested in.

Join these two where you see all records from 2 and only those of 1 where the id matches.

Those records which give a NULL result in the first query are the ones that have not finished.

3 - You could create a third query which shows everyone that has started and inner join this one with the second query.

HTH