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!
5 5301
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!
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))
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.
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;
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: thomasp |
last post by:
I am trying to get a record count of a PHP query on a MS Acess database
using ODBC with a DSN for MS ACCESS connection. I got this code from the
PHP manual user notes. It seems to return the...
|
by: Cro |
last post by:
Hello Access Developers,
I'd like to know if it is possible to perform a count in an expression
that defines a control source.
My report is based on a query. In my report, I want a text box to...
|
by: Invalidlastname |
last post by:
Hi,
We are developing an asp.net application, and we dynamically created certain literal controls to represent some read-only text for certain editable controls. However, recently we found an issue...
|
by: Volkan |
last post by:
Hi,
I'm trying to compare two XML documents and i'm using XPath queries to
select nodes. XPathNavigator's Select method runs fast enough and
returns an XPathNodeIterator object. When i try to...
|
by: heckstein |
last post by:
I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Phil Stanton |
last post by:
I am trying to count the fields in a queryDef in an external database.
If I run this in the actaal database I get Fields.count = 6 correctly
Private Sub ObjectName_DblClick(Cancel As Integer)
...
|
by: CampbellJD1 |
last post by:
I am using Access 2003 Professional. I have been working with Access for some time and I have created an MDB with a Linked Table and the Data there is temporarily transferred to other Table for...
|
by: wildThought |
last post by:
If I have an object that contains a generic dictionary inside of it,
how do I get access to its properties such as count?
|
by: craigchalmers |
last post by:
Hi
I am a complete novice so hope someone can shed some light on my
problem/goal.
I have an access database with some records in it. i have two fields
1) ArrivalDate 2) ReturnDate
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |