How get my report design working?
I have a report card report that i need to get working. I have the school name , address , and students name and report card information at top correct but the classes are not displaying correctly.
I will post the sql query here but if you need to see my report i can post a picture.
SELECT dbo.Course.number, dbo.Course.name, dbo.Calendar.endYear, dbo.Calendar.startDate, dbo.Calendar.endDate
FROM dbo.Calendar INNER JOIN
dbo.Course ON dbo.Calendar.calendarID = dbo.Course.calendarID
WHERE year(getdate()) BETWEEN year(dbo.Calendar.startDate) AND year(dbo.Calendar.endDate)
My objective is to get classes from a specific school year to show up for a specific student. The students courses and the calendar year are in different tables though. For instance, for john willy i want his 2010 -2011 classes to show up.
Brian Dong replied
I see two things that need to be worked out. The first is finding the proper table to work with. The second is to break down the query so you get the data you want.
CROSS JOINs create cartesians which will slow down your query because it's generating extra records if you do not include a proper where clause. I noticed the Calendar table isn't linked to a year so it. Things like this will cause you problems. If you can try to use INNER or OUTER joins.
From your first post you wanted to generate a report card listing all the courses for each student in the current school year. Break the query down into pieces. I made up the joins because you didn't link the tables so you will need to figure that out.
Start with the student like:
Select v_SchoolCurrent.name , v_SchoolCurrent.address , v_SchoolCurrent.city , v_SchoolCurrent.state , v_SchoolCurrent.zip , v_SchoolCurrent.phone , student.studentNumber , student.lastName , student.firstName , student.middleName From garrard.dbo.v_SchoolCurrent INNER JOIN garrard.dbo.student On v_SchoolCurrent.UID = student.School_UID WHERE v_SchoolCurrent.name='GARRARD COUNTY HIGH SCHOOL'
This should return you a list of the students in your school.
Now try adding the calendar table to it:
Select v_SchoolCurrent.name , v_SchoolCurrent.address , v_SchoolCurrent.city , v_SchoolCurrent.state , v_SchoolCurrent.zip , v_SchoolCurrent.phone , student.studentNumber , student.lastName , student.firstName , student.middleName , Calendar.startDate , Calendar.endDate From (garrard.dbo.v_SchoolCurrent INNER JOIN garrard.dbo.student On v_SchoolCurrent.UID = student.School_UID) INNER JOIN garrard.dbo.Calendar On student.School_Year = Calendar.School_Year Where WHERE v_SchoolCurrent.name='GARRARD COUNTY HIGH SCHOOL' And Calendar.School_Year = 2011
Add the next table and check the results after each addition.