November 8, 2008 at 11:57 pm
I am trying to generate a report from the output of the sql below but passing the staff ID and the Lesson Date as a parameter on a form. How do I do that?
SELECT Staff.Staff_Id, Lesson_Schedule.Lesson_Date, Student.Student_Id, Student.First_Name AS Student_First_Name, Student.Last_Name AS Student_Last_Name, Lesson_Time.Lesson_Time, Booking.Group, Lesson_Type.Lesson_Desc
FROM Student INNER JOIN (Staff INNER JOIN (Lesson_Time INNER JOIN (((Booking INNER JOIN Session_Information ON Booking.Booking_Id = Session_Information.Booking_Id) INNER JOIN Lesson_Schedule ON Session_Information.Lesson_TypeId = Lesson_Schedule.Lesson_TypeId) INNER JOIN Lesson_Type ON Session_Information.Lesson_TypeId = Lesson_Type.Lesson_TypeId) ON Lesson_Time.LessonTime_Id = Booking.LessonTime_Id) ON Staff.Staff_Id = Booking.Staff_Id) ON Student.Student_Id = Booking.Student_Id;
November 9, 2008 at 9:53 am
For starters, you don't need to nest your inner joins. It makes them a LOT easier to read and work with when you do something like this (You can change the formatting some if you want, mine is different than a lot of people's)
[font="Courier New"]CREATE PROCEDURE MyProc(
@StaffID INT,
@LessonDate DATETIME)
AS
SELECT Staff.Staff_Id,
Lesson_Schedule.Lesson_Date,
Student.Student_Id,
Student.First_Name AS Student_First_Name,
Student.Last_Name AS Student_Last_Name,
Lesson_Time.Lesson_Time,
Booking.GROUP,
Lesson_Type.Lesson_Desc
FROM Student
INNER JOIN Booking ON Student.Student_Id = Booking.Student_Id;
INNER JOIN Staff ON Staff.Staff_Id = Booking.Staff_Id
INNER JOIN Lesson_Time ON Lesson_Time.LessonTime_Id = Booking.LessonTime_Id
INNER JOIN Session_Information ON Booking.Booking_Id = Session_Information.Booking_Id
INNER JOIN Lesson_Schedule ON Session_Information.Lesson_TypeId = Lesson_Schedule.Lesson_TypeId
INNER JOIN Lesson_Type ON Session_Information.Lesson_TypeId = Lesson_Type.Lesson_TypeId
WHERE Staff.Staff_ID = @StaffID AND Lesson_Scedule.Lesson_Date = @LessonDate
[/font]
In the above code, I have created a stored procedure. You would call this stored procedure and pass it the parameters @StaffID and @LessonDate. The specifics on how to pass those parameters depends on what you're using as a front end. From within Query analyzer you could simply do:
exec MyProc @StaffID = 25, @LessonDate = '11/9/2008'
Also, keep in mind that if your lesson date is stored with the time and you don't pass in exactly the right time, you won't get any rows. Decide what you want to return / how you want to manipulate the dates and we can help you write the code for it.
November 9, 2008 at 10:21 am
First of all thank you for your help.
The layout of the code you provided is much more easiers to read.
In the front end I am trying to create a form with Staff_Id and a Date field where I can select and generate a report showing all appointments for that date. The Staff Id and the Date should be the 2 parameters picked from a drop down menu or a combo box.
When the preview report button is clicked then a report with the following fields should appear with the matching data. The fields on the report should be Student_Id, Student_First_Name, Student_Last_Name, Lesson_Time, Lesson_Desc, Group. At the very top of the report I would like for the Staff_First_Name and Staff_Last_Name with the date next to it to appear something resembling a label.
e.g.
Staff: Cindy Sinath 22/10/09
Stu_Id Stu_F_Name Stu_L_Name Lesson_Time Lesson_Desc Group
1 Jack Beanstalk 8am-9am Practical Session 01 1PS
November 9, 2008 at 10:49 am
Cindy,
I just noticed that you are posting Access related questions in SQL Server forums. Please make sure you are posting Access questions in the appropriate forum - or, you are going to get quite a few suggestions that just will not work for you.
Thanks
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply