Reports with Calculated Controls Calculations Needed in Reports

Reports with Calculated Controls

Calculations Needed in Reports May be times when we need calculations in a report. ◦ Total of how much a salesman has sold ◦ Report showing total inservice training hours an employee has attended.

What does it take to do this? To perform calculations, controls are placed in the report. Three types of controls ◦ Bound controls – used to display data that comes from fields in the database (Employee ID No. ; First Name, etc. ) ◦ Unbound controls – Not associated with data from the database Contains things such as report title, etc.

Types of Controls Continued Calculated controls – displays data that is calculated from other data. ◦ Total Important to know the three different types of controls and what they store.

Creating a Report Can create forms different ways. One way is by clicking Report button. ◦ A generic looking form – no control over the way it looks and fields contained. Report Wizard ◦ Can select table, etc. , to create report from ◦ Can select fields to be included in report ◦ Can add grouping

Let’s Try It Open and save the Shelves Done Right Customers table and Salesmen table and Export them into an Access database. Use the Report Wizard. When creating one with the report wizard, base it on the customers table. Include the fields salesman no. , customer name, and balance. Add grouping by salesman no. It would be great to know how much each salesman has in outstanding fees. This is where we will add a calculated control to each salesman’s section.

In layout view, look at the three new tabs: Format, Arrange, and Page Setup Make sure the Format tab is selected. Right click on the first dollar amount in the Balance column. Select Total Balance and then sum. This feature will add a total for each salesman as well as provide a total sum for all amounts due from all salesmen.

When working in layout view, Access uses the right control for you. Select other options on the Total Balance sub menu to see each option’s effect on the data but end the task on the sum option.

Extra Practice Using the Nursing Home Database for extra practice, create a report that lists all of the nurses according to their title (1 st grouping option) and status (2 nd grouping option) -full time employees, part time employees, prn, and terminated employees. Add grouping to the report grouping them by status. Decide which information is most important to create this report. We are only concerned with names.

After creating the report, enter a calculated field that counts the number of employees in each grouping and also gives an overall total number of employees. Omit the employees whose status is TERM from the report.