How to display all fields regardless of match?

Question

I have two tables, ProblemChildren and DailyTable. ProblemChildren consists of an ID and a name. DailyTable consists of a ID, name, and date_hit.

Right now, I'm displaying all ProblemChildren.name where it exists in DailyTable. So if the name "Mike" in ProblemChildren does not have a record in DailyTable, then his name is not outputted.

Again right now, it's diplaying the ProblemChildren.name and the sum of amount times the name can be found in DailyTable. If "Mike" is not found within DailyTable, from ProblemChildren.name, then I still want the output to reflect "Mike" as 0.

How can I do this?

TRANSFORM Sum(DT.[Page Views]) AS [SumOfPage Views]
SELECT PC.CA_NTID AS ntid, PC.CA_Name
FROM tbl_ProblemChildren AS PC 
LEFT JOIN [Daily Table] DT 
ON DT.ntid = PC.CA_NTID 
WHERE DT.[report date] > #6/1/2010#
GROUP BY PC.CA_NTID, PC.CA_Name
PIVOT DT.[report date];

Answer 1

I'm thinking LEFT JOIN would help here.

Answer 2

SELECT p.id, p.name, COUNT(d.date_hit)
FROM ProblemChildren p 
LEFT JOIN DailyTable d
ON d.id = p.id AND d.report_date > mm/dd/yyyy
GROUP BY  p.id, p.name

Or following the update to the question and feedback in the comments maybe

TRANSFORM Sum(DT.[Page Views]) AS [SumOfPage Views]
SELECT 
     PC.CA_NTID AS ntid, 
     PC.CA_Name
FROM tbl_ProblemChildren AS PC 
LEFT JOIN
(
SELECT [Page Views], [report date], ntid
FROM [Daily Table]  
WHERE [report date] > #6/1/2010#
) DT
ON DT.ntid = PC.CA_NTID 
GROUP BY PC.CA_NTID, PC.CA_Name
PIVOT DT.[report date];

Or Tipx's solution

TRANSFORM Sum(DT.[Page Views]) AS [SumOfPage Views]
SELECT PC.CA_NTID AS ntid, PC.CA_Name
FROM tbl_ProblemChildren AS PC 
LEFT JOIN [Daily Table] DT 
ON DT.ntid = PC.CA_NTID 
WHERE DT.[report date] > #6/1/2010# OR DT.[report date] IS NULL
GROUP BY PC.CA_NTID, PC.CA_Name
PIVOT DT.[report date];

Answer 3

select *, count(*) from ProblemChildren pc 
right outer join DailyTable dt on pc.ID=dt.ID group by pc.ID

The code might not be perfect, but the point is that you need an outer join in order to keep values from both. Is setting the output = 0 instead of NULL necessary at the database level? That might mean the same thing to you.

Answer 4

I'm going to hazard a guess that you're using Access' query generator (the graphic interface that lets you build queries).

If so, right click on the line connecting the two tables and choose "Show all rows from ProblemChildren and only those rows from DailyTable that have a match" (or something similar, I don't have Access in front of me) then retry the query.