Need to Count Nulls

  • Thread starter Thread starter marywoody
  • Start date Start date
M

marywoody

I'm pretty much a newbie and self taught so I get me in messes all the
time. Right now, I am in desperate need of a query/report that will
return zeros when there are no records. I'm combining two tables into
one query. The first contains a list of sales campaigns. The second a
list of leads. I'm building a query that tells me how many leads have
been generated by each campaign. Currently am using the Count function.
Works fine on those campaigns that have leads, but I also need to know
which campaigns don't have leads. I'm willing to try both the DCount
function and the NZ function, but I'm enough of a novice that I don't
know what to put where? Can somebody paint me a picture?
 
Marywoody,

The key to success here will be to use a Left Join in your query.
Assuming you have a field in your Leads table called CampaignID, which
is the field that links this table to the Campaigns table. If you are
doing this in the design view of the query, double-click on the line
joining the two tables, and from the Join Properties dialog which will
pop up, select the (probably) second option, the one that says something
like all fields from the Campaigns table and only matching records from
the Leads table. OK, now your query should show 0 for the Lead-less
Campaigns. If you look at the SQL view of the query (select SQL from
the View menu) it may look something like this...
SELECT Campaigns.CampaignID, Count(Leads.LeadID) AS LeadsPerCampaign
FROM Campaigns LEFT JOIN Leads ON Campaigns.CampaignID = Leads.CampaignID
GROUP BY Campaigns.CampaignID

If you still can't get it, please post back with a copy/paste of the SQL
of the query you are using so far.
 
Back
Top