446,392 Members | 1,484 Online
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
5 Replies

 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

 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