There’s a varied list of applications that stores data ranging from MS Access 2000 database to MSSQL database. A challenge facing moving data from MS Access to MSSQL Database is the front end application. Though both products are from Microsoft they have a difference in handling SQL syntax and data types. I have faced a task to debug a report that doesn’t show correctly.
Thus, I need to recreate the whole SQL statement. I was surprised to see the old time “INNER JOIN” commands which, I must admit, I am not very comfortable working with. Anyway, the MS Access code is shown below for reference.
SELECT IIF([Faculty].[PositionID]>=1 And [Faculty].[PositionID]<=8,"LONG-TERM","SHORT-TERM") AS Term, Positions.PositionID, Positions.DisplayOrder, Positions.PositionName, Schools.SchoolCode, "T" AS AppointmentTypeCode, Sum(FacultyUnit.Weightage) AS SumOfWeightage
FROM Schools RIGHT JOIN ((Faculty INNER JOIN Positions ON Faculty.PositionID = Positions.PositionID) INNER JOIN FacultyUnit ON Faculty.FacultyID = FacultyUnit.FacultyID) ON Schools.SchoolCode = FacultyUnit.SchoolCode
WHERE (((Faculty.Active)<>0))
GROUP BY IIf([Faculty].[PositionID]>=1 And [Faculty].[PositionID]<=8,"LONG-TERM","SHORT-TERM"), Positions.PositionID, Positions.PositionName, Positions.DisplayOrder, Schools.SchoolCode, "T"
ORDER BY Positions.DisplayOrder, Schools.SchoolCode
Upon seeing the code, I decided it would be easier to create it from scratch using plain SQL syntax. The resulted code which generated the correct output run perfectly fine on MSSQL Query Analyzer and is shown below for reference.
SELECT CASE WHEN f.positionid >= 1 AND f.positionid <= 8 THEN 'LONG-TERM' ELSE 'SHORT-TERM' END AS Term, p.PositionID, p.DisplayOrder, p.PositionName, fu.SchoolCode, 'T' AS AppointmentTypeCode, SUM(fu.weightage) AS SumOfWeightage
FROM faculty f, facultyunit fu, positions p
WHERE f.active = 1 AND f.facultyid = fu.facultyid AND f.positionid = p.positionid AND f.appointmenttypecode IS NOT NULL
GROUP BY f.positionid, p.PositionID, p.displayorder, p.PositionName, fu.schoolcode, f.AppointmentTypeCode
ORDER BY p.displayorder, fu.schoolcode
Excited by the correct result, I pasted the SQL statement in MS Access, but it throws a syntax error.
MS Access Database Syntax Error using CASE WHEN function
Syntax error was caused by CASE WHEN in the SQL statement which is I suppose not supported by MS Access. The solution is to use the original IIF MS Access function. The resulting code is shown below for reference.
SELECT IIF([f].[PositionID]>=1 And [f].[PositionID]<=8,"LONG-TERM","SHORT-TERM") AS Term, p.PositionID, p.DisplayOrder, p.PositionName, fu.SchoolCode, 'T' AS AppointmentTypeCode,
SUM(fu.weightage) AS SumOfWeightage
FROM faculty f, facultyunit fu, positions p
WHERE f.active = 1 AND f.facultyid = fu.facultyid AND f.positionid = p.positionid AND f.appointmenttypecode IS NOT NULL
GROUP BY f.positionid, p.PositionID, p.displayorder, p.PositionName, fu.schoolcode, f.AppointmentTypeCode
ORDER BY p.DisplayOrder, fu.SchoolCode
The report finally show correct result as expected. I am still puzzled why the MS Access SQL statement did not give the correct result. After a closer look I could say that including appointmenttypecode IS NOT NULL in the where clause of the query have solved the incorrect report problem.
