Link to home
Start Free TrialLog in
Avatar of Mooseshanks
Mooseshanks

asked on

How to identify changes in data between consecutive rows (sorted in descending order by acct #)?

I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.

Please help! Thank you!

Ex. The "counter" is what I am trying to create

Account        Date_To              Counter
12345            06/30/2015           1
12345            05/31/2015           2
12345            04/30/2015           3
19999            06/30/2015           1
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mooseshanks
Mooseshanks

ASKER

Kelvin I really appreciate your help, but I am still getting an operator/syntax error message. This is my script from what you suggested:
SELECT Account_Number, Date_To, Row_Number() OVER (Partition BY Account_Number ORDER BY Account_Number, Date_To) as Counter,
FROM dbo_Fee_Component;

Thank you again for your advice!
Remove the comma after the word Counter

Kelvin
I must be missing something... still getting the error. I really appreciate your help and patience!!

User generated image
Ah! MS Access, not SQL Server. I thought I'd seen sql server in this. The dbo indicated sql server. If running from Access, either run as a pass through query - or use ADODB to execute it on the server.

Code as it is will not run as a native Access query.

Kelvin
Apparently my company does not allow us to pass through to the server. I get an error stating 'invalid object name'. I know the table name is correct though and I am connecting to the correct source when asked. Any suggestions on how to accomplish this with Access based on my limitations? Thanks again for your help!
Kelvin - please disregard my previous message. I just figured out what I was doing wrong. I'm all set now. Thanks so much for your assistance.
SELECT Account, Date_To, (SELECT Count(*)
      FROM dbo_Fee_Component As X
      WHERE X.Account = dbo_Fee_ComponentAs .Account
          And X.Date_To<= dbo_Fee_ComponentAs .Date_To) As SeqNo
FROM  dbo_Fee_Component

This MIGHT help
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial