Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fun with Grouping and Counting (Access 97)

    I wish to put together a report that displays the following (it's for a nursing home tracking report) where the data is grouped in three-year intervals:
    Move-ins by Age (last 2 years)
    93-91 ...... 1
    90-88 ...... 0
    87-85 ...... 1
    84-82 ...... 4
    81-79 ...... 10
    78-76 ...... 6
    75-73 ...... 0
    72-70 ...... 1

    Given a table with patients and their ages at admittance listed, how would I do this?

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

    Re: Fun with Grouping and Counting (Access 97)

    The query behind the report would be somthing like this:

    SELECT int((Age+2)/3)*3 as Age1, int((Age+2)/3)*3-2 as Age2, count(Age) as Admissions
    FROM Persons
    GROUP BY int((Age+2)/3)*3, int((Age+2)/3)*3-2

    The field Age is assumed to be a field in the table Persons, this query returns 3 columns, Age1 (eg. 93), Age2 (eg. 91) and Admissions (Moveins).

    HTH
    Pat

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

    Re: Fun with Grouping and Counting (Access 97)

    Another possiblity is to use the little-known (?) Partition function:

    SELECT Partition([Age],40,100,3) AS AgeGroup, Count(*) AS Admissions
    FROM Persons
    GROUP BY Partition([Age],40,100,3);

    Partition([Age],40,100,3) means: group Age in intervals of 3, starting at 40 and stopping at 100.

Posting Permissions

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