Conditional Countif

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Situmorang - 08 Apr 2008 07:56 GMT

Hello,

From what I learned from this forum I am able to make statistics report for
number of gender, male and female using this formula in the source control:
=Sum(IIf([JenisKel]="L",1,0))

L= stands for male
P= Stands for female

I have also status of the member : A for Active and I = Non Active

I want to know the number of male who are acive. How can I make to IFF in
the formula.

Thanks in advance

Signature

H. Frank Situmorang

Reply to this Message

Allen Browne - 08 Apr 2008 09:11 GMT

Try:
=Sum( IIf( ([JenisKel] = "L") AND ([Status] = "A"), 1, 0))

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello,
>
[quoted text clipped - 13 lines]
>
> Thanks in advance

Reply to this Message

Frank Situmorang - 08 Apr 2008 10:06 GMT

Thanks Allen, it works perfecly, now I get to the difficult one I have tried
to see on help of Access but we can only insert from the table fields for the
component of graph while mine is I put in the report footer the unbound and I
put that formula.

How can I put graph on the page footer too.

Thanks in advance

Signature

H. Frank Situmorang

> Try:
> =Sum( IIf( ([JenisKel] = "L") AND ([Status] = "A"), 1, 0))
[quoted text clipped - 16 lines]
> >
> > Thanks in advance

Reply to this Message

Allen Browne - 08 Apr 2008 11:08 GMT

I'm not sure I follow this question Frank.

A graph has its own source: typically different from the query that feeds
the main report. It can therefore go in any section you wish.

I also did not understand how this question was related to the IIf()
question. Or perhaps you intended to post this as a new question.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks Allen, it works perfecly, now I get to the difficult one I have
> tried
[quoted text clipped - 7 lines]
>
> Thanks in advance

Reply to this Message

Frank Situmorang - 08 Apr 2008 11:33 GMT

No Allen, my question is related to my statistical report, but the way I made
my report maybe unusual, because I am not expert in Access. This is the way I
made it:

Based on query I put in detail these fields

1. name,
2. membership type
3. Gender
4. memb ership status

then on the pagefooter , since I want to know how many members are male and
femali
How many members are baptized members or just Sabbath School member
How many members are active or passive

That is why I I use Countif or sumif in order not to count the zero field.

Now I want to make a graph of it, since it is in the page footer, I do not
know how to make it using the chart wizard
Thanks for your help

Signature

H. Frank Situmorang

> I'm not sure I follow this question Frank.
>
[quoted text clipped - 15 lines]
> >
> > Thanks in advance

Reply to this Message

Allen Browne - 08 Apr 2008 14:55 GMT

I think you will have to make a query that gives you the grouping you need
(e.g. group by gender, and by status.)

Then use this query as the source for the graph (in the Wizard.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> No Allen, my question is related to my statistical report, but the way I
> made
[quoted text clipped - 39 lines]
>> >
>> > How can I put graph on the page footer too.

Reply to this Message

Frank Situmorang - 09 Apr 2008 08:49 GMT

Allen:

From my membership table I already make a query with a calculated field as
follows:
TotalAll: Sum(IIf([JenisKel] Is Not Null,1,0))
TTLMale: Sum(IIf([JenisKel]="L",1,0))
TTLFemale: Sum(IIf([JenisKel]="P",1,0))
ActiveMale: Sum(IIf([STAT_CODE]="A" And [JenisKel]="L",1,0))
ActiveFemale: Sum(IIf([STAT_CODE]="A" And [JenisKel]="P",1,0))
PassiveMale: Sum(IIf([STAT_CODE]="I" And [JenisKel]="L",1,0))
PassiveFemale: Sum(IIf([STAT_CODE]="I" And [JenisKel]="P",1,0))

But when I tried to make a pie chart ( I just need a proportion to total, no
time series) using insert chart and I follow the wizzard instruction, but It
can not shows more than one axis data.

Could you please help me how can we make it

Thanks in advance

Signature

H. Frank Situmorang

> I think you will have to make a query that gives you the grouping you need
> (e.g. group by gender, and by status.)
[quoted text clipped - 44 lines]
> >> >
> >> > How can I put graph on the page footer too.

Reply to this Message

Allen Browne - 09 Apr 2008 13:52 GMT

I can't really help you with this, Frank.

Someone else may be able to make a suggestion.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen:
>
[quoted text clipped - 15 lines]
>
> Could you please help me how can we make it

Reply to this Message

Duane Hookom - 09 Apr 2008 15:00 GMT

I'm not sure how you would create a single pie chart since it looks like you
would create at least 3 whole pies.

One of the issues is that you have "un-normalized" you table by creating
multiple fields/columns rather than multiple records. It is much easier to
create a graph if your Row Source produces records like:

Active Males 20
Active Females 13
Passive Males 8
Passive Females 3

Your Row Source SQL might look something like:
SELECT IIf([STAT_CODE]="A","Active","Passive") & IIf([JenisKel]="L"," Male",
" Female") As GenderStatus, Count(*) As NumOf
FROM tblNoName
GROUP BY
IIf([STAT_CODE]="A","Active","Passive") & IIf([JenisKel]="L"," Male", "
Female");

Signature

Duane Hookom
Microsoft Access MVP

> Allen:
>
[quoted text clipped - 64 lines]
> > >> >
> > >> > How can I put graph on the page footer too.

Reply to this Message