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;

no errors, but it doesnt show any record...

please help me to solve these problems, thank you

since those would have to be separate rows, you are basically looking for an employee who has certain rows within his set of rows

when you deal with a set of rows, GROUP BY should immediately come to mind

the technique for this type of problem is to count the rows which satisfy the criteria, and then see if you got all the rows which you wanted...

Hey thanks! it did work for one table query! really thanks!

but no i am trying to modify my query as before, and show also firstname, lastname,cardexpire dat, for similiar query.

i did combine my query and your suggestion like this:

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) INNER JOIN (a_areas INNER JOIN r_rights ON a_areas.AreaID = r_rights.AreaID) ON e_employees.EmployeeID = r_rights.EmployeeID) ON t_titles.TitleID = e_employees.TitleID
WHERE r_rights.AreaID IN ( 1, 5 )
GROUP
BY e_employees.EmployeeID
HAVING count(*) = 2;

but it showed the following error: You tried to execute a query that does not include the specified expression 'FirstName' as part of aggregate function.

is this error because such method works only when i select from ONE table?

This approach will only work if the combination of (EmployeeId, AreaId) is unique. As soon as you have two rows with the same combination, the query may not return the expected result. What I would do instead is this:
You also avoid complex GROUP BY conditions if you want to return a lot of columns in the select list.

Note, however, that this has a distinct problem if you have many AreaId values that should be checked because each additional ID implies another join. Grouping is much more elegant in those cases.

which, indeed, it is, by virtue of being the PK




that's my feeling, too

Thanks friends!
Now i need to run a query which shows FirstName,LastName,FathersName, and also shows passgiver and birthplace! by the DivisionID number

here is my SQL code for it:

SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, b_birthplaces.BirthPlaceName, p_passgivers.PassGiverName
FROM b_birthplaces INNER JOIN (p_passgivers INNER JOIN (d_divisions INNER JOIN e_employees ON d_divisions.DivisionID = e_employees.DivisionID) ON p_passgivers.PassGiverID = e_employees.PassGiverID) ON b_birthplaces.BirthPlaceID = e_employees.BirthPlaceID
WHERE d_divisions.DivisionID = 1;

it works good, but problem is that some of my employees dont have passgivers...this field is empty, some of them have empty field of birthplaceID... but in my query i want to see everyone! (if field is empty, i want to see empty field in my query table)

is this possible? if yes then how should i change my SQL code? what to add?

thanks

removed the d table because it was performing no useful purpose in this query

removed the unnecessary parentheses (note: if you really want them, then perhaps we should move this thread out of the ANSI SQL forum and into the Microsoft Access forum)