WHERE clause with multiple values from one field [Access]
Hello i have created database of Employees and their rights to access many areas, using MS Access(see the attached image of my database schema)
now, lets say i want to select ALL EmployeeIDs from r_rights table, who have access to areas 1 OR 5,
here is my SQL code: SELECT r_rights.EmployeeID, r_rights.AreaID
FROM r_rights
WHERE r_rights.AreaID = 1 OR r_rights.AreaID = 5;
it returns all EmployeeIDs who has access to area1 or area5
But what if i want to list ONLY EmployeeIDs who have access to area1 AND area5?
i tried to put this line in the above code: WHERE r_rights.AreaID = 1 AND r_rights.AreaID = 5;
but it didnt work...no errors, but it didnt give any results
similiar problem comes out when i want to select, for example all employees whoose card expires on 5th month of 2008 year... here is the SQL for it:
SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, d_divisions.DivisionName, t_titles.TitleName, e_employees.CardExpireDate
FROM t_titles INNER JOIN (d_divisions INNER JOIN e_employees ON d_divisions.DivisionID = e_employees.DivisionID) ON t_titles.TitleID = e_employees.TitleID
WHERE (Year(CardExpireDate)) = 2008 AND (Month(CardExpireDate)) = 5;
Re: WHERE clause with multiple values from one field [Access]
For your first problem you could use this:
Code:
SELECT EmployeeID
FROM r_rights
WHERE AreaID = 1 OR AreaID = 3
GROUP BY EmployeeID
HAVING COUNT(EmployeeID) = 2;
It'll get the ID's of all employees that have access to both area 1 and 3. It'll only return one row for each employee and not the area IDs as you already sent them in I figured you don't need them back. If that's the case you'll need a different query.
With your second query I don't see any errors, you should try removing the WHERE clause altogether and see if you get any rows, and if you do, check the values of CardExpireDate and see if they are dates with year 2008 and month 5.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.