Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2014
    Posts
    12

    Unanswered: How do I count a letter? (i.e count M = Male and F = Female)

    Hello. I realize that unless it's algerbra you don't count letters, but I'm using Access 2007 and I'm trying to do a query that will count the amount of males and females.

    The actual query is done using MS Access 2007 Query Designer and does the following:

    Query the Unit field and Group by Unit; then count up the unit to provide a total number of people in that unit; then determine how many males and females are in that unit. The SQL from Access 2007 is as follows:

    SELECT Table.Unit, Count(Table.Unit) AS CountOfUnit, Count(Table.Gender) AS CountOfGender
    FROM Table
    GROUP BY Table.Unit
    HAVING (((Count(Table.Gender))="M")) AND ((Count(Table.Gender))="F"));

    The error I'm getting is: Data type mismatch.

    I've been trying this off and on for a couple of days now, and figure if I could get a good understanding of this issue, I should be good on similar issues.

    I did do a search for data type mismatch before submitting this issue, and it doesn't seem like what I'm stating here was addressed; based on the five posts I reviewed. I appreciate the help in resolving this.

    Chas

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    SELECT COUNT(*) AS CountOfMales
    FROM Table
    WHERE Table.Gender = 'M'
    The same goes for Females.
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT Table.Unit
    ,  Count(Table.Unit) AS CountOfUnit
    ,  Sum(Iif(Table.Gender="M", 1, 0)) AS M
    ,  Sum(Iif(Table.Gender="M", 0, 1)) AS NotM
       FROM Table
       GROUP BY Table.Unit
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd try something like:-
    Code:
    select table.unit, table.gender,count(table.gender)
    from table
    group by table.unit, table.gender
    Last edited by healdem; 01-15-14 at 06:10.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hello. I realize that unless it's algerbra you don't count letters, but I'm using Access 2007 and I'm trying to do a query that will count the amount of males and females.
    nah its perfectly logical, you are COUNTing the number of rows with a specific value
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by healdem View Post
    I'd try something like:-
    Code:
    select table.unit, table.gender,count(table.gender)
    from table
    group by table.unit, table.gender
    This was actually my first thought too. If changing the structure of the result set isn't a problem, then this is a better answer. If you've already got code that depends on having one row per table.unit for processing, then I like my first post better.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2014
    Posts
    12

    Thanks Everyone for Your Input!

    Thank you everyone for your responses. I went with Pat P's:

    ---------
    SELECT Table.Unit
    , Count(Table.Unit) AS CountOfUnit
    , Sum(Iif(Table.Gender="M", 1, 0)) AS M
    , Sum(Iif(Table.Gender="M", 0, 1)) AS NotM
    FROM Table
    GROUP BY Table.Unit
    ---------

    As it seems to be the easiest for me to add onto and maintain the consistency of what I need to show in the report when put in a form. (Not sure that makes sense.)

    Basically, when someone clicks on the button that queries the report, they'll get the data that they want to see broken down for a specific group. And because I will need to total up everything to provide the demographics of a group of people, this one seems like a better option for my needs.

    Thanks again! Chas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •