Stumped by a subquery count

[Guest]

Here's the SQL:

SELECT tblEventResults.fkAthleteID, tblEventResults.EventResultsID,
tblAthletes.School, tblEventResults.FinalPlace, tblMeetEvents.MeetEventID,
(SELECT Count(*) FROM tblEventResults AS T WHERE T.FinalPlace <
tblEventResults.FinalPlace AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)+1 AS DualPlace
FROM tblAthletes INNER JOIN (tblMeetEvents INNER JOIN tblEventResults ON
tblMeetEvents.MeetEventID = tblEventResults.fkMeetEventID) ON
tblAthletes.AthleteID = tblEventResults.fkAthleteID
WHERE (((tblMeetEvents.MeetEventID)=271))
ORDER BY tblEventResults.FinalPlace;

How can I modify this query to have results returned for any 2 schools I
choose, and assign the “DualPlace†based on only the schools I specified? I
tried using parameters of School1 or School2 but the “DualPlace†still
counted all the records.
Thanks.

 

[pietlinden]

Here's the SQL:

SELECT tblEventResults.fkAthleteID, tblEventResults.EventResultsID,
tblAthletes.School, tblEventResults.FinalPlace, tblMeetEvents.MeetEventID,
(SELECT Count(*) FROM tblEventResults AS T WHERE T.FinalPlace <
tblEventResults.FinalPlace AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)+1 AS DualPlace
FROM tblAthletes INNER JOIN (tblMeetEvents INNER JOIN tblEventResults ON
tblMeetEvents.MeetEventID = tblEventResults.fkMeetEventID) ON
tblAthletes.AthleteID = tblEventResults.fkAthleteID
WHERE (((tblMeetEvents.MeetEventID)=271))
ORDER BY tblEventResults.FinalPlace;

How can I modify this query to have results returned for any 2 schools I
choose, and assign the "DualPlace" based on only the schools I specified? I
tried using parameters of School1 or School2 but the "DualPlace" still
counted all the records.
Thanks.

Why is School not mentioned in your WHERE or HAVING clause?

 

[Guest]

I wasn't quite sure where to put School...I set up parameters for School1 and
School2, and put School1=tlbAthletes.School or School2=tbl.Athletes.School in
the WHERE clause, but I just couldn't get the right combination.