Countif code in MS Access

Hello experts,

I Have a table with content like this

   A    B    
  ----------
   s     100  
   s     100  
   s     225
   s     325  
   p     255  
   p     255  
   p     151  
   p     151  
   u     120  
   u     121  
   u     121  
   u     123  
   .      .    
   .      .    


I would like to add a coloumn with a countif similar function to get a result like this:

   A    B       C
  -----------------
   s     100   1
   s     100   1
   s     225   2
   s     325   3
   p     255   1
   p     255   1
   p     151   2
   p     151   2
   u     120   1
   u     121   2
   u     121   2
   u     123   3
   .      .      .
   .      .      .

Thanks in advance!
kpyrgosAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query

Select v.a,v.b,v.c
From
(
select e.A, e.B, iif([X] In (0,1),[X]+1,[X]) as c, (select count(t.b) from tableX t where  t.a=e.a and t.b<e.b) AS X
from tableX AS e
) as v
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
select a,b,count(*) from table
from tablename
group by a,b
0
 
Rey Obrero (Capricorn1)Commented:
see this link
How to Rank Records Within a Query
http://support.microsoft.com/?kbid=208946
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
kpyrgosAuthor Commented:
I'm not sure that this is what i want.

My table is sorted on culumn A.

My goal is to get the first ten appearances of the values in culumn B for each value in column A
0
 
LambertHeenanCommented:
What are you counting to get your column C?
0
 
kpyrgosAuthor Commented:
Thank you capricorn1 for the query.

This is very close to what I want.

For it to be perfect I want the counting to be continuous like this:

1
1
2
2
2
3

your query gets me a result like this:

1
1
3
3
3
6

Thank you for your help
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of your db.. in my test data the counting is

a      b      c
s      1      1
s      1      1
s      2      2
s      3      3
p      5      1
p      5      1
p      6      2
p      6      2
u      7      1
u      8      2
u      8      2
u      9      3

which is exactly what you posted in the original question.
0
 
kpyrgosAuthor Commented:
There you go!
ct.mdb
0
 
Rey Obrero (Capricorn1)Commented:
do you have any other fields in the table other than  fields A,B?
0
 
kpyrgosAuthor Commented:
Yes I have other fields. Is that a problem?
0
 
kpyrgosAuthor Commented:
Any luck on this?
0
 
kpyrgosAuthor Commented:
Solution from capricorn accepted.

Redisigned my data and used his query!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.