?
Solved

Countif code in MS Access

Posted on 2010-11-19
12
Medium Priority
?
753 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:kpyrgos
12 Comments
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 34172375
select a,b,count(*) from table
from tablename
group by a,b
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34172444
see this link
How to Rank Records Within a Query
http://support.microsoft.com/?kbid=208946
0
 

Author Comment

by:kpyrgos
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 120

Accepted Solution

by:
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

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

Author Comment

by:kpyrgos
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

Thank you for your help
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

by:kpyrgos
ID: 34175782
There you go!
ct.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34176015
do you have any other fields in the table other than  fields A,B?
0
 

Author Comment

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

Author Comment

by:kpyrgos
ID: 34186043
Any luck on this?
0
 

Author Comment

by:kpyrgos
ID: 34188142
Solution from capricorn accepted.

Redisigned my data and used his query!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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…

839 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