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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Remove the comma after the word Counter
Kelvin
Kelvin
ASKER
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
Code as it is will not run as a native Access query.
Kelvin
ASKER
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!
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!