Count in Access

[bfinder]

Hi,

I have a database in which there is a table with the field "Gender" and each person has indicated "Male" "Female" or "Other".

In a report how can I display the following:
Total Male:
Total Female:
Total Other:

Thanks for your help!

[MMcCarthy]

Create a query as the record source for the report:

SELECT Count(IIf([Gender]="Male",1,0) ) As TotalMale,
Count(IIf([Gender]="Female",1, 0)) As TotalFemale,
Count(IIf([Gender]="Other",1,0 )) As TotalOther
FROM TableName;

If you already have a report and you just want to show totals at the bottom then you need a group or report footer. Create 3 textboxes and put the following code in them.

=Count(IIf([Gender]="Male",1,0) )
=Count(IIf([Gender]="Female",1, 0))
=Count(IIf([Gender]="Other",1,0 ))

Hi,

I have a database in which there is a table with the field "Gender" and each person has indicated "Male" "Female" or "Other".

In a report how can I display the following:
Total Male:
Total Female:
Total Other:

Thanks for your help!

[bfinder]

I entered what you sent in SQL view and subbed in "guides" for the TableName.
When I look at the query in Datasheet mode it has three columns that all say "56" which is the total amount of entries in the table, it hasn't counted them.

Create a query as the record source for the report:

SELECT Count(IIf([Gender]="Male",1,0) ) As TotalMale,
Count(IIf([Gender]="Female",1, 0)) As TotalFemale,
Count(IIf([Gender]="Other",1,0 )) As TotalOther
FROM TableName;

If you already have a report and you just want to show totals at the bottom then you need a group or report footer. Create 3 textboxes and put the following code in them.

=Count(IIf([Gender]="Male",1,0) )
=Count(IIf([Gender]="Female",1, 0))
=Count(IIf([Gender]="Other",1,0 ))

[MMcCarthy]

Sorry substitute sum for count

SELECT Sum(IIf([Gender]="Male",1,0) ) AS TotalMale,
Sum(IIf([Gender]="Female",1, 0)) AS TotalFemale,
Sum(IIf([Gender]="Other",1,0 )) AS TotalOther
FROM guides;

I entered what you sent in SQL view and subbed in "guides" for the TableName.
When I look at the query in Datasheet mode it has three columns that all say "56" which is the total amount of entries in the table, it hasn't counted them.

[nlsmith326]

I have a similar question, how can I run a SUM in a report if I to count by a combination of fields? Here's the code I've been using, and I keep getting an #Error....

=Sum(IIf(([Age]>"18",1,0)) and ([Searched]=-1,1,0)))

Many thanks,
~Natalie

Sorry substitute sum for count

SELECT Sum(IIf([Gender]="Male",1,0) ) AS TotalMale,
Sum(IIf([Gender]="Female",1, 0)) AS TotalFemale,
Sum(IIf([Gender]="Other",1,0 )) AS TotalOther
FROM guides;

[Stewart Ross]

noticed the AND part too

I have a similar question, how can I run a SUM in a report if I to count by a combination of fields? Here's the code I've been using, and I keep getting an #Error....

=Sum(IIf(([Age]>"18",1,0)) and ([Searched]=-1,1,0)))

Many thanks,
~Natalie

Hi Natalie. It may be a similar question, but it's not the same and should be posted in a separate thread. Posting a question on the back of somebody else's thread is given the sinister sounding title of Thread Hijacking - I'm sure you will be aware of it in future and post your own question instead!

To answer your question, you are treating the age as a string by placing it inside double quotes: SUM(IIF([Age]>"18", 1, 0)).... As the age field is much more likely to be a number this is the probable cause of one error.

Another error is in leaving the [Search] part dangling outside of your SUM. As it is a boolean (True/False) value bring it into the IIF and AND the two criteria:

=Sum(IIf(([Age]>18) AND [Searched],1,0))

-Stewart