Conditional Countif

[Frank Situmorang]

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

 

[Allen Browne]

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

 

[Frank Situmorang]

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
--
H. Frank Situmorang

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

 

[Frank Situmorang]

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

 

[Allen Browne]

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

 

[Frank Situmorang]

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

 

[Allen Browne]

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

Someone else may be able to make a suggestion.

 

[Duane Hookom]

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");
--
Duane Hookom
Microsoft Access MVP

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

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

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