Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query is fine, but when sorted then requests parameters

    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.

  2. #2
    jwoods
    Guest
    Post your code.

  3. #3
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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);

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    There is no Order By clause here.
    Rui
    -------
    R4

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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).
    Wendell

  6. #6
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?resi...int=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;

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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;
    Rui
    -------
    R4

  8. #8
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  9. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by jlwood44 View Post
    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.
    Attached Files Attached Files
    Rui
    -------
    R4

  10. The Following User Says Thank You to ruirib For This Useful Post:

    jlwood44 (2016-01-16)

  11. #10
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  12. #11
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I am glad I could help .
    Rui
    -------
    R4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •