Neil,
Do records exist in the hcontact table if there is no coach; that is, is this a 2-fold table holding data about available sports and its coach? If so then this should work. If it is only holding coach information then the WHERE/HAVING clause is messing you up because there is no coach record in your case. Is there another place you could query the sport information? Then you can use it in your WHERE/HAVING clause and the coach information would be drawn in on the OUTER JOIN.

As a design suggestion, could you add another table of Sports that has both students and coaches as children? This is merely a suggestion and I don't know the extent of your database so it may not be prudent.

< snipped >
>This is a database of athletes and high schools with coaches. Tables in the query are Students, HS (schools) and Hcontact (coaches). Each school may have several coaches (FB, GBK, GVB, BBK, etc.) Some schools don't have an entry for the GVB coach, but we have students entered for that sport at the school.
>
>I want my query to select only the student-athletes with sport = GVB between certain years, and to include the coach name. I need the student-athlete even if there is no corresponding GVB coach entry. There may be other sport coaches (FB, BK) which I DO NOT want.
>
< snipped >

>

>SELECT ;
>	students.student_id, ;  && (other student fields - deleted for brevity)
>	hs.hs_name, ;
>	hs.address1, ;
>	hs.address2, ;
>	hs.city as hs_city, ;
>	hs.state as hs_state, ;
>	hcontact.fname as coach_fname, ;
>	hcontact.lname as coach_lname,  ;
>	hcontact.home_phone as coach_hmphone, ;
>	hcontact.work_phone as coach_wkphone,;
>	students.pos_date, ;
>	Students.comments, ;
>	SPACE(160) AS notes ;
>	FROM  Students INNER JOIN f:\names\temp\gvbrpt;
>	on students.student_id = gvbrpt.student_id ;
>	inner join hs;
>	on gvbrpt.ceeb = hs.ceeb;
>	left join hcontact;
>	on hs.ceeb = hcontact.ceeb ;
>	WHERE YEAR BETWEEN "01" AND "04";
>        having hcontact.sport = 'GVB';  && I've tried this as part of the WHERE clause, too.
>	ORDER BY hs_state, hs_city, slname, sfname ;
>	INTO table f:\names\temp\gvb_all
>SELECT gvb_all
>REPLACE ALL notes WITH ALLTRIM(comments)

Larry Miller
MCSD
LWMiller3@verizon.net

Accumulate learning by study, understand what you learn by questioning.  -- Mingjiao