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

0
votes

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.

2
sqldatabasems-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
I don't know how to user these command 'r = row_number() over (partition by ACell order by ACell desc)' in Access. - Sawitree Cha
Edit question to show sample data and desired output. Do you need an Access query object? - June7
@June7 I have already input the example data and output that I want from this query. - Sawitree Cha

2 Answers

1
votes

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

Sequential row numbers

1
votes

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.