Help with a simple query

[EAB1977]

Hi guys and gals,

Can anyone help me with this query? I am having some issues with
pulling in all names even though (it appears) I have the join correct.

SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(tmpReports.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN tmpReports ON tblEmployee.UserName =
tmpReports.UserName
WHERE (((tblEmployee.IsCQATech)=True) AND ((tmpReports.CompleteDate)
Between #2/8/2010# And #2/12/2010#))
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

What am I doing wrong?

 

[John Spencer]

You have defeated the left join by applying criteria to the right side table.

You MAY be able to fix this using a bit more criteria. The problem

SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(tmpReports.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN tmpReports
ON tblEmployee.UserName = tmpReports.UserName
WHERE tblEmployee.IsCQATech=True AND
(tmpReports.CompleteDate Between #2/8/2010# And #2/12/2010#
OR tmpReports.UserName is Null)
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

If that does not work you Can use a sub-query in place of tmpReports but based
on tmpReports.

SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(TEMP.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN
(SELECT UserName, NumOfSets
FROM tmpReports
WHERE tmpReports.CompleteDate Between #2/8/2010# And #2/12/2010#) As TEMP
ON tblEmployee.UserName = TEMP.UserName
WHERE tblEmployee.IsCQATech=True AND
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

IF you save that in design view Access will modify it slightly to use square
brackets and a period to denote the subquery. This means you cannot use any
square brackets in the subquery.
SELECT tblEmployee.EmpRptID, tblEmployee.UserName,
Sum(TEMP.NumOfSets) AS SumOfNumOfSets
FROM tblEmployee LEFT JOIN
[SELECT UserName, NumOfSets
FROM tmpReports
WHERE tmpReports.CompleteDate Between #2/8/2010# And #2/12/2010#]. As TEMP
ON tblEmployee.UserName = TEMP.UserName
WHERE tblEmployee.IsCQATech=True AND
GROUP BY tblEmployee.EmpRptID, tblEmployee.UserName;

If you need to use square brackets in the subquery (for example a parameter
query), you will need to use two queries. The first query would be something
like:

Parameters [Start of Period] DateTime, [End of Period] DateTime;
SELECT UserName, NumOfSets
FROM tmpReports
WHERE tmpReports.CompleteDate Between [Start of Period] And [End of Period]

Then you will use that query as if it were the table tmpReports (in your
original query).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

EAB1977 -

Are you getting an error message? Is the data not what you expect? I see a
couple things that might cause you to get data you don't expect.

Is NumOfSets always populated? If not, change Sum(tmpReport.NumOfSets) to
Sum(nz(tmpReport.NumOfSets,0)).

The LEFT JOIN will work like an INNER JOIN if you put criteria on the
tmpReports table that requires information. You can fix that by changing
((tmpReports.CompleteDate)Between #2/8/2010# And #2/12/2010#)) to
(((tmpReports.CompleteDate) Between #2/8/2010# And #2/12/2010#)) OR
(tmpReports.CompleteDate is null))

If it is something else, tell us what you are getting, and what you expect
to get...

 

[EAB1977]

John's second query did the trick. Thanks!

Eric