CODEWITHSUNDEEP
sqlwindowsms-access
Tobi0812
Tobi0812

Reputation: 15

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

Upvotes: 1

Views: 79

Answers (2)

Tobi0812
Tobi0812

Reputation: 15

Our "MitarbeiterRollen" table: https://i.sstatic.net/7pDct.png

Our "Gehaltsabrechnung" table https://i.sstatic.net/dUKWd.png

Our "Rolle" table: https://i.sstatic.net/bhmYn.png

Our "Mitarbeiter" table: https://i.sstatic.net/yniNv.png

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

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.

Upvotes: 1

Related Questions