Query is fine, but when sorted then requests parameters

Topic

New Reply

I have a query that runs fine. BUT when I try to have the last field sort in descending order, then when I try running the query, then it asks me for values of a parameter.

Reply | Quote

Replies

SELECT Cons_FT_v4_Query.Player_ID, Cons_FT_v4_Query.Roster_ID, Cons_FT_v4_Query.Game_ID, Cons_FT_v4_Query.Skill_Level, Cons_FT_v4_Query.Full_Name, Cons_FT_v4_Query.g1_FTM, Cons_FT_v4_Query.Free_Throws_Attempted, Cons_FT_v4_Query.FT_Missed, Cons_FT_v4_Query.[Game_#], Cons_FT_v4_Query.[MaxOfGame_#], Cons_FT_v4_Query.Additional_Games, Cons_FT_v4_Query.Fall_Year, Cons_FT_v4_Query.Next_Season, Cons_FT_v4_Query.g2_Season, Cons_FT_v4_Query.Next_Game, [Roster_Info]![Roster_ID] AS g2_R_ID, [Game_Stats]![Free_Throws_Made] AS g2_FTM, [Game_Stats]![Free_Throws_Attempted] AS g2_FTA, Val(Nz([g2_FTA],”0″))-[g2_FTM] AS g2_Ms, IIf([g2_Ms]=0,[g2_FTM]+[g1_FTM],[g1_FTM]) AS g2_C_FT
FROM (Cons_FT_v4_Query INNER JOIN Roster_Info ON (Cons_FT_v4_Query.g2_Season = Roster_Info.Season_Play) AND (Cons_FT_v4_Query.Player_ID = Roster_Info.Player_ID)) INNER JOIN Game_Stats ON (Roster_Info.Roster_ID = Game_Stats.Roster_ID) AND (Cons_FT_v4_Query.Next_Game = Game_Stats.Game_ID);

Reply | Quote

There is no Order By clause here.

Reply | Quote

How are you trying to sort it by the last field? Are you putting something in the property of the query, or are you putting something in the query grid? I would check to make sure there isn’t something in the “Order By” property, and put the sort in the query grid (or in the SQL statement).

Reply | Quote

This is with the OrderBy. The parameter is Ms_g2. I seems that any positive number will make the parameter happy, AND does not seem to affect the query. The sort is definitely not what I expected.
In advance, thank you for your help and expertise.
Here is the OneDrive link: https://onedrive.live.com/redir?resid=B220B890CEE8154E!501&authkey=!ABXydRrSRtwgtFs&ithint=file%2czip
It includes a screen clipping of the query that is ‘sorted’. The query in question is: Cons_FT_v41_Query
Here is the SQL with the sort:
SELECT Cons_FT_v4_Query.Player_ID, Cons_FT_v4_Query.Roster_ID, Cons_FT_v4_Query.Game_ID, Cons_FT_v4_Query.Skill_Level, Cons_FT_v4_Query.Full_Name, Cons_FT_v4_Query.g1_FTM, Cons_FT_v4_Query.Free_Throws_Attempted, Cons_FT_v4_Query.FT_Missed, Cons_FT_v4_Query.[Game_#], Cons_FT_v4_Query.[MaxOfGame_#], Cons_FT_v4_Query.Additional_Games, Cons_FT_v4_Query.Fall_Year, Cons_FT_v4_Query.Next_Season, Cons_FT_v4_Query.g2_Season, Cons_FT_v4_Query.Next_Game, [Roster_Info]![Roster_ID] AS g2_R_ID, [Game_Stats]![Free_Throws_Made] AS g2_FTM, [Game_Stats]![Free_Throws_Attempted] AS g2_FTA, Val(Nz([g2_FTA],”0″))-[g2_FTM] AS g2_Ms, IIf([g2_Ms]=0,[g2_FTM]+[g1_FTM],[g1_FTM]) AS g2_C_FT
FROM (Cons_FT_v4_Query INNER JOIN Roster_Info ON (Cons_FT_v4_Query.g2_Season = Roster_Info.Season_Play) AND (Cons_FT_v4_Query.Player_ID = Roster_Info.Player_ID)) INNER JOIN Game_Stats ON (Cons_FT_v4_Query.Next_Game = Game_Stats.Game_ID) AND (Roster_Info.Roster_ID = Game_Stats.Roster_ID)
ORDER BY IIf([g2_Ms]=0,[g2_FTM]+[g1_FTM],[g1_FTM]) DESC;

Reply | Quote

The problem is that you ordering by aliased column names, which Access doesn’t like.

The issue has an easy solution, though: Use the original query as a subquery, which means that all the aliases used will be actual column names, so the problem will go away.

Here is the SQL:

Code:

SELECT Cons_FT_v41_Query.*, IIf([g2_Ms]=0,[g2_FTM]+[g1_FTM],[g1_FTM]) AS Expr1
FROM Cons_FT_v41_Query
ORDER BY IIf([g2_Ms]=0,[g2_FTM]+[g1_FTM],[g1_FTM]) DESC;

Reply | Quote

I tried using your code, and ran into a couple of problems. (1) both [g1_FTM] and [g2_FTM] are listed in the query as FTM.
(2) now I am getting parameters for both [g2_Ms] and [g2_FTM], so my/our iif statement is not running row by row, and everybody’s first game of free throws made is having the parameter value added to it.

Reply | Quote

I tried using your code, and ran into a couple of problems. (1) both [g1_FTM] and [g2_FTM] are listed in the query as FTM.
(2) now I am getting parameters for both [g2_Ms] and [g2_FTM], so my/our iif statement is not running row by row, and everybody’s first game of free throws made is having the parameter value added to it.

You need to remove the column added to Order by in Cons_FT_v41_Query. Just delete the whole column and save the query. Once you do that, the query based on the code I posted before will run without issues.

I have attached a copy of the database with a new Query, named FixedQuery, that solves the problem. Of course, Cons_FT_v41_Query has been changed as I described.

Reply | Quote

Thank you very much for the help.
2 adjustments that I made: (a) instead of Expr1, I used my name ‘cons_FT_2g’ and (b) ORDER by ‘cons_FT_2g’.
Again thank you very much for your help, effort and expertise, AND taking the time to write the solution that I could easily use and understand.

Reply | Quote

I am glad I could help :).

Reply | Quote