Null fields

[Craig]

I have an Access 2000 database that has a table 'tblCourse' and a
table 'tblRegistratio n'.

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.
"CountRegistrat ionID:
IIf(IsNull(Coun t([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]

Re: Null fields

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" <cwilliamson@ce ntacarebrisbane .net.au> wrote in message
news:626a7207.0 310261726.3404e 8ae@posting.goo gle.com...[color=blue]
> I have an Access 2000 database that has a table 'tblCourse' and a
> table 'tblRegistratio n'.
>
> 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.
> "CountRegistrat ionID:
> IIf(IsNull(Coun t([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.[/color]

[Craig Williamson]

Re: Null fields

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]

Re: Null fields

"Craig Williamson" <cwilliamson@ce ntacarebrisbane .net.au> wrote in message
news:3f9c85c9$0 $196$75868355@n ews.frii.net...[color=blue]
> 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[/color]

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]

Re: Null fields

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(CountOfRegis trations)?

Larry Linson
Microsoft Access MVP

"Craig Williamson" <cwilliamson@ce ntacarebrisbane .net.au> wrote in message
news:3f9c85c9$0 $196$75868355@n ews.frii.net...[color=blue]
> 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![/color]