Solved

Countif code in MS Access

Posted on 2010-11-19
12
745 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:leoahmad
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 120

Accepted Solution

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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ā€¦

770 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