Solved

MS Access query running counter

Posted on 2014-11-07
6
67 Views
Last Modified: 2016-07-11
Hi

I need to add a running counter to a query that recites every time the value in a specific field changes.  The output would look like this

Value    Counter
Red        1
Red        2
Red        3
Blue       1
Blue       2
Yellow    1

etc...

How would I do this?

Many thanks
0
Comment
Question by:kenabbott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428637
What about if Red comes back? Do you want the next value to be 4 or 1?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428639
And do you have an autonumber ID field (as you are going to need it)?
0
 

Author Comment

by:kenabbott
ID: 40428644
The colour column will be sorted so Red won't come back.  And yes there is an autonumber field
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40428660
Assuming you have an ID column, and it is called Table1, here is the SQL code:

SELECT t.ID, t.Value, Count(u.ID) AS CountOfID
FROM Table1 AS t INNER JOIN Table1 AS u ON (t.Value = u.Value) AND (t.ID >= u.ID)
GROUP BY t.ID, t.Value;

Open in new window

0
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 250 total points
ID: 40429051
SELECT  t.Value,
  (
    Select Count(*) From Table1 tt
    Where t.ID < tt.ID and  t.Value = tt.Value
  )+1 AS Count
FROM Table1 t
ORDER BY t.Value



If you enter a new colors or exiting colors out of order, this query still works.

Mike
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question