Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access query running counter

Posted on 2014-11-07
6
Medium Priority
?
86 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
5 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 1000 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 1000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

564 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