Join help

[EAB1977]

Hello everyone,

I have a query that appears that the join is not working correctly. I
have a Access 2000 db that is in Access 2003 SP3. I am trying to
return records for all employees against a weight factor table so I
can create a report in Excel. When I run this query, I get the same
amount of records no matter what what type of join I try. I am getting
frustrated and need some other eyes to look at this.

SELECT WF.ProductLineCode, WF.UserName
FROM tblWeightFactor AS WF RIGHT JOIN tblEmployee AS EMP ON
WF.UserName = EMP.UserName
WHERE (((WF.CompleteDate) Between #12/29/2008# And #12/29/2008#) AND
((EMP.IsCQATech)=True));

 

[tina]

well, what records ARE you getting? are you getting all users, even those
that don't meet the criteria? or are you getting all employees, even those
that have no records in tblWeightFactor?

suggest you start out with a one-table query, using tblWeightFactor. pull
the field(s) you need, and set the criteria, then make sure you're getting
only the records you want. and btw, if you really are pulling records for
only a single date, don't use Between...And, just use =, as

WF.CompleteDate = #12/29/08#

once the query is right, then add tblEmployee and LEFT JOIN from WF.UserName
to EMP.UserName. assuming, of course, that you want to see only records that
meet the criteria set on tblWeightFactor. if you want to see all employee
records, whether or not there's matching records in tblWeightFactor, then
reverse the join.

hth

 

[John Spencer MVP]

The problem is that you are applying criteria to tblWeightFactor and that
negates the outer join (right join).

You can try using a subquery in the from clause

SELECT WF.ProductLineCode
, WF.UserName
FROM
(SELECT UserName, ProductLineCode
FROM tblWeightFactor
WHERE CompleteDate Between #12/29/2008# And #12/29/2008#) AS WF
RIGHT JOIN tblEmployee AS EMP ON
WF.UserName = EMP.UserName
WHERE EMP.IsCQATech=True

Although I suspect you really want to return the UserName from tblEmployee so
you list all employees and the ProductLineCode associated with the employee
for that specific date of 12/29/2008. Why the same date for the between clause?

SELECT WF.ProductLineCode
, EMP.UserName
FROM
(SELECT UserName, ProductLineCode
FROM tblWeightFactor
WHERE CompleteDate Between #12/29/2008# And #12/29/2008#) AS WF
RIGHT JOIN tblEmployee AS EMP ON
WF.UserName = EMP.UserName
WHERE EMP.IsCQATech=True

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

 

[EAB1977]

John,

Your second solution worked. Thank you.

Eric