Displaying records with no child table entry in relational query 1

[casperthedog]

I have two tables. One of COURSES, and one of DELEGATES.

Each COURSE has a unique number (courseID). Each time a delegate books onto a new course, a new record is created in the delegates table i.e. all the delegate information is repeated as a new record EXCEPT the field 'courseID' which has the ID of the new course they have enrolled on.

I want to run a query which shows all courses and how many delegates are booked on each. If I use both tables and perform a count of delegates with the same course ID, the query only shows those courses for which there is someone booked on. In other words, if there is a course with no bookings, it doesn't appear on the query.

I'd like this:

Basic Hygene 27/01/2002 4 delegates
Basic Hygene 30/01/2002 0 delegates
Basic Hygene 02/02/2002 3 delegates

...instead of this:

Basic Hygene 27/01/2002 4 delegates
Basic Hygene 02/02/2002 3 delegates

 

[Craig0201]

Change your join to an outer join.

Presuming you don't know SQL and are using the QBE window,
right click on the join in the table section of the window to bring up a popup and change your join type to either 2 or 3 (depending on how you've got the tables displayed in the window).

You will also need to put Nz() around your delegate count so it displays 0 instead of null.

Craig