Adding Numbers within Records

[kgjeremytw]

Hello,

I need to get help on how to approach generating a report out of a database.

I have a training course database, that keeps track of students and such.

One of my reports deals with creating a fill rate report. When a user enters a date range into a form, a the report will display course title, size and actual number of students that attended.

My problem is figuring out how to add all of the size records (numbers) for each course. A particular course can be offered mulitple times during this period, but I only want one course title to display with the total number from the size and attended columns.

Not sure if there is a function I can use in the SQL string or if I need to build up a bunch of variables and add them together. I'm using MS Access.

Any suggestions or sample code would be greatly appreciated. Thanks - Jeremy

[raf]

Not sure if there is a function I can use in the SQL string or if I need to build up a bunch of variables and add them together.
:) Nice description. But it doesn't tell us much so we can't help you

A particular course can be offered mulitple times during this period, but I only want one course title to display with the total number from the size and attended columns.

OK. So is suppose i can imagen what you want to do (more a less) but we need to know exactly what you want + probably see the code you use and some info on your db-design.

[kgjeremytw]

Okay not a problem.

Here is a high view of the tables that the report is generated from.

tblCourse
courseID, coursetitle, blah
**this table holds course information**

tblOffering
offeringID, offeringDate, courseID, offeringsize, blah
**this table tracks the different deliveries**

tblstudent
studentID, studentName, blah
**Student Table**

tblOfferingStudentLookup
offeringID, studentID, blah
**This table maps enrolled students to offerings of course**

I would like to be able to run a report that would give me the following info:

Report Date Span To and From

courseTitle, number of times offered, offeringsize, # of student attended


My issue is if course A is delivered multiple times during the period of time, I only want the report to list course A with the totals of the offeringsize, and total of the number of students attended.

What I want to get is:

Course A, 58, 57
Course B, 22, 21

As opposed to having every offering listed.

What I currently get is:

Course A, 16, 15
Course A, 20, 18
Course A, 8, 10
Course A, 14, 14
Course B, 10, 10
Course B, 12, 11


Hope this helps. I don't have any code since I'm looking for how to approach this. Let me know if this isn't clear.

[raf]

I see. This is not going to be easy. If you're working with a microsoft db, you should look into data shaping which does exactly what you want. Buth this will bind you on microsoft db's ...

For a more universal approach:
So you want to end with
Course A, 58, 57

Hmm. The first part is easy:
sql="SELECT DISTINCT CourseID, coursetitle, Count (*) as offerincount FROM tblOffering INNER JOIN tblCourse ON tblOffering.courseID = tblCourse.courseID"

Buth then you still need the total number of students:
sql="SELECT tblOfferingStudentLookup.studentID, tblOffering.courseID FROM tblOffering INNER JOIN tblOfferingStudentLookup ON tblOffering.offeringID = tblOfferingStudentLookup.offeringID"

this build a view with all students that take a course with the courseID linked to it.
But we need to get the number in one course. So
sql="SELECT DISTINCT courseID, COUNT(*) as studentcount FROM (SELECT tblOfferingStudentLookup.studentID, tblOffering.courseID FROM tblOffering INNER JOIN tblOfferingStudentLookup ON tblOffering.offeringID = tblOfferingStudentLookup.offeringID)"

And now this needs to be joined with the firts count
So you probably need to store these two views in a temporarely table
Like
sql="SELECT DISTINCT CourseID, coursetitle, Count (*) as offerincount INTO tempoffer FROM tblOffering INNER JOIN tblCourse ON tblOffering.courseID = tblCourse.courseID"
and
sql="SELECT DISTINCT courseID, COUNT(*) as studentcount INTO tempstudents FROM (SELECT tblOfferingStudentLookup.studentID, tblOffering.courseID FROM tblOffering INNER JOIN tblOfferingStudentLookup ON tblOffering.offeringID = tblOfferingStudentLookup.offeringID)"

to finaly run
sql="SELECT tempoffer.CourseID, tempoffer.coursetitle, tempoffer.offerincount, tempstudents.studentcount FROM tempoffer INNER JOIN tempstudents ON tempoffer.CourseID=tempstudents.CourseID"


So you need to run the three last statements + afterwards delete the temporarely files:
sql="DROP tempoffer"
sql="DROP tempstudents"

(I didn't test the statements so it probably needs some experimenting)
You get the idea? But i can't image that these 5 statement are executed quick enough to do this at runtime. If you wan't to generate these stats frequently, you'd better have a table with coursename, number of offerings, number of student, and update this table each time a student or offering is entered or deleted. It will slow the inputproces a very tiny bit down, but this is probably not such big a deal. I treated a similar 'priority' problem here
http://www.codingforums.com/showthread.php?s=&threadid=17608&highlight=frequency

Hope this helps

[kgjeremytw]

Thanks for your help. It will definitely be a learning experience as well as an exercise in patience.