June 25, 2007 at 11:05 am
Is it possible to make a Count Distinct query that may find no values come up zero. Here is my situation - I'm doing a query for anyone with a certain type of coaching certification that is registered for a tournament for Special Olympics. If I do not find one, the count distinct comes back with no results - (the query shows the field names but no data) - I want to make this zero since no coaches match the criteria. Any help would be appreciated
June 25, 2007 at 11:08 am
Please post your query.
A correlated subquery with count may return no records, but the result should be zero in that case.
N 56°04'39.16"
E 12°55'05.25"
June 25, 2007 at 11:13 am
SELECT COUNT(DISTINCT dbo.[Web-Registration-Golf].StateID) AS Lvl2NonPlayCount, dbo.[Web-Registration-Golf].Delegation AS Facility
FROM dbo.[Web-Registration-Golf] INNER JOIN
dbo.certifs ON dbo.[Web-Registration-Golf].StateID = dbo.certifs.Owner
WHERE (dbo.[Web-Registration-Golf].Unified = 0) AND (dbo.[Web-Registration-Golf].Coach = 1) AND (dbo.certifs.Kind = 'sptf') AND
(dbo.certifs.Misc LIKE '%Cert:gf%')
GROUP BY dbo.[Web-Registration-Golf].Delegation
June 25, 2007 at 11:18 am
SELECT
dbo.[Web-Registration-Golf].Delegation AS Facility
,ISNULL(COUNT(dbo.[Web-Registration-Golf].StateID), 0) AS Lvl2NonPlayCount
FROM
dbo.[Web-Registration-Golf]
LEFT JOIN dbo.certifs
-- INNER JOIN dbo.certifs
ON dbo.[Web-Registration-Golf].StateID = dbo.certifs.Owner
AND (dbo.certifs.Kind = 'sptf')
AND (dbo.certifs.Misc LIKE '%Cert:gf%')
WHERE
(dbo.[Web-Registration-Golf].Unified = 0)
AND (dbo.[Web-Registration-Golf].Coach = 1)
GROUP BY
dbo.[Web-Registration-Golf].Delegation
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 25, 2007 at 11:22 am
After looking closer, are you sure that counting on the StateID is appropriate? Wouldn't you use certifs.Misc or .Kind
just a thought...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 25, 2007 at 11:32 am
They can have any number of certificates - that is why I do it by StateID
June 25, 2007 at 11:43 am
I copied your query and still get no results back - just field names with no data - I'm sure its me but what am I doing wrong. StateID is a 16 varchar that is the primary key for the table since an individual can only register once for a tournament. I uses the value returned for the Level 2 Non Playing coach to compare to a table to see that they 1st have enough Level 2 and do not exceed the number of coaches allowed to play with their team. I'm doing this in sql but use MS Access to conect to the database
June 25, 2007 at 11:49 am
What do you get when you run ...
SELECT TOP 100
dbo.[Web-Registration-Golf].Delegation AS Facility
,dbo.certifs.Owner
FROM
dbo.[Web-Registration-Golf]
LEFT JOIN dbo.certifs
ON dbo.[Web-Registration-Golf].StateID = dbo.certifs.Owner
AND (dbo.certifs.Kind = 'sptf')
AND (dbo.certifs.Misc LIKE '%Cert:gf%')
WHERE
(dbo.[Web-Registration-Golf].Unified = 0)
AND (dbo.[Web-Registration-Golf].Coach = 1)
ORDER BY
dbo.[Web-Registration-Golf].Delegation AS Facility
,dbo.certifs.Owner
If you get records, then this should work...
SELECT
dbo.[Web-Registration-Golf].Delegation AS Facility
,ISNULL(COUNT(DISTINCT dbo.certifs.Owner), 0) AS Lvl2NonPlayCount
FROM
dbo.[Web-Registration-Golf]
LEFT JOIN dbo.certifs
ON dbo.[Web-Registration-Golf].StateID = dbo.certifs.Owner
AND (dbo.certifs.Kind = 'sptf')
AND (dbo.certifs.Misc LIKE '%Cert:gf%')
WHERE
(dbo.[Web-Registration-Golf].Unified = 0)
AND (dbo.[Web-Registration-Golf].Coach = 1)
GROUP BY
dbo.[Web-Registration-Golf].Delegation
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 25, 2007 at 12:01 pm
When I copied the query into MS Access - I got and error talking about the keyword AS. So I removed the second AS facility and it didn't give me an error. However I still get no data. I'll try to explain what is suppose to go on. After a team (Delegation) registers - I count the number of athletes, Unified Partners and coaches. A Playing coach will also be included in the Unified Partner count. I have queries that are suppose to give me a count of the coaches that are level 1 - playing and non-playing and the same for level 2. I then check the info to make sure they have the proper number of coaches (total numer at each level). If they have less than 3 athletes they need 2 level 2 coaches - but both can be unified partners. Once they get above 5 athletes they need to have a certain number of non playing level 1 and level 2 coaches - Hope that makes sense
June 29, 2007 at 2:01 pm
Forgive me if I'm stating the obvious here, but I suspect that, in order to get numeric results, you're going to have to rethink the entire query. Why? Because in order to get results, your record set has to contain records. You have 0 records selected - blame your WHERE clause. It's Friday afternoon, so I can't really give your code the attention it deserves, but counting all levels should force a 0 value where you want it.
You would end up with a null result (no content at all) only when there are no coaches at all in the delegation, which you claim should NOT occur.
June 29, 2007 at 9:16 pm
Michael,
SQL is not SQL... Access has a slightly different set of commands, clauses, and features... you might want to try posting on the Access forum... or, at least give someone the heads up that you're using Access when you post your request on an SQL Server forum... that way, we don't end up spending a lot of time troubleshooting code that will never work in Access.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply