Access – Query not executable

The error is “The query must be based on at least one table or query”.

The query:

INSERT INTO Gehaltsabrechnung (IBAN, MitarbeiterID, Betrag) VALUES (
    (SELECT IBAN FROM Mitarbeiter WHERE MitarbeiterID = 2), 2,
    (SELECT SUM(Stunden_Gearbeitet * Stundenlohn) FROM 
        (mitarbeiterrollen INNER JOIN ROLLE ON mitarbeiterrollen.RolleID = rolle.RolleID) 
     WHERE MitarbeiterID = 2)
)

Our “Gehaltsabrechnung” table

Answer

You want INSERT . . . SELECT. In MS Access, this would look like:

INSERT INTO Gehaltsabrechnung (IBAN, MitarbeiterID, Betrag)
    SELECT m.IBAN, m.MitarbeiterID,
           (SELECT SUM(Stunden_Gearbeitet * Stundenlohn) 
            FROM mitarbeiterrollen INNER JOIN
                 ROLLE
                 ON mitarbeiterrollen.RolleID = rolle.RolleID
            WHERE MitarbeiterID = m.MitarbeiterID
           )
    FROM Mitarbeiter as m
    WHERE m.MitarbeiterID = 2;

Notice that I replaced most of the 2s with references to m.MitarbeiterID. This helps ensure that typos won’t affect the query.

I would also recommend using qualified column names in the subquery, but this version should work.