complex sql statement - 3 tables (1 Viewer)

[and1_hotsauce]

Hi,
I have 3 tables(pictured below). When a student sends an sms I need to check the studentID and password and respond with his/her results.

So far, I have created an sql statement which checks the studentID and passwords and sends a reply with his first name and last name from tblStudentInfo

SELECT * FROM tblStudentInfo WHERE StudentID = & intStdNo & " AND Passwords =n ' " &txtPassword& " ' "

But this will select the FirstName and LastName from tblStudentInfo.

Is there to way to extract CourseName, Results and Grade in the one sql statement?

eg. SELECT results,grade,courseName FROM tblResults AND tblCourseInfo WHERE StudentID = '1111' AND Passwords = 'abc123'
 
note that studentID nor Passwords are contained in either tblResults and tblCourseInfo

 

[vbaInet]

You sure can. Just use the SQL Wizard and select all five fields from the different tables. Open the query in design view, enter criteria in the StudentID and Passwords fields and untick the Show box for those two fields. Go into SQL view and copy the sql statement.

 

[and1_hotsauce]

hI

thanks for your reply.

Are the double quotes and single quotes in the correct place? I have colour coded for readability
CourseName and Grade are both text(string). txtPassword is also text(string).

[COLOR=darkgreen]"[/COLOR]SELECT tblCourseInfo.CourseName, tblResults.Results, tblResults.Grade
 
FROM tblStudentInfo INNER JOIN (tblCourseInfo INNER JOIN tblResults ON tblCourseInfo.CourseNumer = tblResults.CourseNumber)
 ON tblStudentInfo.StudentID = tblResults.StudentID
 
WHERE (((tblStudentInfo.StudentID)= [COLOR=blue]"[/COLOR] & intStdNo & [COLOR=blue]"[/COLOR] ) AND ((tblStudentInfo.Passwords)=[COLOR=red]'[/COLOR] [COLOR=lime]"[/COLOR] & txtPassword & [COLOR=lime]"[/COLOR] [COLOR=red]'[/COLOR])) [COLOR=darkgreen]"[/COLOR]

At the moment I have left spaces for eadability but I will join and make one complete sentece. I am not too familiar with single and double quotes hence I tred not to use concatenation to combine the statements i.e. SELECT & FROM & WHERE

 

[vbaInet]

Yep, the syntax is correct, but it won't return any results because there's a leading and trailing space surrounding the password. That WHERE line should be:

WHERE (((tblStudentInfo.StudentID)= " & intStdNo & " ) AND ((tblStudentInfo.Passwords)=[COLOR=Red]'"[/COLOR] & txtPassword & [COLOR=Red]"'[/COLOR])) "