Null fields

[Craig]

I have an Access 2000 database that has a 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.

[Larry Linson]

Perhaps I am missing something, but can't you count the Courses in
tblCourse? It'd seem to me to be awfully difficult to count
non-registrations in the Registration table, unless you preallocate a record
per seat in each class or something.

Larry Linson
Microsoft Access MVP
"Craig" <cw*********@centacarebrisbane.net.au> wrote in message
news:62**************************@posting.google.c om...

I have an Access 2000 database that has a 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.

[Craig Williamson]

I am wanting to count the number of registrations against the course and
need to indicate to people if the course has 0 or more participants
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rick Brandt]

"Craig Williamson" <cw*********@centacarebrisbane.net.au> wrote in message
news:3f*********************@news.frii.net...

I am wanting to count the number of registrations against the course and
need to indicate to people if the course has 0 or more participants

What kind of join are you using between the tables? It needs to be an outer
join. In the query design window it would have an arrow pointing towards the
table that might not have matching records. Double-click the line to change it.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Larry Linson]

How about a Totals Query, grouped by Course, joined with a Query of the
Course table, "all records from Course and only those that match from the
Totals Query", and a calculated field of NZ(CountOfRegistrations)?

Larry Linson
Microsoft Access MVP

"Craig Williamson" <cw*********@centacarebrisbane.net.au> wrote in message
news:3f*********************@news.frii.net...

I am wanting to count the number of registrations against the course and
need to indicate to people if the course has 0 or more participants
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!