Is CountIF possible in Access

[ahmed kamal]

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

StudentName <tbody> </tbody>	Formula <tbody> </tbody>	Result <tbody> </tbody>
Sarah <tbody> </tbody>	=COUNTIF($A$2:A2,A2) <tbody> </tbody>	1 <tbody> </tbody>
Ahmed <tbody> </tbody>	=COUNTIF($A$2:A3,A3) <tbody> </tbody>	1
John <tbody> </tbody>	=COUNTIF($A$2:A4,A4) <tbody> </tbody>	1
Sarah <tbody> </tbody>	=COUNTIF($A$2:A5,A5) <tbody> </tbody>	2
Alaa <tbody> </tbody>	=COUNTIF($A$2:A6,A6) <tbody> </tbody>	1
John <tbody> </tbody>	=COUNTIF($A$2:A7,A7) <tbody> </tbody>	2

<tbody>
</tbody>

 

[Joe4]

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!

 

[ahmed kamal]

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.

 

[welshgasman]

Try

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

 

[TechnoWay]

Try

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

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

 

[Joe4]

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?

 

[TechnoWay]

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?

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

ID	Design	Formula	Counts
1​	8551F	COUNTIF(B:B,B2)	3​
2​	8551F	COUNTIF(B:B,B3)	3​
3​	8551F	COUNTIF(B:B,B4)	3​
4​	7276F	COUNTIF(B:B,B5)	5​
5​	7276F	COUNTIF(B:B,B6)	5​
6​	7276F	COUNTIF(B:B,B7)	5​
7​	7276F	COUNTIF(B:B,B8)	5​
8​	7276F	COUNTIF(B:B,B9)	5​
9​	7277F	COUNTIF(B:B,B10)	2​
10​	7277F	COUNTIF(B:B,B11)	2​
11​	7278F	COUNTIF(B:B,B12)	2​
12​	7278F	COUNTIF(B:B,B13)	2​

 

[welshgasman]

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.

 

[Joe4]

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.

 

[TechnoWay]

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.

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 ???