Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2002-10-10 : 09:27:16
|
Michael writes "I have a SQL 7.0 query:SELECT StudentVolInfo.StuSchoolName, Volunteers.FirstName, Volunteers.LastName, Students.StuFirstName, Students.StuLastName, VolSchool.VolClassification, VolSchool.ActiveSchoolFROM VolSchool INNER JOIN Volunteers ON VolSchool.VolID = Volunteers.VolID RIGHT OUTER JOIN StudentVolInfo ON VolSchool.SchoolName = StudentVolInfo.StuSchoolName AND Volunteers.VolID = StudentVolInfo.VolID LEFT OUTER JOIN Students ON StudentVolInfo.StuID = Students.StuIDWHERE (StudentVolInfo.StuSchoolName LIKE N'eastgate%')However, this query refuses to run on Access, and generates the following error:Syntax error (missing operator) in query expression 'VolSchool.VolID = Volunteers.VolID RIGHT OUTER JOIN StudentVolInfo ON VolSchool.SchoolName = StudentVolInfo.StuSchoolName'I need to solve this as quickly as possible!I'm running SQL Server 7.0 with a SQLserver 2000 Enterprise manager to generate the query. The access DB is version 2000 SP-1" |
|
ewade
Starting Member
36 Posts |
Posted - 2002-10-10 : 15:17:42
|
Are you copying the SQL and pasting it into Access or are you running this as a stored procedure? Would I be correct in assuming that you want to use the output from the query for a report?Access has, in the past, not done a very good job of interacting with data sources other than Access or Excel (and even that was iffy). Access XP solves some of this by allowing you to directly interact with a SQL server, but it sounds like you don't have this luxury.I think the problem is your syntax... Access 97 (and presumably 2000) use RIGHT JOIN and LEFT JOIN rather than RIGHT OUTER JOIN and LEFT OUTER JOIN.Try:SELECT StudentVolInfo.StuSchoolName, Volunteers.FirstName, Volunteers.LastName, Students.StuFirstName, Students.StuLastName, VolSchool.VolClassification, VolSchool.ActiveSchool FROM VolSchool JOIN Volunteers ON VolSchool.VolID = Volunteers.VolID RIGHT JOIN StudentVolInfo ON VolSchool.SchoolName = StudentVolInfo.StuSchoolName AND Volunteers.VolID = StudentVolInfo.VolID LEFT JOIN Students ON StudentVolInfo.StuID = Students.StuID WHERE (StudentVolInfo.StuSchoolName LIKE N'eastgate%') ^ You could also simply use the IS NULL function in the Access QBE view, but you have to place it appropriately on either the right or left side of the equation. The orientation of the relationship line dictates which side is considered right and left in this view.My best suggestion is to trigger a stored procedure from within Access (you can do this from the QBE view also)- it will run faster and you won't have to struggle with syntax incompatibilities.Edited by - ewade on 10/10/2002 15:57:13 |
 |
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-10 : 19:14:07
|
If you have the option look into Access Data Projects.They offer the benefit of a SQL Backend with an Access Front End. |
 |
|
ewade
Starting Member
36 Posts |
Posted - 2002-10-16 : 14:25:09
|
I wanted to explain why I altered the syntax for Access in the manner that I did.Inner joins are the default join type for Access (hence simply using JOIN ... ON rather than INNER JOIN).Never stop learning! |
 |
|
|
|
|
|
|