Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Counting multiple items in a query

    Hi,
    I'm just getting to grips with queries at the moment and was hoping that someone could tell me how to do a query as such.

    Imagine there is a table with two fields 'Name' and 'Status.' There are three possible answers to Status; Yes, No and Don't Know.

    How would I create a query that would have all the names then listed next to them would be a count for how many said Yes, how many said No, and how many said Don't Know.

    This seems like it shouldn't be too hard to do but after playing around for many hours I still haven't worked it out.

    Any help would be massively appreciated!

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    As far as I know you can not list all the names with the totals next to each name. You can however use an UNION query to list all the names and the status for each name then have the last three lines of the query show the Total Yes, Total No and Total Don't Know

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Poppa - sounds to me that he is asking "how do I create a crosstab"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Pootle

    I thought he may require a crosstab but with just two columns you can not create a crosstab unless there is another column with a value of 1.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Blimey you are right - this is my problem with not using Access for so long.
    Code:
    SELECT [name], SUM(Iif(Status = "Yes", 1, 0)) AS Yeses, SUM(Iif(Status = "No", 1, 0)) AS Noses, SUM(Iif(Status = "Don't Know", 1, 0)) AS Dinnawses
    FROM myTable
    GROUP BY  [name]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Using the code below for a union query, the names and status will be displayed followed by a total for each status on a separate row.

    See attached for the union query display.

    SELECT tbl_names.person_name AS Name, tbl_names.person_status AS Status
    FROM tbl_names
    UNION SELECT "Total Yes" AS name, Sum(IIf([person_status]="Yes",1,0)) AS Status
    FROM tbl_names
    UNION SELECT "Total No" AS name, Sum(IIf([person_status]="No",1,0)) AS Status
    FROM tbl_names
    UNION SELECT "Total Don't Know" AS name, Sum(IIf([person_status]="Don't Know",1,0)) AS Status
    FROM tbl_names;
    Attached Thumbnails Attached Thumbnails union.JPG  

Posting Permissions

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