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

    invalid argument to function

    I am working with ACCESS 2013 and working with basketball stats. I have created a query that looks at points scored for a 3 game span and increased this to a 5 game span. When I run the 3 game query - to put totals in descending order, and have only 1 entry per player per season the query runs - for about 4+ hours and gives appropriate results.
    When I run the 'same' SQL for the 5 game span I get an error - Invalid argument for function. When I remove the 'F.' from the second WHERE statement then the query runs fine/fairly quickly (about a minute), but it only shows the VERY top result - not the TOP 30 as requested. Same results when I adjust the 3 game SQL - only top result with query finished in about a minute.
    Why the INVALID ARGUMENT TO FUNCTION error, and why only with the 5 game SQL?
    The reason for the distinct is that I do NOT want 'similar' results showing a second time. As example Judy Burns had games of 32 and 33 and the game both before and after were both 15 points. I understand at my level (of skill, etc.) both will show. The distinct would remove one of sequences if she went 15, 32, 33 and 10. The 80 total would show, but the last 3 sequence (total of 75) would not show. So the results show more players and more years in the TOP 30. When I ran the sorted list for the 5 games withOUT distinct, the top 12 totals were all for the same 3 players in the same year/sequence they totaled 142 for 3 games.

    3 game SQL:


    SELECT DISTINCT TOP 30 F.Pl_ID AS Pl_ID, F.Ros_ID AS Ros_ID, F.Name AS Name, F.Season_Play AS Season, F.g1_Gnum AS ['g1_G#'], F.g3_Gnum AS ['g3_G#'], F.g1_pts AS g1_Pts, F.g2_Pts AS g2_Pts, F.g3_Pts AS g3_Pts, F.Tot_Pt_3G AS Tot_Pt_3g, F.Game_ID AS First_Game
    FROM Most_pts_3g_TOT_v31_Query AS F
    WHERE (((F.Tot_Pt_3g) = (SELECT MAX (S.Tot_Pt_3g)
    FROM Most_pts_3g_TOT_v31_Query AS S
    WHERE S.Ros_ID = F.Ros_ID)))
    ORDER BY Tot_Pt_3G DESC;


    5 game SQL:

    SELECT DISTINCT TOP 30 F.Player_ID AS Pl_ID,
    F.Roster_ID AS R_ID,
    F.Season_Play AS Season, F.g1_Gnum AS g1_Gnum, F.g5_Gnum AS g5_Gnum, F.Full_Name AS Name,
    F.g1_Pts AS g1_Pts,
    F.g2_Pts AS g2_Pts,
    F.g3_Pts AS g3_Pts,
    F.g4_Pts AS g4_Pts,
    F.g5_Pts AS g5_Pts,
    F.Tot_Pts AS Tot_Pts
    FROM Most_pts_5g_TOT_v1_Query AS F
    WHERE (((F.Tot_Pts) = (SELECT MAX (S.Tot_Pts)
    FROM Most_pts_5g_TOT_v1_Query AS S
    WHERE Roster_ID = S.Roster_ID)))
    ORDER BY Tot_Pts DESC;


    I have not re-run 3 game query, but it took 4+ hours last time. When I originally ran this it was TOP 35 and actually showed 40 records. I imagine 'ties'. I know Judy Burns was listed twice in the same year because both totaled 80.


    When I ran 5 game total, it gave me the results in about a minute, but it is only showing 3 records - all with a total of 142.
    When I put the 'F' in the second WHERE statement for the 5 games, I get an 'Invalid argument to function' error.

    Any help is greatly appreciated.


  2. #2
    New Lounger
    Join Date
    Jun 2011
    Posts
    21
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Can you post the actual query string that generates the error? As I am not an Access user but a SQL Server user I'm not clear on what "Most_pts_3g_TOT_v31_Query" is. Is it a table or a view? Is it a query you save?

    At first blush it looks like the query performance could be improved. Can you describe more what you're trying to accomplish?
    Last edited by LHuisingh; 2016-08-18 at 11:45.

  3. #3
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    "Most_pts_3g_TOT_v31_Query" is a query that is saved.
    I make a query and then use that to go to the next level - several steps.
    I have 6 tables, (a) Games, (b) Player_Game_Stats, (c) Players, (d) Roster_Info, (e) Seasons and (f) Season_Stats.
    I start with a query to have a name and points from a first game. Then I have to (2) queries to find the next game and Player(Roster)_Id to find the points for the next game. After doing this repeatedly, I have the points for 3 (or 5) consecutive games. I then run another query to find the/each total, and sort by total.
    And then I want to eliminate where the same player has several entries listed from the same set of games. For example when I did rebounds C.W. had 5 of the top 9 results from a 9 game span.
    What I was doing was using the year (Roster_ID) of the first game, and allowing only 1 entry(best) from each season. I was using WHERE, but somebody suggested using INNER JOIN and I get results in about a minute - vs several hours or an error that I have no idea why I am getting. A person that I greatly respect has said that INNER JOIN can lose some results.
    Also, if a person has the same result/total in the same year then both results show. This would typically happen if for a sequence, the first game result and 6th game results are the same.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Berridale, NSW, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    complex queries by Peter-Rabbit

    I find that complex queries in Access often fail. To keep track of the bits that do work, I use a simpler query to make a table ... then query the new table with the next bit to make yet another table ... and so on.

    This way I can interrogate each table to test the effectiveness of the process/code.

    Once I get it to work, I try to assimilate the various steps.

    Some of my commercial products still use this approach, but driven by a form which asks the customer to click on a number of steps.

  5. #5
    New Lounger
    Join Date
    Jun 2011
    Posts
    21
    Thanks
    0
    Thanked 2 Times in 2 Posts
    I tend to think that the process can be optimized but without a clear list of your various table structures it's hard to tell. Likewise, without the actual query string that has an error it's impossible to help with that issue. If you can provide those things then I think more people might be able to help.

    An INNER JOIN is only similar to a WHERE clause. An INNER JOIN has a join condition that assumes that there are records in two tables that are related to each other. For example, say you have a Game_Stats table (Game_ID, Player_ID, Game_Points) and a Player table (Player_ID, Player_Name). If you

    SELECT Game_ID, Game_Stats.Player_ID, Player_Name, Game_Points
    FROM Game_Stats
    INNER JOIN Player on Game_Stats.Player_ID=Player.Player_ID


    and if Player_ID = 27 had points in a game but there was no Player_ID=27 in the Player table then the results will not include the points player #27 scored in any game because any data that is returned has to meet all applicable join conditions.

    I'm willing to help but only if you can provide more details on the structure of your tables.

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Berridale, NSW, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Good points made by LHuisingh

    I agree that you need to provide more detail for us to help.

  7. #7
    New Lounger
    Join Date
    Dec 2015
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Game_Stats Table:
    GS_ID Key AutoNumber, Game_ID ShortText, Roster_ID Number, Game_Played Yes/No, Start Yes/No, Quarters Number, Minutes Number, Seconds Number, Baskets_Made Number, Baskets_Attempted Number, Field_Goals_Made Number, Field_Goals_Attempted Number, 3pt_Baskets Number, 3pt_Attempts Number, Free_Throws_Made Number, Free_Throws_Attempted Number, Points Number, Cal_Pts_1 Calculated, Cal_Pts_2 Calculated, Offensive_Rebounds Number, Defensive_Rebounds Number, Unknown_Rebounds Number, Assists Number, etc.

    Games Table:
    Game_ID Key ShortText, Season_ID ShortText, Skill_Level ShortText, Game_# Number, Game_Date Date/Time, Home_Away ShortText ComboBox, Opponent ShortText, Marion_Score Number, Opponent_Score Number, Overtimes Number, Win/Loss Yes/No, etc.

    Players Table:
    Player_ID Key ShortText, FirstName ShortText, LastName ShortText, GradYear Number, etc.

    Roster_Info:
    Roster_ID Key AutoNumber, Player_ID ShortText, Season_Play ShortText, Part_Level ShortText ComboBox, Part_Level_2 ShortText ComboBox, Var_Unif_# ShortText, Ht_ft Number, Ht_In Number, Cal_Ht_J_in Calculated, Cal_Ht Calculated, Position ShortText ComboBox, etc.

    Season Table:
    Season_ID Key ShortText, Fall_Year Number, HC_Last_Name ShortText, HC_First_Name ShortText, VA_Last_Name ShortText, etc.

    I hope this helps some. I will provide other info as requested.

  8. #8
    New Lounger
    Join Date
    Jun 2011
    Posts
    21
    Thanks
    0
    Thanked 2 Times in 2 Posts
    I hope this helps some. I will provide other info as requested.
    Please post the exact query string that generates the "invalid argument" error.

Posting Permissions

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