Is CountIF possible in Access

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:
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.
Oh I def thought about this too, but was wondering if i could handle it with a simple Dcount first. I guess I'll try this route.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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 ???
No, the original reply was for a counter of each one (first instance would be 1, second would be 2, third would be 3, etc).
You want a Total count, not a counter. As he said, you only need the first criteria. So you can drop the second one, i.e.
Rich (BB code):
Counts: Dcount([ID],[TableName],[Design]='"&???&"')
 
Upvote 0
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 ???
When you do not know the syntax of a function, Google for it and go to the source.


So read that link and see if you can work it out. I very much doubt you have a table called TableName? You do not need the <= ID, as you are not doing a running count.
 
Upvote 0
When you do not know the syntax of a function, Google for it and go to the source.


So read that link and see if you can work it out. I very much doubt you have a table called TableName? You do not need the <= ID, as you are not doing a running count.
Honestly I'm confused by the syntax lol, I keep getting errors no matter how I type. And yes TableName is just a random name for the purpose of this example, i wanted to keep it simple. Here's the combinations i've tried so far:

Dcount([ID],[TableName],[Design]=' "&[Design]&" ')
Dcount("[ID]","[TableName]","[Design]=' "&[Design]&" ')
Dcount("ID","TableName","Design=' "&Design&" ')

None of them worked...
 
Upvote 0
Maybe try:
VBA Code:
Dcount("[ID]","TableName","[Design]='" & Design & "'")
or
VBA Code:
Dcount("[ID]","TableName","[Design]='" & [Design] & "'")
(don't try typing this, use Copy and Paste, or you might get the single/double quotes wrong).

Also, your table name does not have any spaces in it, does it?

Quite frankly, I rarely use DCOUNT (I think there are other issues with it, why it is typically not recommended, but I cannot remember the details around it).
I always using the query method I recommended.
 
Upvote 0
So go with the two query method offered by Joe4 ?
More efficient and easier to create, if you are having such problems with a DCount()

Never a great idea to give dummy data :( Give the real data names and what type they are, text, numeric, date etc, as only numeric does not need any delimiters.
 
Upvote 0
Maybe try:
VBA Code:
Dcount("[ID]","TableName","[Design]='" & Design & "'")
or
VBA Code:
Dcount("[ID]","TableName","[Design]='" & [Design] & "'")
(don't try typing this, use Copy and Paste, or you might get the single/double quotes wrong).

Also, your table name does not have any spaces in it, does it?

Quite frankly, I rarely use DCOUNT (I think there are other issues with it, why it is typically not recommended, but I cannot remember the details around it).
I always using the query method I recommended.
I tried both, the 2nd one gives me this error: The specified field '[Design]' could refer to more than one table listed in the FROM clause of your SQL statement.

And also yes, i do have space in the table name... Is there a way to change it and update it across the whole Queries easily or do i have to start over the DB?
 
Upvote 0
Finally made it work, thank you both! For those who might come across this thread oneday here's how i wrote the formula based on my example:

Dcount("[Design]","TableName","[Design]=' "&[Design]&" ' ")

Also I made the counter work too:

Dcount("[ID]","TableName","[Design]=' "&[Design]&" ' AND [ID]<="&[ID])

***remove the spaces between quotation marks***
 
Upvote 0
Solution

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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