|
Displaying Subtotals and a Grand Total on a Form That Displays a One-to-Many Relationship
By Tom Wickerath 
Note: These instructions were written when I taught an Access course, using Microsoft Access 2000, at a local community college. They were written for use with a student database, "Bavant Marine Services," which was created by each student using step-by-step instructions in Project 1 of the book used with this course. An enhanced version of this database is available for download (295 KB) to follow along with these instructions. This database includes embedded pictures, a practice that can cause severe database bloat and should be avoided. There are other techniques available for making images available in forms and reports.
Special Note: This problem of database bloat is apparently being fixed with the next release of Microsoft Office Access.
http://blogs.msdn.com/access/archive/2005/11/07/490113.aspx states:
- Modern image support - use png, gif, jpg, etc. without database bloat and maintain transparency.
The Technician Master form in Bavant Marine Services is used to display a one-to-many relationship. On the "one" side, we see information about the technician. The "many" side of the relationship is displayed in a subform, which includes the marinas each technician services, along with the warranty and non-warranty amounts for each marina. This document will explain how to include textboxes to display subtotals for each of these fields, as well as a grand total field. To see an example of this, click here. You can easily produce a grouped report to return this type of information, however, this does not allow one to see real-time changes to these amounts during the data entry phase.
To accomplish our goal, we take advantage of the fact that controls in a form's Header and Footer sections do not display in Datasheet View. Datasheet View is the spreadsheet-like view of a subform. In order to see these controls' sections, you must be in Form View.
Figure 1: Datasheet view of the fsubMarinas subform
Figure 2: Design view of the fsubMarinas subform
Add two textbox controls in the Footer of your subform to sum the Warranty and NonWarranty fields. Give your new textboxes reasonable names, and set their control sources as indicated below:
|
Name of Control
|
Control Source
|
Format
|
|
txtWarrantySubtotal
|
=Sum([Warranty])
|
Currency
|
|
txtNonWarrantySubtotal
|
=Sum([NonWarranty])
|
Currency
|
|
Note that we must include the parentheses with the Sum function. The square brackets are used when referencing a field name. Their use is required when special characters have been used. Record the names of your new controls. You will need to know these names later on when you are setting the control sources for the textboxes on your main form, unless you use the Expression Builder. There is no need to keep the labels that are automatically included when you create each textbox on the subform.
Figure 3: Subform with two textbox controls added to Form Footer
Switch to Form View to test out your two new textboxes. You should see the sums for all records displayed, as shown below. In Datasheet View, your subform will look identical to Figure 1.
Figure 4: Subform displayed in Form view to test the two new textboxes
We are now ready to add textboxes to our main form, which reference the values of our subtotal textboxes from the subform. One of the first things we need to do is to record the name of the subform object in design view. We are not interested in the name of the subform itself, shown as the "Source Object" on the Data tab, only the name of the subform object. (Note: It is not unusual to find that these names are identical.) We can learn this name by selecting this object, such that the sizing handles are shown around the entire object. Open your main form in design view, make sure you are viewing the properties, and then select this object as shown below:
Figure 5: Selecting the Subform object to learn its name
Finally, we add three textbox controls on the form to display the warranty and non-warranty subtotals, along with the grand total. You can use the same name, or a different name, for your subtotal textboxes as compared with the names you used in your subform. However, the Control Sources will now reference the name of the subform object and the names of the textboxes in your subform as shown below in general form:
=[NameOfSubformObject].[Form]![NameOfTextboxInSubform]
The grand total textbox is calculated by simply adding the two subtotal textboxes on your main form. You can add a line control to add visual clarity.
|
Name of Control
|
Control Source
|
Format
|
|
txtWarrantySubtotal
|
=[Marinas].[Form]![txtWarrantySubtotal]
|
Currency
|
|
txtNonWarrantySubtotal
|
=[Marinas].[Form]![txtNonWarrantySubtotal]
|
Currency
|
|
txtGrandTotal
|
=[txtWarrantySubtotal]+[txtNonWarrantySubtotal]
|
Currency
|
|
Your finished form should look something like the example shown below. As new marina records get added to the subform, the warranty and non-warranty subtotals and the grand total textboxes, shown circled below, will be updated. Return to the beginning.
Created: Dec. 2, 2001
Last Updated: Sept. 20, 2005
Copyright © 2005 - 2006 Tom Wickerath. All rights reserved. Reprinted by permission.
 Top
|
|
About the author:
Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data.
Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel.
Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups.
Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks."
-- Tom Wickerath
http://www.nwkidney.org/ways2help/donatenow/
For questions regarding this tutorial, please contact Tom at:
|
Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on one of his articles or tips, or you have been specifically invited by Tom in a newsgroup posting to contact him. If your question references a question in the newsgroups, please include the URL to the post
Top
Visitors since 20 Sept. '05:
|
