By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Null fields

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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!
Nov 12 '05 #3

P: n/a
"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
Nov 12 '05 #4

P: n/a
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!

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.