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




[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]

 

[TechnoWay]

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.

 

[Joe4]

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.

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

 

[welshgasman]

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.

DCount Function - Microsoft Support

support.microsoft.com

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.

 

[TechnoWay]

When you do not know the syntax of a function, Google for it and go to the source.

DCount Function - Microsoft Support

support.microsoft.com

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

 

[Joe4]

Maybe try:

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

or

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.

 

[welshgasman]

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.

 

[TechnoWay]

Maybe try:

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

or

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?

 

[TechnoWay]

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