Issue with syntax in FROM Clause

heisswa · April 20th, 2012, 07:43 PM

Hello all,

I have created a SQL statement and have changed it a bit to work with VBA in word via a macro. I've been having difficulty finding a good reference which explains how the syntax should be set in a FROM clause inner joining data from more than 2 tables. I have the following code so far:

strStudents = "SELECT tblStudentNo, fldLastName, fldDepartmentName, fldTelephone, fldClassName "

strStudents = strStudents & "FROM [tblDepartments] INNER JOIN " & "[tblStudents] INNER JOIN tblClasses INNER JOIN tblStudentsAndClasses ON "

strStudents = strStudents & "[tblStudents].fldStudentNo = tblStudentsAndClasses.fldStudentNo ON "

strStudents = strStudents & "[tblDepartments].fldDepartmentNo = tblStudents.fldDeptNo "

strStudents = strStudents & "WHERE fldClassName= '" & strVar & "' ORBER BY fldLastName"

I have been unable to find a good site that shows what the syntax should typically look like in this case. Can someone please point me in the right direction?

Cheers,
Heisswa

gjgriffith · April 21st, 2012, 05:59 AM

Hello Heisswa,

Thanks you for posting in the "Microsoft Access 2010 Programmer's Reference" book forum. I had a chance to look over your question here and even though it is not really about this book, I'll see if I can help

! Specifically you asked:

Quote:

I've been having difficulty finding a good reference which explains how the syntax should be set in a FROM clause inner joining data from more than 2 tables.
...
I have been unable to find a good site that shows what the syntax should typically look like in this case. Can someone please point me in the right direction?

Hmm, I had a look at your query here...it seems very complex and looks like you have at least 4 tables with joins, even though they aren't all listed here, which is probably why you are having a problem! In this case, what I like to do when I have a really complex query, is just create the query in Access using Query Design view mode and get it just the way I want it to work. Then, once I have the query correct, I switch to SQL view mode, then copy, paste and the SQL code generated by Access, and fix up for VBA. Does that make sense?

Otherwise, I'm not sure about a website to provide exactly what you are looking for. One book I might recommend would be "Beginning SQL", which you can see here:
http://www.wrox.com/WileyCDA/WroxTit...764577328.html

And one site I would recommend would be W3Schools, they have a GREAT SQL tutorial at:
http://www.w3schools.com/sql/default.asp

But really, in this case, it would probably just be simplest to design the Query in Access using Design view mode and then switch to SQL view mode to get the specific SQL that you need! Anyway, I hope this helps and hopefully you get this problem all worked out using Access!

Regards,

malcolmdixon · April 21st, 2012, 07:15 AM

Hi Heisswa,

You have the syntax wrong for each INNER JOIN as it should be followed by the ON clause. Also, you only have 2 ON clauses when there should be 3 as you have 4 tables! I think you missed the join condition from tblStudentsAndClasses to tblClasses.

Using alternative JOIN syntax you could have your query similar to the following:
SELECT s.tblStudentNo, s.fldLastName, d.fldDepartmentName, d.fldTelephone, c.fldClassName
FROM tblDepartments d, tblStudents s, tblClasses c, tblStudentsAndClasses sc
WHERE c.fldClassName = ?
AND s.fldStudentNo = sc.fldStudentNo
AND sc.fldClassId = c.fldClassId
AND s.fldDeptNo = d.fldDepartmentNo
ORDER BY fldLastName

The ? is just the parameter, which you would replace with your variable strVar when building your SQL string. I have also assumed that the primary key for the tblClasses table is fldClassId and the foreign key is likewise in tblStudentsAndClasses. Also, I have used aliases to simplify the SQL. I have also assumed the attribute fldTelephone is from the tblDepartments table.

HTH a little bit!

Malc.

heisswa · April 21st, 2012, 07:11 PM

Hey guys, thanks for the reply. I already used Access to build a query in design mode. I got the following from Access 2010:

Code:

SELECT tblStudents.fldStudentNo, tblStudents.fldFirstName, tblStudents.fldLastName, tblDepartments.fldDepartmentName, tblStudents.fldTelephone, tblClasses.fldClassName

FROM tblDepartments INNER JOIN (tblStudents INNER JOIN (tblClasses INNER JOIN tblStudentsAndClasses ON tblClasses.fldClassNo = tblStudentsAndClasses.fldClassNo) ON tblStudents.fldStudentNo = tblStudentsAndClasses.fldStudentNo) ON tblDepartments.fldDepartmentNo = tblStudents.fldDeptNo

WHERE (((tblClasses.fldClassName) = "Windows XP Level 1"))

ORDER BY tblStudents.fldLastName;

I will take another look at what I already have and make a couple adjustments per your suggestion, Malcolm.

Heisswa