If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with a simple query
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? |
#2
|
|||
|
|||
Help with a simple query
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 EAB1977 wrote: 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? |
#3
|
|||
|
|||
Help with a simple query
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... -- Daryl S "EAB1977" wrote: 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? . |
#4
|
|||
|
|||
Help with a simple query
John's second query did the trick. Thanks!
Eric |
Thread Tools | |
Display Modes | |
|
|