0

I have this piece of code that I am trying to run on Access DB and getting a syntax error. I suppose Row number and Partition By does not work in Access. I was hoping I can get help in writing this in an alternate way so I can run in Access.

SELECT *
,ROW_NUMBER() OVER(PARTITION BY s.ConstraintId, s.unitid ORDER BY s.SF desc) as RN2
from genoutages g
inner join shiftfactor_report s on s.itemid=g.unitid
Shyama Sonti
  • 321
  • 1
  • 5
  • 16
  • Access doesn't support both of these, indeed. You might be able to create something that produces a similar result, but please provide table structure, desired input (about 5 rows) and desired output for me to produce it. – Erik A Sep 07 '17 at 15:16
  • Eric - dumb question. What is the best way for me to do that? – Shyama Sonti Sep 07 '17 at 15:20
  • You can create sample tables in text on [this site](https://ozh.github.io/ascii-tables/) and copy-paste them. As for the queries input, just copy-paste a section of both relevant tables, and run the current query in your previous system (Oracle probably) against that – Erik A Sep 07 '17 at 15:24
  • I am struggling with getting you the data. Is there anyway you could rewrite the best you can to fit Access and I can take it from there? I read some other posts where they suggest doing some sort of self join and get count etc. I could not figure that. But if you could rewrite and get me a start I think I would be able to take it from there. – Shyama Sonti Sep 07 '17 at 15:33
  • All examples I read for PARTITION BY show only one parameter - however, is moot since Access doesn't recognize. Access does have a Partition() function but it is different. If you want to generate a number sequence within each unitid group when the ConstraintId changes (or vice versa), consider using DCount(). Review https://stackoverflow.com/questions/45898593/transform-multiple-rows-into-columns-with-unique-key – June7 Sep 10 '17 at 01:59

0 Answers0