SQL: Invalid Column Name
Posted on 2005-04-12
im currently trying to do some work in sql that was started in access but now needs to be finished in sql as a bug in access means i cant get my rankings tow ork, but when i put the query in sql and run it after ensuring the sytax is ok i get this message:
Mircosoft[ODBC SQL Server Driver]: Invalid Column Name Extract1
but in access i was able to name my column extract1 with no problems, in fact ive tried changed the column alias in case extract was reserved by sql but again whatever alias name i use it will not except yet in access it's fine....ive used the sql syntax:
SELECT dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData,
REPLACE(dbo.GCSE.CriteriaData, CHAR(10), '') AS EXTRACT1, REPLACE(EXTRACT1, '1', '') AS EXTRACT2, REPLACE(EXTRACT2, 2, '') AS GCSE,
dbo.qxStuds.CourseYear AS YEARGROUP
FROM dbo.GCSE INNER JOIN
dbo.qxStuds ON dbo.GCSE.StudentId = dbo.qxStuds.StudentId
GROUP BY dbo.GCSE.SetId, dbo.GCSE.StudentId, dbo.GCSE.ExamId, dbo.GCSE.ModuleId, dbo.GCSE.CriteriaData, REPLACE([ CRITERIADATA ], CHAR(10), ''),
HAVING (dbo.GCSE.SetId = [Current Ye]) AND (dbo.GCSE.ExamId = GCSE) AND (dbo.qxStuds.CourseYear = 12)
ORDER BY dbo.GCSE.StudentId
as you can predict my other column extract2 is also not liked.
what is going on? i thought by using sql i could get around the access ranking problem i have but now even the original query wont work- help!!