Report Totals

[gary.newport]

The database I have is a fictitious system for experimental reasons and is for a Driving School. It comprises of 4 entities; Instructor, Student, Vehicle and Booking.

I want to generate a report that shows each instructor, the students they have taught in a particular month and the lessons they had with that student (date and duration). All of this I have working.

In the footer of the form I want to have calculations that works out:

• How many instructors are given in this report (therefore, how many instructors had lessons in the chosen month)
• How many students received lessons
• How many lessons there were in total.
• Total income

Now the latter two are easy since the 3rd one is nothing more than the total number of records from the source query whilst the 4th one is the sum total of the cost for each lesson; again, derived from each record in the query.


It is the first two I am having problems with. The query lists the instructor, the student, the lesson date, the lesson duration and the lesson cost; so for each lesson the instructor's name will appear several times. Since the calculation COUNT is about counting the number of records this will return the total number of records and not the number of unique instructor names there are (my test system reports 5 records for June 2013 with 2 instructors and 3 students; currently my first three totals report a total of 5, rather than 2, 3 and 5).


I thought I would create a new query for each total and run this within the textbox but I either get 5 (the number of records in the main query) despite the query showing me two records when I run it directly, or it reports 0 if I break it (DOH!).


Where am I going wrong?


The query SQL is Access' own so sorry for the mess:

SELECT [tbl_instructor].[txt_first] & " " & [tbl_instructor].[txt_last] AS Instructor, [tbl_student].[txt_first] & " " & [tbl_student].[txt_last] AS Student, tbl_booking.dte_lesson, tbl_booking.tme_lesson, tbl_booking.cur_price, Month([dte_lesson]) AS [Month], Year([dte_lesson]) AS [Year]
FROM (tbl_instructor INNER JOIN tbl_booking ON tbl_instructor.instructor_id = tbl_booking.instructor_id) INNER JOIN tbl_student ON (tbl_student.student_id = tbl_booking.student_id) AND (tbl_instructor.instructor_id = tbl_student.usual_instructor)
GROUP BY [tbl_instructor].[txt_first] & " " & [tbl_instructor].[txt_last], [tbl_student].[txt_first] & " " & [tbl_student].[txt_last], tbl_booking.dte_lesson, tbl_booking.tme_lesson, tbl_booking.cur_price, Month([dte_lesson]), Year([dte_lesson])
HAVING (((Month([dte_lesson]))=[forms]![frm_instructor_report].[txt_month]) AND ((Year([dte_lesson]))=[forms]![frm_instructor_report].[txt_year]));

And the second query is:

SELECT Month([dte_lesson]) AS [Month], Year([dte_lesson]) AS [Year], Count(tbl_instructor.instructor_id) AS CountOfinstructor_id
FROM tbl_instructor INNER JOIN tbl_booking ON tbl_instructor.instructor_id = tbl_booking.instructor_id
GROUP BY Month([dte_lesson]), Year([dte_lesson])
HAVING (((Month([dte_lesson]))=[forms]![frm_instructor_report].[txt_month]) AND ((Year([dte_lesson]))=[forms]![frm_instructor_report].[txt_year]));

 

[CJ_London]

Where am I going wrong?

Without knowing your table structure and relationships it is impossible to advise. Suggest post this information and we can see if we can help