Hi John,
I decided that I didn't like using the domain aggregate function that I showed in step 11
in my original post. The reason is that your count will be off if you add new students to
tblStudents, without having at least one corresponding record for the student in
tblSessions. Here are some additional steps that replace the original step 11. This
allows us to avoid using the DCount function. I once heard a presenter state that using
domain aggregate functions is like eating candy--something that should be avoided! For
one thing, they tend to be slower.
Replacement Step 11: Add a textbox to the pkStudentID Header and name it txtRunningSum.
Delete its associated label. Set the following properties for this textbox:
On Data tab
Control Source: =1
Running Sum: Over Group
On Format tab
Visible: No
Fore Color: 255
Note: Changing the Fore (font) color to 255 (red) isn't really required. This is the
convention that I use when I set the visible property for a control = No. This way, I
know at a glance that I'm dealing with a hidden control.
12. Add a textbox to the Sex Footer. Set the following properties for this textbox:
On Other tab
Name: txtSexSum1
On Data tab
Control Source: =[txtRunningSum]
Running Sum: No
Align this textbox under the Name textbox. Edit the associated label, so that it reads
something like "Subtotal:".
13. Save the report. Click on Preview. The report should open and display the correct
grouped sums for the male and female students. If this is good, then stop here. If you'd
like a grand total, then proceed to step 14.
14. Add a second textbox to the Sex Footer (not visible--place anywhere in the Sex
Footer). Delete its associated label. Set the following properties for this textbox:
On Other tab
Name: txtSexSum2
On Data tab
Control Source: =[txtRunningSum]
Running Sum: Over Group
On Format tab
Visible: No
Fore Color: 255
15. Click on View > Report Header/Footer if required to produce a report footer. Use
your mouse to drag the height of this footer from zero to about 0.25 inches. Add a new
textbox to this section. Set the following properties for this textbox:
On Other tab
Name: txtGrandTotal
On Data tab
Control Source: =[txtSexSum2]
Running Sum: No
Position this new textbox directly underneath txtSexSum1, and add an appropriate caption
to its associated label, such as "Grand Total:". Save the report and preview it.
Let me know if you have any difficulties reproducing this. This lengthy set of directions
looks more complicated than they really are.
Tom
_____________________________________
Hi John,
Here is one way to achieve the results you seek. There may be other ways as well. This
method depends on having a normalized structure to store student information (Name & Sex)
in one table, and Session information in a related table. It will not work if you have
all of this data in a single table.
tblStudents
pkStudentID Autonumber primary key
FirstName Text
LastName Text
Sex Text
tblSessions
pkSessionID Autonumber primary key
fkStudentID Foreign key. Same as pkStudentID from tblStudents
SessionAttended Text
1. Create a 1:M relationship between tblStudents and tblSessions. Fill in the
appropriate data to each table.
2. Create a query based on tblStudents and tblSessions. Add the pkStudentID, FirstName,
LastName, Sex and SessionAttended fields. Run the query. It should return 8 records for
the sample data you provided. Save the query as qryStudentsAndSessions.
3. Create a new report based on qryStudentsAndSessions. Use the New Object toolbar button
and select Report (not AutoReport). Choose Report Wizard. As an alternative, you can
click on Insert > Report, and choose Report Wizard. Then select qryStudentsAndSessions in
the dropdown.
4. Add all five fields to your report. In the next screen, which reads "How do you want
to view your data?", select tblStudents.
5. In the next screen, which reads "Do you want to add any grouping levels?", add the Sex
field.
6. In the next screen, specify an ascending sort for SessionAttended if you wish.
7. Accept the default choices for layout (Stepped & Portrait). In the next screen, pick
a suitable style.
8. In the final screen of the wizard, which reads "What title do you want for your
report?", name it rptStudentsAndSessions. Note: you can adjust the label text later in
report design view.
9. Open the report in design view. Size the fields appropriately. Change the label text
to read whatever you want (for example: Students and Sessions). Delete the pkStudentID
label from the report's page header. Click on View > Properties to bring up the
Properties sheet. Select the pkStudentID field. Set the visible property for this field
to No. Preview the report. At this point, you should have a report that displays the
grouped data as you wish, but without any counts included.
10. Click on View > Sorting and Grouping...
Select the Sex field. Select Yes for Group Footer.
Note: The reason I had you add the primary key field, even though you won't be displaying
it in the report, is so that you can group by this unique identifier. Otherwise, if you
have two students with the same names, you'll end up grouping their records together.
Close the Sorting and Grouping dialog box.
11. Add a new calculated textbox to the Sex footer with the following control source:
=DCount("pkStudentID","tblStudents","Sex = '" & [Sex] & "'")
Note: the criteria portion of the DCount is a little tricky. After the = sign, we
have:
single quote double quote & [Sex] & double quote single quote double quote
Tom
___________________________________________
Hi Access gurus,
HELP!
I have a report about the gender of students in a school. It contains
Gender, Name and SessionAttended. There are two groups (for Gender and
Name) and a Detail Section (for Session.)
Male
Joe
Fall
Fred
Fall
Spring
Harry
Spring
Count: 4
Female
Mary
Summer
Fall
Spring
Kate
Fall
Count: 4
In the Gender Footer, how do I get the count of Males to be 3, not 4, and
the count of Females to be 2, not 4? In other words, Count(Names) in the
Gender Footer is really counting Sessions, not Names.
If I based the report on a Group-By Query, I would lose the Session Detail
and the report would look something like this:
Gender: Name: Sessions Attended:
Male
Joe 1
Fred 2
Harry 1
Count by gender:
3
Female
Mary 3
Kate 1
Count by gender:
2
This would be easy to write, but it wouldn't tell *which* sessions the
student attended.
