By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Count in Access

P: 2
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!
Oct 16 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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!
Oct 16 '06 #2

P: 2
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))
Oct 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 16 '06 #4

P: 3
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;
Apr 1 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Apr 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.