none
ACCESS 2013 - 'No current record' error RRS feed

  • Question

  • I have just run a query - it ran all night.  For this query that seems normal.  Here was the original SQL code:

    SELECT DISTINCT TOP 30 F.Player_ID AS P_ID, F.Roster_ID AS R_ID, F.Name AS Name, F.Season_ID AS Season, F.[Game_#] AS g1_Gnum, F.g5_Gnum AS g5_Gnum, F.g1_Rb AS g1_Rb, F.g2_Rb AS g2_Rb, F.g3_Rb AS g3_Rb, F.g4_Rb AS g4_Rb, F.g5_Rb AS g5_Rb, F.Tot_Rb_5g AS Tot, F.Game_ID AS Game_ID
    FROM Most_V_Reb_5g_TOT_v1_Query AS F
    WHERE (F.Tot_Rb_5g = (SELECT MAX (S.Tot_Rb_5g)
             FROM Most_V_Reb_5g_TOT_v1_Query AS S
             WHERE (S.Tot_Rb_5g = F.Tot_Rb_5g)))
    ORDER BY 12 DESC , 13;


    This ran but did not give me the results I expected (My mistake.)

    When I change/correct the code to:

    SELECT DISTINCT TOP 30 F.Player_ID AS P_ID, F.Roster_ID AS R_ID, F.Name AS Name, F.Season_ID AS Season, F.[Game_#] AS g1_Gnum, F.g5_Gnum AS g5_Gnum, F.g1_Rb AS g1_Rb, F.g2_Rb AS g2_Rb, F.g3_Rb AS g3_Rb, F.g4_Rb AS g4_Rb, F.g5_Rb AS g5_Rb, F.Tot_Rb_5g AS Tot, F.Game_ID AS Game_ID
    FROM Most_V_Reb_5g_TOT_v1_Query AS F
    WHERE (F.Tot_Rb_5g = (SELECT MAX (S.Tot_Rb_5g)
             FROM Most_V_Reb_5g_TOT_v1_Query AS S
             WHERE (S.Roster_ID = F.Roster_ID)))
    ORDER BY 12 DESC , 13;

    I put bold to 'high-light' the change.  Now I get a 'No current record' error.

    Important note: When I remove the 'F.' in the second WHERE it runs, but only gives me 1 result.

    I also tried changing the '=' sign to 'IN'; from reading about the 'same' error with ACCESS 2010.

    I have often gotten an error:  'Invalid argument for function', but like this error do not know why.

    As background I am working with basketball stats for several years/seasons.  Thru about a dozen queries I have determined the rebounds for 5 consecutive games, and found that total and have then put them in descending order.  But the problem is that instead of showing 30 'different' players, the top 9 include 1 player during an 8/9 game span with 5 results, and another player also listed multiple times for a 'similar' span.  

    The structure of my database is 7 tables - a) Game Stats, (b) Games, (c) Input Season Stats, (d) Participation, (e) Player Info, (f) Roster Info and (g) Seasons.  I do not use the Participation table.  It was an attempt to reduce redundancy, but I did not make the necessary adjustments.

    I will be happy to give more details, or code if it helps you better understand the problem.

    In advance thank for your time and interest.
    Friday, August 12, 2016 12:53 PM

Answers

  • Try this:

    SELECT DISTINCT TOP 30 F.player_id AS P_ID, 
                           F.roster_id AS R_ID, 
                           F.name      AS Name, 
                           F.season_id AS Season, 
                           F.[game_#]  AS g1_Gnum, 
                           F.g5_gnum   AS g5_Gnum, 
                           F.g1_rb     AS g1_Rb, 
                           F.g2_rb     AS g2_Rb, 
                           F.g3_rb     AS g3_Rb, 
                           F.g4_rb     AS g4_Rb, 
                           F.g5_rb     AS g5_Rb, 
                           F.tot_rb_5g AS Tot, 
                           F.game_id   AS Game_ID 
    FROM   most_v_reb_5g_tot_v1_query AS F 
           INNER JOIN (SELECT roster_id, 
                              Max(tot_rb_5g) AS max_tot_rb_5g 
                       FROM   most_v_reb_5g_tot_v1_query 
                       GROUP  BY roster_id) AS S 
                   ON S.roster_id = F.roster_id 
                      AND F.tot_rb_5g = S.max_tot_rb_5g 
    ORDER  BY 12 DESC, 
              13; 


    Michał


    • Edited by Dziubek Michał Sunday, August 14, 2016 3:23 PM
    • Marked as answer by jlwood44 Sunday, August 14, 2016 7:33 PM
    Friday, August 12, 2016 3:01 PM

All replies

  • Do you run this query in a form? I so, the "no current record" message means that the query returned no records.

    To debug this query, you should give us information about the tables (or queries) involved: Field names, keys, example data and desired result.

    Some things to try: Work in the query editor instead of the form. Take out the "Distinct" and the "Top 30" clauses, check the results and put them back in one after the other. Test the subquery in the Where clause ("Select Max ...") separately and check if it returns the correct results.

    Matthias Kläy, Kläy Computing AG

    Friday, August 12, 2016 2:56 PM
  • Try this:

    SELECT DISTINCT TOP 30 F.player_id AS P_ID, 
                           F.roster_id AS R_ID, 
                           F.name      AS Name, 
                           F.season_id AS Season, 
                           F.[game_#]  AS g1_Gnum, 
                           F.g5_gnum   AS g5_Gnum, 
                           F.g1_rb     AS g1_Rb, 
                           F.g2_rb     AS g2_Rb, 
                           F.g3_rb     AS g3_Rb, 
                           F.g4_rb     AS g4_Rb, 
                           F.g5_rb     AS g5_Rb, 
                           F.tot_rb_5g AS Tot, 
                           F.game_id   AS Game_ID 
    FROM   most_v_reb_5g_tot_v1_query AS F 
           INNER JOIN (SELECT roster_id, 
                              Max(tot_rb_5g) AS max_tot_rb_5g 
                       FROM   most_v_reb_5g_tot_v1_query 
                       GROUP  BY roster_id) AS S 
                   ON S.roster_id = F.roster_id 
                      AND F.tot_rb_5g = S.max_tot_rb_5g 
    ORDER  BY 12 DESC, 
              13; 


    Michał


    • Edited by Dziubek Michał Sunday, August 14, 2016 3:23 PM
    • Marked as answer by jlwood44 Sunday, August 14, 2016 7:33 PM
    Friday, August 12, 2016 3:01 PM
  • I am just running this as a query.  As I mentioned, I had just run this as a query - several hours.  The only difference is that I changed the second WHERE from Tot_Rb_5g to Roster_ID for both the F. and S.
    Saturday, August 13, 2016 12:36 PM
  • I tried what you showed and got an error - asking for parameter value for s.tot_rb_5g.

    Seeing that your 2nd SELECT started just Roster_ID, I took off the 'S.' after the Max

    AND I almost immediately got results, and first glance they are the desired results.

    Thank you very much.

    Saturday, August 13, 2016 2:03 PM
  • My mistake, I corrected my post

    Michał


    Sunday, August 14, 2016 3:24 PM
  • I see that there are rating points.  How can I/do I rate your response.  It was definitely right on!
    Sunday, August 14, 2016 7:35 PM