Solved

MS Access query running counter

Posted on 2014-11-07
6
61 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
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

837 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