# Thread: Counting Problem (Access 97)

1. ## 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. ## 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. ## 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]

4. ## 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. ## 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.

6. ## 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. ## 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. ## 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.

9. ## Re: Counting Problem (Access 97)

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

10. ## 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

#### Posting Permissions

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