Error from Access when trying to add 'ranking' field using the following code:

[adamnorsworthy]

I have a query called 'Query1' and it uses the following SQL:

SELECT DISTINCT 
    nps.Agent
    , nps.[NPS Score]
    , (Select Count(*) 
        from [3-npscountagents qry] 
        Where [NPS Score] < [nps].[NPS Score])
     AS Rank
FROM [3-npscountagents qry] 
    AS nps
ORDER BY nps.[NPS Score] DESC;

I am attempting to 'rank' the records based on the NPS score. When I run it I get the following error:

"The Microsoft Jet Database engine does not recognise " as a valid field name or expression"

I have double checked the SQL and it seems okay. Where am I going wrong?

Many thanks.

[zmbd]

First off, remove the dashes - these are reserved tokens and even when enclosd can give you heartburn:

• Access 2007 reserved words and symbols
• AllenBrowne- Problem names and reserved words in Access

In the inner SQL as Rank you have
Where [NPS Score] < [nps].[NPS Score]) however the NPS is outside of the "()" thus out of scope.

I think that's it; however, I am on my way out the door so may have missed something.

[adamnorsworthy]

Hi zmbd, thanks for replying.

You mentioned that 'the NPS is outside of the "()"...but I can't see it. Here is the "()" part:

(Select Count(*) from [3-npscountagents qry] Where [NPS Score] < [nps].[NPS Score])

Which NPS is outside?

Many thanks again for helping :)

[zmbd]

You mentioned that 'the NPS is outside of the "()"...but I can't see it. Here is the "()" part:

I reformatted the SQL in your original post:
You attempt to use "NPS" within the SUB-Query starting on Line 4.
However, Line 4 also starts the "(" encasement of the SQL which ends on Line 6 with ")"
Nowhere within this group do you define "NPS"

Code Block Line 9: You define "NPS".
Line 9 is outside of the line 4/6 "()" grouping block; thus, out of scope for the SQL script starting on line 4 and ending on line 6.