Need Zero Values From Query

[Ben Johnson]

My client wants their database to produce a report showing
how many jobs each Real Estate Agent has sent them for a
given date range. They want the report to list all Agents,
regardless of whether the Agent has created any jobs.
Therefore, the Agents that have created no jobs have a zero
value.

Creating the basic query isn't the problem. I just don't
know how to get it to return the zero values.

Relationships: tblAgents to tblReports by AgentID where
all records from tblReports are returned and only matching
records from tblAgents.

Count of jobs is equal to querying tblReports for the
number of job dates for each Agent. With the relationship
in place Agents with zero job dates are not included in the
results. With no relationship I can't get any sensible
results at all.

All help appreciated.

Regards,
Ben Johnson

Results are the same no matter what relationship I use
between the tables.

 

[André Hartmann]

Count of jobs is equal to querying tblReports for the

number of job dates for each Agent. With the relationship
in place Agents with zero job dates are not included in the
results. With no relationship I can't get any sensible
results at all.

This should help:

SELECT AgentId,
(SELECT COUNT(*) FROM tblReports R WHERE
R.agentid=A.agentid)
FROM tblAgents A;

AH

 

[Ben Johnson]

Thanks Andre, I'll give it a go.

-----Original Message-----

This should help:

SELECT AgentId,
(SELECT COUNT(*) FROM tblReports R WHERE
R.agentid=A.agentid)
FROM tblAgents A;

AH



.