Query to count where table joins

[Jolted_Chicken]

I've a database that logs arrivals of documents at work.
Documents get forwarded to other section of the Division.

I already got a query that selects the documents forward in a particular month. (called qrymonthlyarrivals)

Now I need to count the ones that are forwarded to people within the division and the ones forwarded to outside of the division.
eg
Person 1: 10
Person 2: 0
Person 3: 4
Outside the division: 6

Person 1,2,3 are stored in a table. But outside the division, the names arent in any table (I didnt design this db).

I've been stuck for days.....
A lot of problem is from counting zeros, cos if a person doesnt get any documents, then their name wont appear in qrymonthlyarrivals.

in a nutshell: If (table.name=qrymonthlyarrivals.name,table.name ,"Others")
But i cant get the joins right.

Any help would be much appreciated .

 

[Pat Hartman]

You are going to need a full outer join which has to be simulated in Access by using a left and right join in a union query.

qryFullOuterJoin:
Select p.PersonName, d.Document
From tblPerson As p Left Join tblDocument As d where p.PersonName = d.PersonName
Union Select p.PersonName, d.Document
From tblPerson As p Right Join tblDocument As d where p.PersonName = d.PersonName;

Select PersonName, Count(*)
From qryFullOuterJoin
Group By PersonName;

 

[Jolted_Chicken]

Thank you very much....

although it should be ON instead on Where at the join

 

[Pat Hartman]

All those years of programming and I still need a complier