inner join accept null

[topwaya]

I have this query below, I can't figure out how to write it so that it will show results whether or not the user has a corresponding tblAddr.PID, and if they do, whether or not they have any data in the tbleAddr.PhoneW and tbleAddr.PhoneH fields.

SELECT tblPersonal.PID, tblWalk.WalkD, qryFullName.FullName, tblWalk.WalkLoc, tblPersonal.LocW, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt
FROM tblPersonal INNER JOIN ((qryFullName INNER JOIN tblAddr ON qryFullName.PID = tblAddr.PID) INNER JOIN tblWalk ON tblAddr.PID = tblWalk.PID) ON (tblPersonal.PID = tblWalk.PID) AND (tblPersonal.PID = tblAddr.PID)
WHERE (((tblWalk.WalkD) Between [Forms]![fdlgWalkers]![txtStart] And [Forms]![fdlgWalkers]![txtEnd]));

Any help is appreciated!!

 

[r937]

select tblPersonal.PID
     , tblWalk.WalkD
     , qryFullName.FullName
     , tblWalk.WalkLoc
     , tblPersonal.LocW
     , tblPersonal.Dept
     , tblPersonal.LocFC1
     , tblPersonal.LocFC2
     , tblAddr.PhoneW
     , tblAddr.PhoneH
     , tblPersonal.Shirt
  from ((
       tblPersonal 
inner       
  join tblWalk 
    on tblPersonal.PID = tblWalk.PID
       )     
left outer
  join tblAddr 
    on tblPersonal.PID = tblAddr.PID
       )
left outer
  join qryFullName 
    on tblAddr.PID = qryFullName.PID 
 where tblWalk.WalkD 
     Between [Forms]![fdlgWalkers]![txtStart] 
         And [Forms]![fdlgWalkers]![txtEnd]

r937.com | rudy.ca

 

[topwaya]

Thanks very much for the quick response!

I tried that query, but it returns a comma in the qryFullName.FullName field for users that are not in the tblAddr.

Any ideas?
Thanks so much for trying to help!!

 

[r937]

if a user is not in tblAddr, then tblAddr.PID will be null after the LEFT OUTER JOIN based on tblPersonal.PID = tblAddr.PID

therefore, the join condition --

tblAddr.PID = qryFullName.PID

cannot possibly return a matching row from qryFullName

so i don't understand where the comma is coming from, unless you are using the "forgiving" concatentation --

Lastname & ', ' & Nirstname as FullName

if this is the case, you're seeing the comma because using & as the concatentation operator ignores nulls



r937.com | rudy.ca

 

[topwaya]

Thanks very much, a friend figured it out for me. Change the join properties so that all on members of tblWalk are included and only those those from tblAddr that match with tblWalk. It shows up in the query design as an arrow pointing from tblWalk to tblAddr. Also made
some other changes.

SELECT tblPersonal.PID, tblWalk.WalkD, tblPersonal.[NameL] & ", " & [NameF] 
& IIf(Len(NZ([NICK],""))>0," """ & [NICK] & """ ","") & " " & [SUFFIX] AS 
FullName, tblWalk.WalkLoc, tblPersonal.LocW, tblPersonal.Dept, 
tblPersonal.LocFC1, tblPersonal.LocFC2, tblAddr.PhoneW, tblAddr.PhoneH, 
tblPersonal.Shirt
FROM tblPersonal INNER JOIN (tblAddr RIGHT JOIN tblWalk ON tblAddr.PID = 
tblWalk.PID) ON tblPersonal.PID = tblWalk.PID
WHERE (((tblWalk.WalkD) Between [Forms]![fdlgWalkers]![txtStart] And 
[Forms]![fdlgWalkers]![txtEnd]));

Thank you for your fast replies!!!! It is really appreciated and always helps me keep learning.