Problem with Order By using a calculated field

  • Thread starter Thread starter Sean Kenney
  • Start date Start date
S

Sean Kenney

I am executing a query in access using the following statement:

SELECT
p.PlayerID,
p.lastname + ',' + p.firstname + ' ' + p.middlename as [Name],
(select Sum(score) from scores s where s.playerid = p.playerid and
pr.roundid = s.roundid) as Total
FROM
players AS p,
playerRounds AS pr
WHERE
p.PlayerID = [pr].[playerid]
AND
pr.TournamentId = 1

and it seems to run ok. But when I add 'order by Total' it pops up
the enter parameter value window.

Can someone please help.
 
You can try adding the following ORDER BY clause.

ORDER BY 3

Or you can try to ORDER By the entire expression (UNTESTED)

ORDER BY (select Sum(score) from scores s where s.playerid = p.playerid and
pr.roundid = s.roundid)

OR you can save the query and use that as the basis of a second query

SELECT PlayerID, [Name], [Total]
FROM YourSavedQuery
ORDER BY [Total]
 
I tried the following:

ORDER BY 3 and
ORDER BY (select Sum(score) from scores s where s.playerid = p.playerid and
pr.roundid = s.roundid)

and neither one worked.

Is there anything other than saving the query that I can do?

BTW - I am using Access 2000.
 
Back
Top