Counting Records

[Wavona]

Good morning everyone,
I have a query that shows the following:-

1) List of courses (e.g. Art and Its History) (called CourseName)
2) Number of students allowed on the course (e.g. 12) (called CourseCap)
3) Number of students currently allocated to the course (a count of a field called StudentID)
4) The difference between (2) and (3), so if a course allows 12 students, and we have 8 allocated to it, the query returns 4 (an expression, which is: Diff: [qryCappedCourses]![CourseCap]-[qryStudentIDCount]![CountofStudentID])

My problem is this. Some of our courses do not have a limit on the number of students who can be allocated, so the CourseCap field for these courses is set to 0. When I run the query, therefore, it only returns those courses that have a cap AND students allocated to it, whereas I want the query to return ALL courses, whether they have students allocated to it or not.

How do I do that please? I seem to be going round in circles.

Many thanks!
Anna

 

[pr2-eugin]

So I take the courseTable and the studentsTable are using a INNER JOIN in this Query? If so change it to RIGHT JOIN.. That will list all rows from the right side table and the rows where an match is found for the left side table..

 

[Wavona]

Thanks for your help Paul, but changing the join type hasn't worked.

I think it may be because the query's structure is a bit complicated. I'll try and explain.

I created a query (qryCappedCourse) that comprises two tables:-

- Classes
- Instructors and Classes

This merges the course name with the details of its convenor, setter and reviser.

I then wanted a query that would return a count of how many students had taken up each course, so I created a query (qryStudentIDCount) that returns the classID, classcode, classname, and count of how many students have taken up the class (e.g. 10).

I then I created a query (qryDifferencecaps) that returned the difference between the cap allocated to each class (e.g.12) and the number of students allocated to the class (e.g. 8). So, this query shows the classname, ID and code, how many students are allowed on it (coursecap), how many students have been allocated (countofstudentid), and then the difference between the two (diff), which is the expression I referred to earlier.

I created these different queries because I was advised that it would be best to have different queries doing each bit of the calculation, and because I needed to be able to see, on my courses & students form, the course's cap, how many students had taken the course, and how many places were left.

Now, I totally understand that the query is returning only those records where a class has been allocated students, and I can see that that is a join problem, but I also need to see those courses where students have yet to be allocated, and changing the join has not changed that.

I do hope I am making sense Paul, and look forward to hearing from you again.

Many thanks,
Anna

 

[highandwild]

Hi Anna

Please do not go round in circles, dizzy ladies and programming are just not compatible!!


Please see attached database for an idea. If you need a 2003 database just let me know.


If a course is run more than once then you will need a table that represents
course occurrences and that would change the queries but I can explain if required.


HighAndWild

 

[Wavona]

This is great, many thanks High & Wild!

I'll take a look when I've stopped being dizzy and let you know how I got on.

BW,
Anna

 

[namliam]

Pft posted in wrong thread

 

[Wavona]

Hello again High & Wild,

Thanks again for your sample database, which was helpful to an extent. However, it did not return the records I wanted.

The problem seems to be that Access is only returning records where there are students allocated to a course, but it won't return records if the course has 0 capacity and NO students allocated to it.

Does this make sense? Hope so.

Thanks again,
Anna

 

[namliam]

That is probably because the query in question is an inner join (has a flat line between the two tables).

Try right clicking the line and changing the option to the "proper" outerjoin version (read the tool tips, or try the 2 other options and see what they do)

 

[highandwild]

I think that this needs to be driven from the Course table so that you can see courses where there are no bookings.

Telling me which columns you would like to see in the query result will help me confirm this.

 

[Wavona]

I think that this needs to be driven from the Course table so that you can see courses where there are no bookings. Telling me which columns you would like to see in the query result will help me confirm this.

Thanks.

The columns you have in your own query are the columns I want to see in the query result.

The columns in your "qry_Results" are the columns I want to see. However,
in the "CourseName" column I also want to see listed those courses where the "NumberofBookings" column is 0 because no one has booked a place on these courses yet, e.g.:-

COURSE
12

COURSECAPACITY
36

NUMBEROFBOOKINGS
0

UNBOOKEDPLACES
36

Hope this clarifies things for you.

Thanks again for your help.
Anna

 

[redalert]

If you add a new course, “Course 6”, to the the “tbl_Courses” table and rerun the “qry_Results” query then you will see that “Course 6” is displayed at the bottom.


Where there are no bookings for a course then what do want to appear in the NumberOfBookings and Unbooked Places columns, blank as with Course 6?


Where there is no course capacity then how do you want to represent the Unbooked Places? Not as a minus figure as with Course 5 I suggest.


From 'highandwild' as awaiting administrators to change email setting.