All,
I know I'm missing something pretty simple here, but unfortunately Access SQL isn't my forte. Here's a quick description of the problem:
I have two tables
Author
ID
Author
Report
Author_ID
ID
Subj
PubDate
Not all authors will write reports.
This query works properly:
SELECT DISTINCTROW Author.Author, Count(Report.ID)
AS [Count Reports]
FROM Author LEFT JOIN Report ON Author.ID = Report.Author_ID
GROUP BY Author.Author
The query returns the count of reports written, and 0 if the Author has no reports in the report query.
This query doesn't work:
SELECT DISTINCTROW Author.Author, Count(Report.ID)
AS [Count Reports]
FROM Author LEFT JOIN Report ON Author.ID = Report.Author_ID
WHERE Report.PubDate > #6/20/2010#
GROUP BY Author.Author
It only returns the positive non-zero counts from the Report table. No other authors are included.
What am I missing?
Thanks!