sql - Issue Related to Join in MS Access Database

Question

I am using the following query in an MS Access database:

SELECT SD.RollNo, SD.Name , ED.ExamName, (
    SELECT count(*) 
    FROM (
        SELECT DISTINCT innerED.StudentId 
        FROM ExamDetails innerED 
        WHERE innerED.StudentId=SD.StudentId 
    )
) AS StudentId
FROM StudentDetails SD 
LEFT OUTER JOIN ExamDetails ED 
    ON SD.StudentId= ED.StudentId

Whenever I execute this query, a dialog box comes up and asks for the value of the parameter SD.StudentId. Why is it asking for this, and how do I stop it from doing so?

Answer 1

MS Access does not understand the SELECT statement on the Count (*) Aggregate. To Access the SQL Statement looks like this.

 SELECT DISTINCT innerED.StudentId
 FROM ExamDetails innerED 
 WHERE innerED.StudentId=SD.StudentId 

Because the alias AS STUDENTID comes after the end of the statement, this Select statement doesn't recognize it, so it has no idea what .StudendID is so it assumes it's a parameter.

MS Access, when faced with a parameter that has not been identified in the query itself will prompt the user for a value.

Rewrite the query so that this Select statement can identify all the table sources.