Results 1 to 4 of 4

Thread: counts

  1. #1
    aye Guest

    Default counts

    Trying to count mothers and children in a single table, some mothers have more than one child but every time i run the query it says there are equal numbers. I am using access and distinct doesn&#039;t seem to be working.<BR><BR>At present i am using the following:<BR><BR>SELECT DISTINCT Count(living.motherid) AS mothers, Count(Living.babyid) AS no_children, Living.ethnicity<BR>FROM Living<BR>GROUP BY Living.ethnicity;<BR><BR><BR>DOES DISTINCT NOT WORK IN ACCESS OR AM I BEING A MUPPET

  2. #2
    Join Date
    Dec 1969

    Default RE: counts

    Using distinct or not in this sense will not make any difference.<BR><BR>SELECT Count(Distinct living.motherid) AS mothers, Count(distinct Living.babyid) AS no_children, Living.ethnicity<BR>FROM Living<BR>GROUP BY Living.ethnicity<BR><BR>is probably what you should try. Whether it works in Access or not I do not know. Works in other DBMS though.<BR><BR><BR><BR><BR>

  3. #3
    aye Guest

    Default RE: counts

    am afraid no joy there.

  4. #4
    No Name Guest

    Default RE: counts

    Not sure how these records are entered or exactly what you are looking for.<BR>Are you looking for just a count showing a total of all mothers and a count showing all children? <BR>ie. Mothers = 13982 , children = 28500<BR>Your query was fine just in wrong order, do it like this...<BR><BR>SELECT Living.ethnicity, Count(living.motherid) AS mothers, Count(Living.babyid) AS no_children, <BR>FROM Living<BR>GROUP BY Living.ethnicity;<BR><BR>This will show a count for each mother and all children...<BR><BR>But if a mother has 3 kids and is entered as 3 separate records like this<BR>motherid = 101, babyid = 287<BR>motherid = 101, babyid = 292<BR>motherid = 101, babyid = 379<BR><BR>Then in this query it will count 3 for mothers with 3 kids since there are 3 separate records...<BR>more info might be able to help out more.<BR><BR>Cheers<BR><BR>

Posting Permissions

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