Null value in query

[Craig W]

I have an Access 2000 database that has one
table 'tblCourse' and a table 'tblRegistration'.

They are linked via a Primary key 'CourseID'
in 'tblCourse' to a foreign key 'CourseID' in the
tblRegistration, this is a one to many relationship. The
Primary key in the tblRegistration is RegistrationID.

When I construct a query that has 'CourseID' and then
e.g. "CountRegistrationID: IIf(IsNull(Count
([tblRegistration].[RegistrationID]),0, Count
([tblRegistration].[RegistrationID]))" as the two fields,
it will not include the courses that have no registrations
in them.

What I am wanting to obtain is a list of 'all' the courses
including the ones that don't have any registrations in
them.

Do you have any ideas as to how to do this, or is it not
possible?

Frustrated.

 

[Steve Schapel]

Craig,

You need to make the join in your query between the tabls as a Left
Join. In query design, double-click the join line between the tables.
This should open the Join Properties dialog. Select the option that
defines "all records from Courses and matching tables from
Registration". You will then see the join line in the query design
window will have an arrowhead on one end.

You do not need to make a calculated field in the way you have. Make
the query into a Totals Query (select Totals from the View menu), put
the CourseID from the Courses table into the grid, and leave the entry
in the Totals row of this field as Group By. And Then add the
RegistrationID field to the grid, and in the Totals row enter Count.
Voila!

- Steve Schapel, Microsoft Access MVP

 

[John Vinson]

What I am wanting to obtain is a list of 'all' the courses
including the ones that don't have any registrations in
them.

Use an "Outer Join" query. Create a Query with the Course table linked
to the Registrations table, select the join line, and choose Option 2
(or 3) - "Show all records in Courses and matching records in
Registration".