show all, including non existing (1 Viewer)

as a quite a newbie in Access, and couldn't find a similar question in this forum, (if I overlooked it , my apologizes for that, as English is not my mother tongue)


I have a table(T_REPORTS) filled in by a form : an administrator fills in which employees have delivered different kind of reports on a specific date.

used table look like :

T_REPORTS
UIN (unique Indentifier Number - Autonumber)
PIN (Person Identifier Number - 8 char text // example : ZG813VRD)
DateR (date Report - date time)
TypeR (Type Report - 2 char text : Example TB, WI, NA, OH, NM)

T_EMPLOYEES
PIN (Person Identifier Number - 8 char text, in this table unique)
1Name (1st name - 16 chars text)
Fname (family name - 24 char text)

a employee can/ may enter more than 1 report of a specific type per month.

now I have an query which results the employees who have delivered a report,
the result of ACCESS query below shows entered reports per month, (of entered Year) and also total of reports per employee
though employees who didn't delivered a report at all are not shown.

I need an overview of all employees despite the fact if reports where filed in or not, and if result field is empty a zero should be shown


TRANSFORM Count(T_REPORTS.TypeR) AS CountOfRTS_a
SELECT [Fname] & ", " & [1Name] AS CName, Count(T_REPORTS.PIN) AS [total of PIN]
FROM T_EMPLOYEES RIGHT JOIN T_REPORTS ON T_EMPLOYEES.PIN = T_REPORTS.PIN
WHERE (((Year([DateR]))=[enter Year (4 digits)]))
GROUP BY [Fname] & ", " & [1Name]
ORDER BY [Fname] & ", " & [1Name]
PIVOT Format([DateR],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


anybody who can help me with this (for me) nasty problem..

thanks in advance

John

P.S.

End User of this query requested to work in "native" Access, preferable without use of VB,
don't know if this can be solved without VB (both ways a challenge for me....)

Well from the SQL you show us you try to do a RIGHT JOIN...
http://www.w3schools.com/sql/sql_join_right.asp

This means : show me all the employees that have reports AND all the reports who doesn't have an employee. (Because reports is on the RIGHT side of employees)

If you want all the employees you need to do a LEFT JOIN (Yes because it is on the LEFT side in your query.)
This will means : show me all the employees that have reports AND all the employees who doesn't have a report.

Hello,

I have a similar problem, then changed my JOIN to LEFT but there was no difference, still not showing users with 0 records input.
I deleted my WHERE statement and works ok.

SELECT T_Users.uUserName AS Technician,
Sum(IIf([Q_Defects].[dCatFK]=1,1,0)) AS AM,
Sum(IIf([Q_Defects].[dCatFK]=2,1,0)) AS PM
FROM T_Users LEFT JOIN Q_Defects ON T_Users.uPK = Q_Defects.dUserFK
WHERE (((DatePart("m",[dDateClosed]))=Forms!F_MainMenu!txtMonth)) Or (((Forms!F_MainMenu!txtMonth) Is Null))
GROUP BY T_Users.uUserName
ORDER BY T_Users.uUserName;

Click to expand...

I don't understand ,why this would be causing a problem?

Can anyone advice please?

Take a look at the problem posted by NauticalGent where he has "'Not In' query" as his thread topic. The problems are not identical, but you could perhaps do what you want in two parts. First, the query that selects the records you want, and then do a second query that implements your transpose.