none
no current record error with count function RRS feed

  • Question

  • hi

    I have this filed in my query to show ranking without skipping any number if there is a duplicated values:

    (SELECT Count(*)     FROM      (SELECT DISTINCT t2.N FROM Q AS t2) AS t1     WHERE t1.N>=Q.[N]) as rank

    "N" is a calculated filed from a query named "Q"

    but when i run the query i get the message : "no current record"

    note: N field is a sum between many fields  .. when i use the same code with another field it work but when i use N field the error appearing

    can anyone tell me whats wrong in my filed ?

    thanks


    • Edited by moaaz-96 Tuesday, August 8, 2017 4:46 PM
    Tuesday, August 8, 2017 12:17 PM

Answers

  • I solved the problem .. there was a blank cells in the table so access is giving an error with the calculated fields .. i updated all the blank cells to 0 and now its working
    • Marked as answer by moaaz-96 Wednesday, August 9, 2017 9:52 AM
    Wednesday, August 9, 2017 9:52 AM

All replies

  • This is syntactically wrong. The primary query's FROM clause does not include Q.

    -Tom. Microsoft Access MVP

    Tuesday, August 8, 2017 1:31 PM
  • but Q is included to the main query this is the whole sql code:

    SELECT (SELECT Count(*)     FROM      (SELECT DISTINCT [N] FROM [Q] AS t2) AS t1     WHERE t1.N>=[Q].[N]) AS rank, students.[name], Q.N AS mark, [mark]/5100*100 AS avg
    FROM ((Dmarks INNER JOIN (Amarks INNER JOIN students ON Amarks.ID = students.[ID]) ON Dmarks.ID = Amarks.ID) INNER JOIN filter ON Amarks.ID = filter.ID) INNER JOIN Q ON Amarks.ID = Q.ID;

    Tuesday, August 8, 2017 4:12 PM
  • note: N field is a sum between many fields  .. when i use the same code with another field it work but when i use N field the error appearing
    Tuesday, August 8, 2017 4:47 PM
  • I solved the problem .. there was a blank cells in the table so access is giving an error with the calculated fields .. i updated all the blank cells to 0 and now its working
    • Marked as answer by moaaz-96 Wednesday, August 9, 2017 9:52 AM
    Wednesday, August 9, 2017 9:52 AM
  • After you did that update, did you set the field to be required? You should.

    -Tom. Microsoft Access MVP

    Thursday, August 10, 2017 2:40 AM