By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.     nps.Agent
  3.     , nps.[NPS Score]
  4.     , (Select Count(*) 
  5.         from [3-npscountagents qry] 
  6.         Where [NPS Score] < [nps].[NPS Score])
  7.      AS Rank
  8. FROM [3-npscountagents qry] 
  9.     AS nps
  10. 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.
Feb 5 '14 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,287
First off, remove the dashes - these are reserved tokens and even when enclosd can give you heartburn:
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.
Feb 5 '14 #2

P: 3
Hi zmbd, thanks for replying.

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

Expand|Select|Wrap|Line Numbers
  1. (Select Count(*) from [3-npscountagents qry] Where [NPS Score] < [nps].[NPS Score])
Which NPS is outside?

Many thanks again for helping :)
Feb 6 '14 #3

zmbd
Expert Mod 5K+
P: 5,287
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.
Feb 6 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.