Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Query (97)

    I've got a query that calculates a patients age, then indicates the age range (for example "Under 19", "20-29", etc.) for a specific date (date is entered when query is run). What I would like to do is count the number of "Under 19"s, "20-29"s.

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

    Re: Counting Query (97)

    If you already have a query that calculates the age range, you can make it into a totals query that Groups By age range and Counts on another field (or on *). Set the Totals option for the parameter (the date you are prompted for) to Where.

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Query (97)

    The following algorithm will convert an Age to a 10-year wide grouping bin "midpoint" (05, 15, 25, ...) which you can use to Group On in a Totals query:

    (Age 10) * 10+ 5

    Notice that the backslash is for integer division.

    You can do this in a single totals query by setting one Group On-Count field as the above algorithm with Age = (Year(Now()) -Year(dateofbirth)), and a second field --the "BinLabel" ("Under 10 years", "10 to 19 years", "20 to 29 years", etc.) -- as a second Group On-First field.

Posting Permissions

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