Counting the number of enrollees on a course

[slaterino]

Hi,
I am in the process of designing a database and am having some serious problems coming up with a way of counting the amount of enrollees in a database. This is probably because the database is a little complex. Anyway, if someone can help me with this I would be very grateful.

The database user can create courses that can run for one day or for multiple days. Once a course is create students can be enrolled on that course. What makes this difficult is because if there is a course that runs for five days then we store five entries for that course in a table, each with the date. Then once a student enrols there ID is added to another table along with the ID of every date that that course happens, as well as a yes/no field. This means that we can keep track of attendance on all kinds of different courses.

In addition to storing all this information we need to be able to output the data into something useful. Therefore I am starting off by simply choosing the name of the course and then trying to create a count of how many students are on there. Now I'm having many problems because if a course runs over two days and has two students, if I count the number of students I will get four because each students ID is stored next to every date of the course.

I don't really want to write any more because I'm aware that I might not be making the best sense. I am however hoping that someone does understand this and might have a suggestion of how to fix it. The only way I can think of doing it would be to count only unique student IDs. Does anyone know how you would do a count of only unique information?

Thanks!
Russ

[kimmer]

Group by CourseID, Count StudentID

[slaterino]

I wish it was that simple!