Is CountIF possible in Access

A

ahmed kamal

New Member
Joined
Aug 28, 2015
Messages
14
hello,
i have a serious problem, that by excel i can count how many the students -at this simple example - are duplicate as shown in result column by row , it helped me out in another tasks, is there a way to achieve same result in Access ?!
i hope some one help me out




[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]StudentName[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]Formula[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, width: 48"]Result[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]Sarah[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]=COUNTIF($A$2:A2,A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, width: 48"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]Ahmed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]=COUNTIF($A$2:A3,A3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]=COUNTIF($A$2:A4,A4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]Sarah[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]=COUNTIF($A$2:A5,A5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]Alaa[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]=COUNTIF($A$2:A6,A6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]=COUNTIF($A$2:A7,A7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You can do an Aggregate (Totals) Query to count how many times each value of "StudentName" appears in your table.

Following these instructions here (Count data by using a query), just
1. Start a new query
2. Add the "StudentName" field twice
3. Click on the Totals button (looks like a Sigma)
4. Under the second field, change the Totals row value of "Group By" to "Count"

And view your results. That should do it!
 
Last edited:
Upvote 0
thank you for replay, when i tried this my query gives me how many times each value of "StudentName" appears in my table, but i need this duplicate value shown as number 2 , 3 so on, like my table above.
 
Upvote 0
Try
Code: 
Result:Dcount("ID","Transactions","Description='"&Client&"' AND ID <=" & ID)

Where my Description is actually the name of the field to check and Client an alias of Description to compare, which in your case would be the studentname and ID is the ID of the record

HTH
 
Last edited:
Upvote 0
welshgasman said:
Try
Code: 
Result:Dcount("ID","Transactions","Description='"&Client&"' AND ID <=" & ID)

Where my Description is actually the name of the field to check and Client an alias of Description to compare, which in your case would be the studentname and ID is the ID of the record

HTH
Click to expand...
Hey there, i dont know if youre still around but Im also having the same issue as the OP. I've tried your method but couldnt make it to work.. I dont have all these fields you typed, I only have one field (Design code) and I'm trying to do Countif so it'll display how many times a code is repeated in the next field, without grouping the design codes. Is this possible?

Hope this message finds you :)
 
Upvote 0
COUNTIF is an Excel function, and this originally was a Microsoft Access question (they were asking how to do a COUNTIF-like formula in Microsoft Access).
So. are you trying to do this in Access or Excel?

Can you show us a small sample of your data, and what you would like your expected results to look like?
 
Upvote 0
Joe4 said:
COUNTIF is an Excel function, and this originally was a Microsoft Access question (they were asking how to do a COUNTIF-like formula in Microsoft Access).
So. are you trying to do this in Access or Excel?

Can you show us a small sample of your data, and what you would like your expected results to look like?
Click to expand...
Hello, sorry i should have clarified. I'm trying to do this on Access, I'm already quite familiar with COUNTIF on Excel, but i couldn't replicate it on Access. Basically Im trying to count how many times a code is repeated in the same column, and it should display in each row WITHOUT grouping. I'm pasting an example of how i need it to look on Access below (added formulas for clarification):


IDDesignFormulaCounts
1​
8551FCOUNTIF(B:B,B2)
3​
2​
8551FCOUNTIF(B:B,B3)
3​
3​
8551FCOUNTIF(B:B,B4)
3​
4​
7276FCOUNTIF(B:B,B5)
5​
5​
7276FCOUNTIF(B:B,B6)
5​
6​
7276FCOUNTIF(B:B,B7)
5​
7​
7276FCOUNTIF(B:B,B8)
5​
8​
7276FCOUNTIF(B:B,B9)
5​
9​
7277FCOUNTIF(B:B,B10)
2​
10​
7277FCOUNTIF(B:B,B11)
2​
11​
7278FCOUNTIF(B:B,B12)
2​
12​
7278FCOUNTIF(B:B,B13)
2​
 
Upvote 0
Look at what I posted in post #4
Yours is simpler as you only need the first criteria. You do however have to use your respective field names.
Also from your display it appears a Dcount() in a query would supply the data you require, but not the most efficient if a large dataset? A subquery would be better, but beyond my expertise. :(
 
Upvote 0
This can be done quite easily in a series of two queries.
1. First Query: Do a Totals (Aggregrate) Query, grouping on the Design field and getting your total count.
2. Second Query: Join your original table to the first query, joining on the Design field, and returning all the fields from your table, and the count from the query.

As welshgasman alluded to, you could actually do this all in one query, using a Sub Query, but that involves a bit more expertise, and is not even really necessary.
There is nothing wrong with doing two queries, and having the second dependent upon the first. Once created, you never even has to touch the first ever again. Just open the second.
 
Upvote 0
welshgasman said:
Look at what I posted in post #4
Yours is simpler as you only need the first criteria. You do however have to use your respective field names.
Also from your display it appears a Dcount() in a query would supply the data you require, but not the most efficient if a large dataset? A subquery would be better, but beyond my expertise. :(
Click to expand...
Hey thank you for the response! I definitely attempted to use your method from the 2018 post lol. But you introduced 4 different field names and i got confused where I place all those.
So if i try to apply your formula to my case:
Counts: Dcount([ID],[TableName],[Design]='"&???&"' AND [ID] <="&[ID])
Would this be correct? Also not sure what to place in ???
 
Upvote 0
You must log in or register to reply here.

Similar threads

Hoffman
  • Solved
Teacher has easy question
Replies
3
Views
67
Joe4
Joe4
L
  • Solved
Number series based on multiple criteria
Replies
3
Views
203
SanjayGMusafir
SanjayGMusafir
F
  • Solved
Sort data without changing the set of each data (using VBA)
2
Replies
12
Views
404
feni1388
F
P
  • Question Question
Count names based on enrollment
Replies
2
Views
229
paulkc
P
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top