Count Query - returning Null values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a count query that i am using to measure the registrations on a
course (for each course). All works well except i need the query to return a
0 value when there is nobody registered. At the moment the query omits the
courses with nobody is registered and without anybody registered on them i
can't use the query on my Registrations form. The form wont display records
where there is nobody currently registered. Catch 22 i can't register people
on the courses.
 
If you have a Courses table and it is linked to the registrations table then
you probably just need to double click on the join line and change the join
line to show All records in the courses table and only matching in the
registration table.

If that doesn't fix your problem, Please copy and post the SQL of your
query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
Well it didn't work so here's the SQL

SELECT [Registration].[Course ID], Count([Training Course].[Number enroled])
AS [CountOfNumber enroled]
FROM [Training Course] INNER JOIN Registration ON [Training Course].[Course
ID]=[Registration].[Course ID]
GROUP BY [Registration].[Course ID];

Thanks btw
 
Ok, I think it should have, but it is sometimes confusing to select the
correct option.

Try this modified version of your SQL code. You should be able to copy and
paste it into your query.

SELECT [Registration].[Course ID]
, Count([Training Course].[Number enroled]) AS [CountOfNumber enroled]
FROM [Training Course] RIGHT JOIN Registration
ON [Training Course].[Course ID]=[Registration].[Course ID]
GROUP BY [Registration].[Course ID];


Note that the only change I made was to change the "INNER JOIN" to "RIGHT
JOIN"

Engels said:
Well it didn't work so here's the SQL

SELECT [Registration].[Course ID], Count([Training Course].[Number
enroled])
AS [CountOfNumber enroled]
FROM [Training Course] INNER JOIN Registration ON [Training
Course].[Course
ID]=[Registration].[Course ID]
GROUP BY [Registration].[Course ID];

Thanks btw

John Spencer said:
If you have a Courses table and it is linked to the registrations table
then
you probably just need to double click on the join line and change the
join
line to show All records in the courses table and only matching in the
registration table.

If that doesn't fix your problem, Please copy and post the SQL of your
query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
Click to expand...
Click to expand...
 
Back
Top