Hello,



I have a slight mental block and cannot see how to make use of the nZ function in the following query. In the "Alternate" I do not always have a data entry. I assume that this lack of value is what is contributing to only those records that have Alternate pupolated being returned and all others not being returned. All data is text like "John Doe". If there isn't an entry in one of the columns then then default entry of 0 is present. I do know that if I leave off Alternate all the relevant team records are returned so it is for sure something that I have done in specifying it. I also know that if there is a 0 in any of the other columns then those teams are not returned. I wasn't concerned with this at the time because I was only intersted in teams with a minimum of 4 shooters. So, I guess the better question is why is the 0 value causing me this problem?

Is the use of the nZ function applicable in this scenario. If not can someone please clue me in on what is wrong.

Code:
 
SELECT T_Teams.Team_Name AS Team, T_Shooters.Name AS Captain, 
T_Shooters_1.Name AS Shooter2, 
T_Shooters_2.Name AS Shooter3, 
T_Shooters_3.Name AS Shooter4, 
T_Shooters_4.Name AS Alternate
 
FROM ((((T_Shooters INNER JOIN 
T_Teams ON T_Shooters.ID=T_Teams.Captian_ID) INNER JOIN 
T_Shooters AS T_Shooters_1 ON T_Teams.Member_2_ID=T_Shooters_1.ID) INNER JOIN 
T_Shooters AS T_Shooters_2 ON T_Teams.Member_3_ID=T_Shooters_2.ID) INNER JOIN 
T_Shooters AS T_Shooters_3 ON T_Teams.Member_4_ID=T_Shooters_3.ID) INNER JOIN 
T_Shooters AS T_Shooters_4 ON T_Teams.Alternate_ID=T_Shooters_4.ID;