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
MooseshanksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
SELECT Account, Date_To, Row_Number() OVER (PARTITION BY Account ORDER BY Account, Date_To) as Counter, FROM your table.

Kelvin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MooseshanksAuthor Commented:
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!
Kelvin SparksCommented:
Remove the comma after the word Counter

Kelvin
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

image001.png
Kelvin SparksCommented:
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
MooseshanksAuthor Commented:
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!
MooseshanksAuthor Commented:
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.
Kelvin SparksCommented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
If you want this for a query, Kelvin's method will work fine, but if you want to do this for a report, you can ignore the sequential numbering in the query and include it in the report itself.

To do this, you create an unbound textbox, give it a ControlSource: =1

Then you set the RunningSum property of that control to "Over Group"
PatHartmanCommented:
To further Dale's suggestion, this is a sequential process and reports are sequential processes so creating the sequence number in a report is trivial and not processing intensive.  However, queries are set processes and getting them to do anything sequential is either impossible or costly in resources.  If you examine the query, in order to do the counting necessary to generate the sequence number, each record ABSOLUTILY MUST have an ascending value unique identifier and the query must be sorted on it.  Without one, no sequence number is possible.  Plus, each row in the recordset will run a separate query to count the rows before it in order to generate its ordinal position.

In a query, using date as in the example, you won't get the sequence number you envision unless there is only a single record per day.  But, in a report, you will get what you want although you won't be able to specifically control the order of records with the same date unless you can sort by some other field.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.