Solved

# Countif code in MS Access

Posted on 2010-11-19
Medium Priority
753 Views
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
.      .      .
.      .      .

0
Question by:kpyrgos

LVL 14

Expert Comment

ID: 34172375
select a,b,count(*) from table
from tablename
group by a,b
0

LVL 120

Expert Comment

ID: 34172444
How to Rank Records Within a Query
http://support.microsoft.com/?kbid=208946
0

Author Comment

ID: 34172454
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

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 34172641
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

LVL 11

Expert Comment

ID: 34173529
What are you counting to get your column C?
0

Author Comment

ID: 34175342
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

0

LVL 120

Expert Comment

ID: 34175494
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

Author Comment

ID: 34175782
There you go!
ct.mdb
0

LVL 120

Expert Comment

ID: 34176015
do you have any other fields in the table other than  fields A,B?
0

Author Comment

ID: 34176434
Yes I have other fields. Is that a problem?
0

Author Comment

ID: 34186043
Any luck on this?
0

Author Comment

ID: 34188142
Solution from capricorn accepted.

Redisigned my data and used his query!
0

## Featured Post

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, â€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month14 days, 22 hours left to enroll