CODEWITHSUNDEEP
sqlms-accessms-access-2010
Alan
Alan

Reputation: 822

SQL query results not what I expect

I'm testing the following SQL query in Access 2010:

SELECT 
    Employees.Corps_ID, Employees.Last_Name, Employees.First_Name, 
    Shifts.Start_Date_Time, Shifts.End_Date_Time, 
    DateDiff('n',Shifts.Start_Date_Time,Shifts.End_Date_Time) AS SubTotalMinutes, 
    Locations.Location_Name
FROM 
    (Locations 
INNER JOIN 
    Shifts ON Locations.Location_ID = Shifts.Location) 
INNER JOIN 
    (Employees INNER JOIN Employees_Shifts ON Employees.Employee_ID = Employees_Shifts.Employee_ID) ON Shifts.Shift_ID = Employees_Shifts.Shift_ID 
WHERE 
    (((Shifts.Start_Date_Time) Between #2015/09/26# And #2015/10/09#) 
     AND ((Shifts.Schedule_ID) = 1) OR ((Shifts.Schedule_ID) = 2)) 
ORDER BY 
    Employees.Last_Name;

Please note the date range Between #2015/09/26# And #2015/10/09#. That's September 26th. to October 9th. This represents a pay period. The query runs without error, however, the results display records beyond October 9th. In fact they continue thru to the end of the month. I don't understand why it is doing that. Has anyone got an explanation?

FYI, I thought it might have something to do with the conditions:

(((Shifts.Schedule_ID) = 1) OR ((Shifts.Schedule_ID) = 2)) but if I change the OR to an AND, I get an empty recordset, and that's not good. I have to reference both Schedules because each one covers one month and the pay period spans the last week of the first month and the first week of the following month.

Please advise.

Upvotes: 1

Views: 39

Answers (2)

davidbaumann
davidbaumann

Reputation: 218

Your condition is like:

WHERE (((Shifts.Start_Date_Time) Between #2015/09/26# And #2015/10/09#) AND ((Shifts.Schedule_ID)=1) OR ((Shifts.Schedule_ID)=2))

Let's make it easier to read:

WHERE (Shifts.Start_Date_Time Between #2015/09/26# And #2015/10/09#) AND (Shifts.Schedule_ID=1) OR (Shifts.Schedule_ID=2)

So it's like:

WHERE A and B OR C

This will show anything with "A and B", and anything with "C", but you want "A and B" or "A and C".

SELECT Employees.Corps_ID, Employees.Last_Name, Employees.First_Name, Shifts.Start_Date_Time, Shifts.End_Date_Time, DateDiff('n',Shifts.Start_Date_Time,Shifts.End_Date_Time) AS SubTotalMinutes, Locations.Location_Name
FROM (Locations INNER JOIN Shifts ON Locations.Location_ID = Shifts.Location) INNER JOIN (Employees INNER JOIN Employees_Shifts ON Employees.Employee_ID = Employees_Shifts.Employee_ID) ON Shifts.Shift_ID = Employees_Shifts.Shift_ID 
WHERE (((Shifts.Start_Date_Time) Between #2015/09/26# And #2015/10/09#) AND ((Shifts.Schedule_ID)=1) OR ((Shifts.Schedule_ID)=2))) 
ORDER BY Employees.Last_Name;

Upvotes: 0

HansUp
HansUp

Reputation: 97101

The Access query designer adds in too many unneeded parentheses, I think those muddy the logic in this situation. Here is how the db engine treats your WHERE conditions ...

WHERE
       (Shifts.Start_Date_Time Between #2015/09/26# And #2015/10/09# AND Shifts.Schedule_ID=1)
    OR Shifts.Schedule_ID=2

So basically a row will be included in the query result set if its Start_Date_Time is within your target date range and its Schedule_ID=1. Or a row will be included if its Schedule_ID=2 regardless of its Start_Date_Time.

I think you actually want this instead ...

        Shifts.Start_Date_Time Between #2015/09/26# And #2015/10/09#
    AND (Shifts.Schedule_ID=1 OR Shifts.Schedule_ID=2)

But for me, it's clearer to use an IN list for the Schedule_ID values ...

        Shifts.Start_Date_Time Between #2015/09/26# And #2015/10/09#
    AND Shifts.Schedule_ID IN (1,2)

Upvotes: 1

Related Questions