Ms-access – Query input must contain at least one table or query error

javams access

I'm working on a java program that connects to a Microsoft Access database and displays the data in a GUI. I'm currently trying to code a button that adds a new row to a table called Grade that holds grades for students that exist in another table called Student for courses that exist in a third table called Course.

The condition for adding a new grade is that the student and course already exist in another table. I thought I had gotten the SQL statement right but when I run it, an error in access appears that says "Query must contain at least one table or query".

Here is my SQL statement:

INSERT INTO Grade (StudentID,CourseID,Semester,Year,Grade)
VALUES((SELECT StudentID FROM Student WHERE StudentID = 5812356),(SELECT CourseID FROM Course WHERE CourseID = 'CS101'), 'Fall', '2016','B');

The StudentID and CourseID specified exist in the Student and Course tables.

I don't understand this error since I have specified which table I'm trying to add to and I have written out a query.

I appreciate any help given.

Best Answer

I think you've got your VALUES clause a bit mixed up.

Is this what you're after?

INSERT INTO Grade 
       (StudentID, CourseID, Semester, Year, Grade) 
SELECT s.StudentID, c.CourseID, 'Fall', '2016','B'
FROM Student s
JOIN Course c
ON s.StudentId = c.StudentId
WHERE s.StudentID = 5812356
AND c.CourseID = 'CS101';
Related Question