Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access query running counter

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

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

664 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