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.
