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

    Counting Problem (Access 97)

    I've got a database where I want to count the number of patients by city, and divide them up into age ranges. But I only want the patients that were seen in a specified date range. My report would look something like this:
    Between January 1, 2001 and January 31, 2001
    Mainville
    Ages 20-29: 150
    Ages 30-39: 200
    Ages 40-49: 100

    Cityville
    Ages 20-29: 100
    Ages 30-39: 150
    Ages 40-49: 200

    Anyville
    Ages 20-29: 125
    Ages 30-39: 200
    Ages 40-49: 130

    What I have done already is created a query that pulls our only Mainville clients, then a queries to pull out patients in the different age groups, then another query to count them. Therefore, I have had to create 3 queries to get my answer for the total number of Mainville clients between the ages of 20-29.

    Question #1: Is there an easier way to do this?
    Question #2: How/where do I specify the date range? I know I can use the Between command, however, I don't know where I could place it.

    HELP!!

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Problem (Access 97)

    I'd suggest doing this in two steps:

    1. Create a query that grabs the location and patient name from your table and calculates the age range for the patient (i.e., something like <font face="Georgia">Age Range: Int([Age]/10)*10 & " - " & Int([Age]/10)*10+9</font face=georgia>) and specifies the date range as the criteria. The whole query might look something like
    <font face="Georgia">
    SELECT Name, Location, "Ages " & Int([Age]/10)*10 & "-" & Int([Age]/10)*10+9 & ":" AS [Age Range]
    FROM Patients
    WHERE DateIn Between #8/1/02# And #8/31/02#;
    </font face=georgia>
    2. Build a Crosstab query based on the above that counts up the names for each location and age range. For example if the above query were named "qdfAgeRanges" then the crosstab query would look something like:
    <font face="Georgia">
    TRANSFORM Count([Name]) AS PatientCount
    SELECT [Age Range]
    FROM [qdfAgeRanges]
    GROUP BY [Age Range]
    PIVOT [Location];
    </font face=georgia>
    Hope this helps.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Counting Problem (Access 97)

    I don't think pivots work in Access 97.
    To get the format suggested, you only to turn the query into a grouping query that groups by Location, then by Ages, and counts the names. (If the names might repeat, do you want them counted more than once?)

    You can build the report on this query.

    To limit the date range, launch the report from a form with two textboxes (formatted as dates) labelled something like txtdatefrom and txtdateto. Add the relevant date field to the query, and put where in the Total line of the query grid.
    Then use the expression builder to put something like this on the criteria line:
    Between [forms]![frmmyform]![txtdatefrom] and [forms][frmmyform]![textto]
    Regards
    John



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

    Re: Counting Problem (Access 97)

    Pivots certainly do work in Access 97.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Problem (Access 97)

    Ther are no Pivot Tables in A97, but the PIVOT expression is used in SQL to create a crosstab query.
    Charlotte

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

    Re: Counting Problem (Access 97)

    What do you mean by a pivot table, are these available beyond A97?
    Pat <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

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

    Re: Counting Problem (Access 97)

    I think that a simple select query will be enough for the layout you want. You can do all the grouping and sorting in the report. You can design it yourself, or let the Report Wizard take care of the basic design for you, and modify the layout manually.

    In the Wizard, specify that you want to group by City and Age. Click Grouping options and specify that you want Age to be grouped in units of 10. Also specify counting options, and that you don't want detail records.

    If you want to do the grouping by age in a query, you can use the Partition function for this. In a variation of the query proposed by Tom Bushaw:
    <font face="Georgia">
    SELECT Name, Location, "Ages " & Partition(Age,0,100,10) AS [Age Range]
    FROM Patients
    WHERE DateIn Between Forms!frmSelect!txtDateFrom And Forms!frmSelect!txtDateThru;
    </font face=georgia>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Problem (Access 97)

    In A2k and beyond you have a pivot table form, which is basically an Excel Pivot Table embedded in an Access form.
    Charlotte

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

    Re: Counting Problem (Access 97)

    Thank you, I must have a look at this.
    Pat

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Counting Problem (Access 97)

    Access 97 also has a pivot form. If you go to the form page of the database window and click the new button, the bottom option should be "Pivot Table Wixard". As always, use the help for explanations

    I'm using Access97 sr-2b
    "Heading for the deep end"

Posting Permissions

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