ROW_NUMER OVER() Clause in Access SQL always give missing operator error

Question

Hi all, I'm frustrated as the following query in access 2007 always gives missing operator error:

SELECT  cust_id

             ,prod_name

             ,txn_dt

             ,ROW_NUMBER OVER (ORDER BY txn_dt) AS row_num

FROM    base_table_2

WHERE   cust_id = '20150113000070'

;

I've been a frequent user of Teredata SQL never encountered a problem like this. I've been searching through the internet but no solution found. I think the syntax might by different here in Access but does anyone know how?

Thank you very much!

Answer 1

I don't believe that syntax is available in Access queries. You may need to use a subquery.

For numbering the orders over customers in the Northwind Orders table:

SELECT Orders.CustomerID, Orders.OrderID, Orders.OrderDate,

(SELECT Count(*)

FROM ORDERS O

WHERE O.CustomerID = Orders.CustomerID and O.OrderDate <=Orders.OrderDate) AS Row_Num

FROM Orders

ORDER BY Orders.CustomerID, Orders.OrderDate;

Answer 2

Thank You Duane! That helped. I guess this is the only way after all!