INTERSECT multiple subqueries in MS-Access (without INTERSECT keyword)

Question

I need to intersect multiple subqueries in MS Access. Unfortunately, Access-SQL does not support the INTERSECT keyword. I understand, how we can use INNER JOIN of two tables to get the intersection we want. But how can I programmatically create a query that will make the intersection of N subqueries?

To be more specific: I have a table of Employees and a table of Specializations. Each employee can have multiple specializations which means there's a many to many relationship between Employees and Specializations represented by an additional table which contains the ids of employees and specializations, nothing complicated.

Now let's say that I want a list of employees, all of which have all of the specializations specified somewhere. In any other SQL engine I would simply create a list of subqueries and used the INTERSECTION keyword to "join" these subqueries together creating something like:

SELECT * FROM ( 
        (SELECT id, first_name, last_name FROM Employees JOIN Emp_spec 
            ON Employee.id = Emp_spec.spec_id WHERE Emp_spec.spec_id=x_1 )
        INTERSECT 
        ... 
        INTERSECT
        (SELECT id, first_name, last_name FROM Employees JOIN Emp_spec 
            ON Employee.id = Emp_spec.spec_id WHERE Emp_spec.spec_id=x_n )
);

, where x_1,...,x_n represent some ids corresponding to some specializations. This query returns a set of employees, all of which have all the specializations x_1,...,x_n. So how do I create such query in Access without the INTERSECT keyword. I've been trying to write the equivalent query with INNER JOIN but I can't seem to succeed.

Answer 1

Perhaps this is what you have in mind. For test tables [Employees] ...

id  first_name  last_name
--  ----------  ---------
 1  Gord        Thompson 
 2  Homer       Simpson  
 3  Hank        Kingsley 

... and [Emp_spec] ...

emp_id  spec_id
------  -------
     1        1
     1        2
     2        1
     3        1
     3        2

... the query

SELECT * FROM Employees
WHERE id IN (SELECT emp_id FROM Emp_spec WHERE spec_id=1)
    AND id IN (SELECT emp_id FROM Emp_spec WHERE spec_id=2)

returns

id  first_name  last_name
--  ----------  ---------
 1  Gord        Thompson 
 3  Hank        Kingsley