Microsoft SQL Server Query:

SELECT
*
FROM
dbo.EA7STUDENTGRADEBOOKGRADE
INNER JOIN dbo.EA7COURSEGRADINGINFOGRADES ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7COURSEGRADINGINFOGRADESID = dbo.EA7COURSEGRADINGINFOGRADES.EA7COURSEGRADINGINFOGRADESID
INNER JOIN dbo.EA7TRANSLATIONENTRIES ON dbo.EA7COURSEGRADINGINFOGRADES.EA7TRANSLATIONSID = dbo.EA7TRANSLATIONENTRIES.EA7TRANSLATIONSID
INNER JOIN dbo.EA7CLASSES ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7CLASSESID = dbo.EA7CLASSES.EA7CLASSESID
INNER JOIN dbo.EA7COURSES ON dbo.EA7CLASSES.EA7COURSESID = dbo.EA7COURSES.EA7COURSESID
INNER JOIN dbo.EA7SESSIONS ON dbo.EA7CLASSES.EA7SESSIONSID = dbo.EA7SESSIONS.EA7SESSIONSID
INNER JOIN dbo.EA7ACADEMICYEARS ON dbo.EA7SESSIONS.EA7ACADEMICYEARSID = dbo.EA7ACADEMICYEARS.EA7ACADEMICYEARSID
INNER JOIN dbo.EA7STUDENTS ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7STUDENTSID = dbo.EA7STUDENTS.EA7STUDENTSID
INNER JOIN dbo.EA7RECORDS ON dbo.EA7STUDENTS.EA7RECORDSID = dbo.EA7RECORDS.EA7RECORDSID
INNER JOIN dbo.EA7MARKINGCOLUMNS ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7MARKINGCOLUMNSID = dbo.EA7MARKINGCOLUMNS.EA7MARKINGCOLUMNSID
INNER JOIN dbo.TABLEENTRIES ON dbo.EA7MARKINGCOLUMNS.DESCRIPTION = dbo.TABLEENTRIES.TABLEENTRIESID

Microsoft Access Query:


Select r.LASTNAME, dbo.EA7COURSES.COURSENAME, dbo.EA7ACADEMICYEARS.DESCRIPTION,
sg.GRADE, dbo.TABLEENTRIES.DESCRIPTION,

Case when isnumeric(sg.GRADE) = 1 then

(SELECT dbo.EA7TRANSLATIONENTRIES.GRADE

FROM (((((((((dbo.EA7STUDENTGRADEBOOKGRADE

INNER JOIN dbo.EA7COURSEGRADINGINFOGRADES ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7COURSEGRADINGINFOGRADESID = dbo.EA7COURSEGRADINGINFOGRADES.EA7COURSEGRADINGINFOGRADESID)
INNER JOIN dbo.EA7TRANSLATIONENTRIES ON dbo.EA7COURSEGRADINGINFOGRADES.EA7TRANSLATIONSID = dbo.EA7TRANSLATIONENTRIES.EA7TRANSLATIONSID)
INNER JOIN dbo.EA7CLASSES ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7CLASSESID = dbo.EA7CLASSES.EA7CLASSESID)
INNER JOIN dbo.EA7COURSES ON dbo.EA7CLASSES.EA7COURSESID = dbo.EA7COURSES.EA7COURSESID)
INNER JOIN dbo.EA7SESSIONS ON dbo.EA7CLASSES.EA7SESSIONSID = dbo.EA7SESSIONS.EA7SESSIONSID)
INNER JOIN dbo.EA7ACADEMICYEARS ON dbo.EA7SESSIONS.EA7ACADEMICYEARSID = dbo.EA7ACADEMICYEARS.EA7ACADEMICYEARSID)
INNER JOIN dbo.EA7STUDENTS ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7STUDENTSID = dbo.EA7STUDENTS.EA7STUDENTSID)
INNER JOIN dbo.EA7RECORDS ON dbo.EA7STUDENTS.EA7RECORDSID = dbo.EA7RECORDS.EA7RECORDSID)
INNER JOIN dbo.EA7MARKINGCOLUMNS ON dbo.EA7STUDENTGRADEBOOKGRADE.EA7MARKINGCOLUMNSID = dbo.EA7MARKINGCOLUMNS.EA7MARKINGCOLUMNSID)
INNER JOIN dbo.TABLEENTRIES ON dbo.EA7MARKINGCOLUMNS.DESCRIPTION = dbo.TABLEENTRIES.TABLEENTRIESID

WHERE ((dbo.EA7ACADEMICYEARS.DESCRIPTION)='FALL 03 THRU SUMMER 04 UPPER SCHOOL')
AND ((dbo.TABLEENTRIES.DESCRIPTION)='1st QUARTER')
AND ((dbo.EA7TRANSLATIONENTRIES.EA7TRANSLATIONENTRIESID)=(SELECT TOP 1 dbo.EA7TRANSLATIONENTRIES.EA7TRANSLATIONENTRIESID FROM dbo.EA7TRANSLATIONENTRIES WHERE Convert(int,dbo.EA7STUDENTGRADEBOOKGRADE.GRADE) >= dbo.EA7TRANSLATIONENTRIES.CUTOFFGRADE ORDER BY dbo.EA7TRANSLATIONENTRIES.SEQUENCE))
And dbo.EA7STUDENTGRADEBOOKGRADE.EA7STUDENTSID = s.EA7STUDENTSID
And dbo.EA7STUDENTGRADEBOOKGRADE.EA7CLASSESID = c.EA7CLASSESID
And dbo.EA7STUDENTGRADEBOOKGRADE.EA7MARKINGCOLUMNSID = mc.EA7MARKINGCOLUMNSID
AND dbo.EA7STUDENTGRADEBOOKGRADE.EA7COURSEGRADINGINFOGRADESID = dbo.EA7COURSEGRADINGINFOGRADES.EA7COURSEGRADINGINFOGRADESID
)
else sg.GRADE
end

FROM (((((((dbo.EA7STUDENTGRADEBOOKGRADE sg

INNER JOIN dbo.EA7STUDENTS s ON sg.EA7STUDENTSID = s.EA7STUDENTSID)
INNER JOIN dbo.EA7RECORDS r ON s.EA7RECORDSID = r.EA7RECORDSID)
INNER JOIN dbo.EA7MARKINGCOLUMNS mc ON sg.EA7MARKINGCOLUMNSID = mc.EA7MARKINGCOLUMNSID)
INNER JOIN dbo.TABLEENTRIES ON mc.DESCRIPTION = dbo.TABLEENTRIES.TABLEENTRIESID
INNER JOIN dbo.EA7CLASSES c ON sg.EA7CLASSESID = c.EA7CLASSESID)
INNER JOIN dbo.EA7COURSES ON c.EA7COURSESID = dbo.EA7COURSES.EA7COURSESID)
INNER JOIN dbo.EA7SESSIONS ON c.EA7SESSIONSID = dbo.EA7SESSIONS.EA7SESSIONSID)
INNER JOIN dbo.EA7ACADEMICYEARS ON dbo.EA7SESSIONS.EA7ACADEMICYEARSID = dbo.EA7ACADEMICYEARS.EA7ACADEMICYEARSID)

WHERE ((dbo.EA7ACADEMICYEARS.DESCRIPTION)='FALL 03 THRU SUMMER 04 UPPER SCHOOL') AND ((dbo.TABLEENTRIES.DESCRIPTION)='1st QUARTER')
 

Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.