Solved

MS Access query running counter

Posted on 2014-11-07
6
59 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 33

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now