Stumped by a subquery count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gina K - 21 Jul 2007 01:46 GMT

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.

Reply to this Message

pietlinden@hotmail.com - 21 Jul 2007 03:49 GMT

> Here's the SQL:
>
[quoted text clipped - 14 lines]
> counted all the records.
> Thanks.

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

Reply to this Message

Gina K - 21 Jul 2007 13:22 GMT

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.

> > Here's the SQL:
> >
[quoted text clipped - 16 lines]
>
> Why is School not mentioned in your WHERE or HAVING clause?

Reply to this Message