Question COUNTIF help in Access

[Elmosan]

I will start off saying that, yes, I know there is no COUNTIF in Access, and that this question covers a few areas of Access. So here we go:

I have a database which includes a Members Table. Each member has a different type of Membership:

Social,
Lifetime,
Founders,
Junior,
Full-time

Each of these has been assigned a number from 1-5 respectively, and I am running a Query of the Member Table, counting the number of occurences of Social Members.

In my Query, I have this Criteria:

Amount Social Members: Count(IIf([Membership Type]="1",1,0))

To be truthfully honest, I have written this coding with no actual idea what it means, but I'm trying to get it to search in the Membership Type Field in the Member Table, the amount of 1's, and showing a total amount of these 1's.

 

[Kiwiman]

Howzit

Try

Dcount("[Yourfield],"yourtable","[Yourfield]=1")]

 

[spikepl]

A Count counts all the records thrown at it. You want a Sum.

 

[vbaInet]

Have you considered using a Crosstab query?

By the way, it should have been:

Amount Social Members: Count(IIf([Membership Type]=1,1,[COLOR=Red]Null[/COLOR]))

Count ignores Nulls so it doesn't count ALL records when it sees Null.

 

[Elmosan]

Have you considered using a Crosstab query?

By the way, it should have been:

Amount Social Members: Count(IIf([Membership Type]=1,1,[COLOR=red]Null[/COLOR]))

Count ignores Nulls so it doesn't count ALL records when it sees Null.

I have tried using this code, but an Error Message comes up saying:

Data type mismatch in criteria expression

EDIT: And I'm using an older version of Access, so I don't have the option to make Crosstab Queries. Unless you can anyway...

 

[vbaInet]

Here you go:

Amount Social Members: Count(IIf([Membership Type]=[COLOR=Red]"[/COLOR]1[COLOR=Red]"[/COLOR],1,Null))

 

[Elmosan]

Here you go:

Amount Social Members: Count(IIf([Membership Type]=[COLOR=red]"[/COLOR]1[COLOR=red]"[/COLOR],1,Null))

When the Query is run, only the number 1 is shown, rather than a 3 for the three Social Members...

 

[vbaInet]

It's showing you an exact count of membership types that are "1". You may have set criteria to filter out the other two.

 

[Brianwarnock]

Holy smoke, the answer was given by spikel in post 3 just change your count to sum, this is standard query technique to count the occurrences of values within a field

Brian

 

[vbaInet]

If we're counting occurences we use Count. If we are adding up the values then we use Sum. I'm of the opinion that the OP is counting occurences and not looking to add them up.

If the membership type is 2 then Sum() will give incorrect results but I guess since it's 1s, Sum should do.

 

[Brianwarnock]

Sorry to say this but nope
Sum(Iif(field=value,1,0)) will count the occurrences of value in the field be it numeric or text, by the simple expediency of giving the alias a value of 1 if true and 0 if false then summing these values.

Brian

 

[vbaInet]

Isn't it the same thing as:

Count(Iif(field=value,1,Null))

I had the same discussion with someone on here regarding this and if you want I will explain why the Count() is more effecient than the Sum() option.

 

[Brianwarnock]

I suppose I should own up to 2 things
1 didn't study the whole thread as I thought that the op should have had it all resoled after post 3

2 I have never seen your solution before, I believe it works and suspect that it is more efficient but whether noticeably ? Still we should always aim for best practice.

Brian

 

[vbaInet]

No problem Brian It's not a noticeable difference for a couple of records but when dealing with millions of records it might be worthwhile.

I did think we had solved it ages ago but the OP seems to think he's not getting the correct results.