SQL depths of a SQL virgin 1

[louize]

i've setting up a simple databases that records crew member that take part in sailing. the main form has a key field-"Sailno", whereas the sub table which contains the various crew taking part has a key feild of "SailingID"

I'm tring to run an SQL query that find who have sailed within a set time but the results brings repeated answers if the one person has worked afew days

heres my pathetic attempt X-)

SELECT CREWtbl.CForname
From CREW_SAILtbl, CREWtbl
WHERE CREWtbl.CrewNo=CREW_SAILtbl.CrewNo
AND CREW_SAILtbl.SailNo IN

(SELECT CrewNo
FROM CREW_SAILtbl
GROUP BY CrewNo
HAVING COUNT(*)<4);

 

[petermeachem]

Without looking too closely, if you change SELECT CREWtbl.CForname to SELECT DISTINCT CREWtbl.CForname, then you ought to get the names only once.

Always pleased to help a virgin out. Peter Meachem
peter@accuflight.com

 

[louize]

peter darling ur a star!!! it worked a treat and i'm forever grateful u source of knowledge u!! ;-)

 

[petermeachem]

If you are going to be as nice as that, you ask me any thing you like. Happy to help. Peter Meachem
peter@accuflight.com