Query expression asking for an ID

[JimO]

I am trying to add an expression in a query to determine the number of games teams are behind the leader.



I have used the expression in a form with no problems but when I try to use it in a query I get prompted of the 'ID' and I am not sure how to write this.

In a form =Max([W])-[W] returns a value for the number of games t team is behind from 0 for the leader on down the list, but I can not get it to work in a query.

Behind: Max([SportsMLBALCPlayoffQry].[W])-[W]

.

Where do I add the 'ID'?

Jim O

[CJ_London]

before trying to answer your question, please provide the full sql to your query (in your query, go to the sql window and copy and paste the code). At the moment you have asked the equivalent of 'My car won't start, it's painted red, why?'

[JimO]

Thanks for your response,

I can see the number of games a team is behind in the form but I would like to have that data in the query so I can access it from a list box generated from this query.

SELECT SportsMLBLeagueQry.ID, SportsMLBLeagueQry.Div, SportsMLBLeagueQry.Team, SportsMLBLeagueQry.W, SportsMLBLeagueQry.L, SportsMLBLeagueQry.League, SportsMLBLeagueQry.WinLoss, [W]+[L] AS GamesPlayed, SportsMLBLeagueQry.Updated, SportsMLBLeagueQry.NickName, DCount("*","SportsMLBALCPlayoffQry","WinLoss>" & [WinLoss])+1 AS Rank
FROM SportsMLBLeagueQry
WHERE (((SportsMLBLeagueQry.Div)="AL Central") AND ((SportsMLBLeagueQry.League)="AL"))
ORDER BY SportsMLBLeagueQry.WinLoss DESC;

Jim O

[CJ_London]

thanks - you'll either need to make the query a groupby query - click on the epsilon icon (totals) in the ribbon or use DMax in much the same way as you have used dcount. Something like

Behind: DMax("[W]","[SportsMLBALCPlayoffQry]")-[W]

not sure if you need a criteria to limit to the value in which case

Behind: DMax("[W]","[SportsMLBALCPlayoffQry]","SportsMLBLeagueQry.Div='AL Central' AND SportsMLBLeagueQry.League='AL'")-[W]

[JimO]

Ajax,

Thank you very much for your help. The first suggestion works great. I appreciate the time and patience with me.

Jim O