Need Query To Show 0 If No Records Found (1 Viewer)

sealc · Jul 12, 2017

Hi,

I'm a beginner here, I'm trying to create a query to count the feedback outstanding for a given team leader. Where there is feedback outstanding the below returns the number of items.

When there isn't any feedback the query doesn't return anything. I need the query to return 0 instead. How can I make this work?

The SQL code for the query at the moment is:

Code:

SELECT Count(Feedback.ID) AS CountOfID, UserIDAgentsTLsLocations.TeamLeader, Feedback.Fedback
FROM UserIDAgentsTLsLocations INNER JOIN Feedback ON UserIDAgentsTLsLocations.UserID = Feedback.AgentUsername
GROUP BY UserIDAgentsTLsLocations.TeamLeader, Feedback.Fedback
HAVING (((UserIDAgentsTLsLocations.TeamLeader)="David Green") AND ((Feedback.Fedback)=False));

I'm using MS Access 2010 if that helps.

Any suggestions you can offer would be greatly appreciated.

Many thanks in advance

Minty · Jul 12, 2017

Use a left join on your feed back table to bring the team leader in regardless of any records being returned, and try Nz(Count(Feedback.ID),0) as CountOfID to give you the Zero.

sealc · Jul 12, 2017

Hi Minty,

Thanks for your reply.

I'm not entirely sure what you mean, I've updated the SELECT line but not sure what you mean about the LEFT JOIN?

Code:

SELECT Nz(Count(Feedback.ID),0) AS CountOfID, UserIDAgentsTLsLocations.TeamLeader, Feedback.Fedback
FROM UserIDAgentsTLsLocations INNER JOIN Feedback ON UserIDAgentsTLsLocations.UserID = Feedback.AgentUsername
GROUP BY UserIDAgentsTLsLocations.TeamLeader, Feedback.Fedback
HAVING (((UserIDAgentsTLsLocations.TeamLeader)="Danny Green") AND ((Feedback.Fedback)=False));

Thanks again for your help.

plog · Jul 12, 2017

Actually, since you have criteria on your Feedback table a LEFT JOIN alone will not solve this. You should build a sub-query on Feedback:

Code:

SELECT AgentUsername, COUNT(ID) AS SubFeedbackTotal
FROM Feedback
WHERE Fedback = False
GROUP BY AgentUsername

Save that query as 'sub1' and modify your existing query to use it instead of the Feedback table you currently are:

Code:

SELECT Nz(SUM(sub1.SubFeedbackTotal),0) AS FeedbackTotal, UserIDAgentsTLsLocations.TeamLeader
FROM UserIDAgentsTLsLocations LEFT JOIN sub1 ON UserIDAgentsTLsLocations.UserID = sub1.AgentUsername
WHERE UserIDAgentsTLsLocations.TeamLeader="Danny Green"
GROUP BY UserIDAgentsTLsLocations.TeamLeader

Need Query To Show 0 If No Records Found (1 Viewer)

sealc

New member

Minty

AWF VIP

sealc

New member

plog

Banishment Pending

Similar threads

Users who are viewing this thread