Solved

Countif code in MS Access

Posted on 2010-11-19
12
744 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now