CODEWITHSUNDEEP
sqlvbams-access
Sapster77
Sapster77

Reputation: 53

MS Access VBA SQL query using multiple AND operators fails?

I have a table (tbl_Avail) that shows availability for different people on certain dates and on certain parts of the particular day. I am trying to make a query that counts the number of people available on multiple dates AND on certain times on those days.

tbl_Avail: (Pas1 & Pas2 (Boolean) indicates the 2 periods of the day that I am searching for availability for. True indicates available - false, not available)

ID   Student   Date_Avail    Pas1    Pas2
1    Joe       2020/08/09    x
2    Bob       2020/08/09            x
3    Pete      2020/08/09    x
4    Joe       2020/08/08    x       x
5    Bob       2020/08/08    x
6    Pete      2020/08/08    x       x

In this example I am searching for the number of people available (true) on date 2020/08/08 Pas2 AND on 2020/08/09 Pas1, but when I run the query it returns 0 - my expected result would be 2, as both Joe and Pete are available in Pas2 on the 8th and in Pas1 on the 9th.

My query:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StudentsAvail As Long

Set rs = db.OpenRecordset("SELECT Count(*) AS StudentsTotal FROM tbl_Avail WHERE ((Pas1=true AND Date_Avail= #2020/08/09#) and (Date_Avail=#2020/08/08# AND Pas2=true))")
StudentsAvail = rs!StudentsTotal

If I change the query to only use …. WHERE Pas1=true and Date_Avail= #2020/08/09# for example, it works correctly. There are no error codes - it just counts 0.

I you have any idea as to what I am doing wrong, or have a better solution please let me know:-) Thank you. Peter

Upvotes: 0

Views: 328

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

One method is two levels of aggregation:

SELECT Count(*) AS StudentsTotal
FROM (SELECT Student
      FROM tbl_Avail
      GROUP BY Student
      HAVING SUM(IIF(Pas1 = true AND Date_Avail = #2020/08/09#, 1, 0)) > 0 AND
             SUM(IIF(Pas2 = true AND Date_Avail = #2020/08/08#, 1, 0)) > 0
     ) as s;

The subquery returns the students with both conditions true. If uses a HAVING clause because the conditions are true on different rows.

The outer query counts the students.

Upvotes: 1

Related Questions