?
Solved

Countif code in MS Access

Posted on 2010-11-19
12
Medium Priority
?
749 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

771 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