Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quey to count Yes's and No's (97 or 2003)

    How do I count the number of yes's and no's in a table? What I'm trying to count is the number of fish (4 types) that DO have an adipose fin and those that don't. So, I have 4 species and my table has either yes or no to whether or not it has the adipose fin. In my query, I can get a count of the Yes's OR the No's, but I can't seem to get a count like I want.

    For example: I want this:
    Species With Adipose Without Adipose
    Chinook 100 25
    Sockeye 135 43
    Steelhead 256 19


    The query results I get are:

    Species CountOfFish ID Adipose
    Chinook 1 No
    Sockey 14 Yes
    Steelhd 4 No

    Below is a screenshot of my query design.

    Thank your for your patience. I realize I have probably not made myself clear and that this is an elementary question.
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    I would create 2 queries, 1 to count the YESs and the other to count the NOs, then create another query based upon the 2 queries you have already created joining them on Species.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    There appears to be something missing - your screen shot doesn't show any SpeciesID in the Fish table. Normally I would expect to see a join between the two tables, but with this arrangement you should probably get nonsense.
    Wendell

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    To test this I created dummy Fish & Species tables, with some dummy records. I assumed the Fish table should have a SpeciesID field (foreign key) related to primary key of same name in Species table. Query SQL to list number of adipose/non-adipose fish in each species group:

    SELECT tbl_Species.Species_Name, Count(tbl_Fish.FishID) AS [Fish Count], Sum(Abs([Adipose])) AS [Adipose Count], Sum(Abs([Adipose]=False)) AS [Non-Adipose Count]
    FROM tbl_Species INNER JOIN tbl_Fish ON tbl_Species.SpeciesID = tbl_Fish.SpeciesID
    GROUP BY tbl_Species.Species_Name
    ORDER BY tbl_Species.Species_Name;

    See attached screen shot to see what this looks in Query Design view, and the results of query with some dummy records. The numbers add up correctly. You may be able to use something similar in the actual query if this is what you're trying to do. As noted previously, you first need to relate the two tables based on some common field, in this case, SpeciesID.

    HTH
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    You're right. But, I do have a junction table that has the links. See attachment. So now I am wondering. Did I set this up wrong? I am very open to any better suggestions and would be thrilled to send a copy of the db to anyone who wants to take a look and tell me if I have some problems with the way I structured this. Please! The smallest I can get it is 500K though.
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    Here is my standard recipe for posting a database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]An Access 97 database is always *much* smaller than the equivalent Access 2000, 2002 or 2003 database.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    Thanks Hans. I did that and got it small enough to attach. Now if anyone is patient enough to look at this and let me know what I've done wrong, I would greatly appreciate it! Thanks.
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    Hate to say it, but you have been a bit too enthousiastic. qry_Adipose expects that qry_Junction Query exists, but that is not in the database you posted. But perhaps we don't need it. Does the following do what you want? It is MarkD's query with tbl_Junction plugged in.

    SELECT tbl_Species.Species_Name, Count(tbl_Fish.[Fish ID]) AS [Fish Count], Sum(Abs([Adipose])) AS [Adipose Count], Sum(Abs([Adipose]=False)) AS [Non-Adipose Count]
    FROM tbl_Species INNER JOIN (tbl_Fish INNER JOIN tbl_Junction ON tbl_Fish.[Fish ID] = tbl_Junction.[Fish ID]) ON tbl_Species.[Species ID] = tbl_Junction.[Species ID]
    GROUP BY tbl_Species.Species_Name;

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quey to count Yes's and No's (97 or 2003)

    Yes! That worked perfectly. Thank you!

Posting Permissions

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