Getting syntax error (missing operator) in query expression in MS Access

Question

I get an error message

Syntax error (missing operator) in query expression 'r = row_number() over (partition by ACell order by ACell desc)'

in Microsoft Access; I don't know why, but it works in Microsoft SQL Server.

My query:

select * 
from 
    (select *, r = row_number() over (partition by ACell order by ACell 
    desc) 
    from cellTB) a 
where 
    r <= 5;

Please could anyone help me?

Example Data: cellTB

=================
ACell  |   RNC
=================
1      |   1
-----------------
1      |   2
-----------------
1      |   3
-----------------
1      |   4 
-----------------
1      |   5
-----------------
1      |   6
-----------------
2      |   1
-----------------
2      |   2
-----------------
2      |   3
-----------------
2      |   4
-----------------
2      |   5
-----------------
2      |   6

I want to select just top 5 from duplicate ACell in MC Access.

Is this a pass-through query sent to SQL Server? If not, the syntax isn't correct, as Access doesn't support window functions — Thomas G
– Thomas G, Commented Feb 25, 2019 at 6:38
I don't know how to user these command 'r = row_number() over (partition by ACell order by ACell desc)' in Access. — Sawitree Cha
– Sawitree Cha, Commented Feb 25, 2019 at 7:03
Edit question to show sample data and desired output. Do you need an Access query object? — June7
– June7, Commented Feb 25, 2019 at 7:37
@June7 I have already input the example data and output that I want from this query. — Sawitree Cha
– Sawitree Cha, Commented Feb 26, 2019 at 4:33

Gustav · Accepted Answer · 2019-02-25 08:04:11Z

1

You code is T-SQL. For Access SQL you can, for example, use my RowNumber function as previously posted several times:

Sequential row numbers

answered Feb 25, 2019 at 8:04

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-02-25 12:42:31Z

1

MS Access doesn't support row_number() (just one of many reasons to switch to another database). One method to do what you want uses a correlated subquery:

select c.* 
from cellTB as c
where (select count(*)
       from cellTb as c2
       where c2.Acell = c.Acell and
             c2.id <= c.id  -- this is the primary key column
      ) <= 5;

This assumes that your table has a primary key, which is called id in this example.

answered Feb 25, 2019 at 12:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Getting syntax error (missing operator) in query expression in MS Access

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related