Find the names of the suppliers who supply all the parts in MS Access

I have three tables:

SPB (ID, Part, Supplier), Suppliers (ID, Name), Parts (ID, Part)

I want to find the names of the suppliers who supply all the parts in MS Access.

This code does not work properly:

SELECT Suppliers.ID, Parts.ID
FROM Suppliers INNER JOIN (Parts INNER JOIN SPB ON Parts.ID = SPB.Part) ON Suppliers.ID = SPB.Supplier
WHERE 
 SPB.Part IN 
 (SELECT SPB.Part
 FROM SPB
 WHERE Suppliers.ID = SPB.Supplier)
AND
 SPB.Part IN 
 (SELECT Parts.ID
 FROM Parts
 WHERE Parts.ID)

What could be done better?

Answer

You need a CROSS join of Suppliers and Parts and a LEFT join to SPB.
Then you group by supplier and set the condition in the HAVING clause:

SELECT t.supplierID, t.name
FROM (
  SELECT s.ID AS supplierID, s.name, p.Id AS part_ID
  FROM Suppliers AS s, Parts AS p
) AS t LEFT JOIN SPB AS b ON b.Supplier = t.supplierID AND b.Part = t.part_ID
GROUP BY t.supplierID, t.name
HAVING COUNT(*) = COUNT(b.Part)