Left Join to show a count of zero

[Carlene]

I am doing a project to track marketer progress through offices by zip
codes and i am having problems

I have a results table, a feedback table, and a processing table...

The processing table has an ID, a Zipcode, a marketer name, and dates
related to marketers time spent on the zipcode

The feedback table has an officeid, a processingid, a result and
comments

the results table is a lookup table for feedback - there are 7 types
of results, one of which is setup (basically a sale) and others are
followup, wrong address, etc.

So basically the feedback table says "during this processing period,
this office was visited, a result occured, and here are the marketers
comments.

I created a query that will show all zip codes listed in the feedback
table and their individual count when result.desc = setup

SELECT Processing.ZipCode, Result.Desc, Count(*) AS [Count Of
Feedback]
FROM Result INNER JOIN (Processing INNER JOIN Feedback ON
Processing.ProcessingID = Feedback.ProcessingID) ON Result.ResultID =
Feedback.Result
Where result.desc = "setup"
GROUP BY Processing.ZipCode, Result.Desc;

this query works - except if a marketer got zero setups in a zip code,
it doesnt show up. How can I change/add to this query to get it to do
that...I've been reading that a left join would work but when I try
replacing Inner with Left Access tells me "Join type not supported"

Thanks in Advance,

Carlene

 

[Michel Walsh]

Hi,


There is ambiguity. All records of table Processing must be kept, but
that table is not implied in all the would be outer joins , thus making the
order of evaluation of the join important. On the other hand, your table
Feedback seem to be more naturally the table to preserve, since you do not
want to loose any feedback, isn't it? If so, then the ambiguity is removed:

Change

FROM Result INNER JOIN (Processing INNER JOIN Feedback ON
Processing.ProcessingID = Feedback.ProcessingID) ON Result.ResultID =
Feedback.Result
Where result.desc = "setup"

to:

FROM (Feedback As F LEFT JOIN Processing As P
ON f.ProcessingID=p.ProcessingID)
LEFT JOIN Result As r
ON f.result = r.resultID

WHERE Nz( result.desc = "setup", True)



Doing that you loose the ZipCode without any feedback. If you want a count
of 0 for them, then, from the last query, saved under the name of Q1, and
from the table with all the possible ZipCode, I assume you have one,
ZipCodes (and at least one field, ZipCode):


SELECT ZipCodes.ZipCode, Nz( Q1.[Count Of Feedback] , 0)
FROM ZipCodes LEFT JOIN Q1 ON ZipCodes.ZipCode=Q1.ZipCode




Hoping it may help,
Vanderghast, Access MVP