Access Forms & Reports:
Exercise 4-3

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


You need to use what you just learned and what you learned in the previous project, and maybe learn a little more. The exercises with databases will require more steps than in the other lessons!

Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise uses a file from the databases resource files. The default location for these files is c:\My Documents\complit101\databases\ You cannot make changes to these files and save them in the same place. Save the changed documents to your Class disk. This keeps the original resource files intact in case you need to start over or another student will be using this same computer.

Full floppy disk How to handle a full disk


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    Exercises Arrow: subtopic open
    Icon: Exercise Ex. 4-1 Scouts
    Icon: Exercise Ex. 4-2 Ornaments
    Icon: Exercise Ex. 4-3 Computers Today
    Icon: Exercise Ex. 4-4 On Your Own


Search  
Glossary
  
Appendix



Exercise Databases 4-3: Computers Today


What you will do:

Link tables
Create data entry form with subform
Icon: New Skill Create an Option group
Create report calculating totals
Create report with subreport in each record

Start with:   Access open with no databases open, resource files

The fictional company Computers Today publishes several magazines/newsletters. You will create some forms and reports about the subscriptions to their various publications. This is a much-simplified database for managing such information! We will not deal with credit card numbers, or special prices for multiple-year subscriptions, or shipping addresses that are different from customer's address.

Computers Today needs forms to enter customer info and data about each order and what items were ordered.

Computers Today can use reports to answer a number of questions, like:

  • How many subscriptions to which publications did we get for each month? Altogether?
  • What is the total price of subscriptions sold?
  • How much commission did each salesman earn?

Computers Today uses two databases to manage their subscriptions- one for the actual data tables and one for the queries, forms, and reports. There are two advantages to splitting a database in this way.

  • Customizing: Different people or different departments can create their own forms and reports based on the same database of tables, without cluttering up the database for everyone else.
  • Editing: It is easier to transport the smaller database of forms and reports for editing. The bulkier database of tables can stay in one place and keep collecting new data while a database designer works on updating/revising the other database.

There is one important disadvantage to splitting your database this way.

  • Moving: If you move one of the two databases, your links will break. The tables can be re-linked, but you must know where the database of tables is!

Link Tables

  1. From your resource files, copy the file computerstoday-data.mdb to your databases project4 folder on your Class disk.
     
  2. Create a new blank database named computerstoday-ex4-3.mdb.
     
  3. Link to all of the tables in computerstoday-data.mdb from the new database.
     
  4. RelationshipsCheck the Relationships.
     

Form with Subform Containing a Subform

To completely describe an order, you need information on the Customer, on the order as a whole, and on what individual items were ordered. That's why there are three tables for this info and why you need 3 forms. Once you have the basic forms, you modify them to use as subforms. So the actual form that a salesman would fill in will have a subform inside a subform! This offers many opportunities to make a mess!!

(In the interest of preserving the sanity of students and their teachers, I have omitted much of the detail that would actually be needed to do real orders of this type.)

  1. Create a form for the Customers table information, named Customers.
    • Include a Form Header labeled Customers and Orders.
    • Add the logo icon, computerstoday-logo.gif, from the resource files to the left side of the Form Header.
       
  2. Create a form for the information in the Orders table, named Subform-Orders.
    • Icon: New Skill Use an Option group for the SalesmanID. A wizard sets this up. The names and ID numbers of  the 4 salesmen are in the Salesman table.
      Note: New salesmen will not be added automatically to an Option group. You will have to revise the form yourself.
    • Use a List Box for the Payment Method
       
  3. Create a form for the information in the OrderItems table, named Subform-OrderItems.
    • Show the OrderItems in Continuous Form view.
    • Use a drop list for the ProductID to show the name of the publication while storing the ProductID number.
    • Use a drop list for the number of years the subscription is for. Possible values are 1, 2, and 5.
    • Do not show the Order ID or the OrderItemID in this subform.
    • In the Detail section, add a control that calculates the total for the item ordered, which multiples the price by the number of years subscribed.
    • In the properties of the Detail section, set Keep Together to Yes.
      This will keep the customer record together on one page.
    • In the Form Footer, add a control that calculates the total for the order.
      (This will be a sum of calculated controls! How does one do that??? Do you remember?)
      Note: To display this kind of total with the order info on the Subform-Orders, you must do the calculation in a query underlying the form because you cannot sum calculated controls.
       
  4. Add the Subform-OrderItems to the form Subform: Orders as a subform.
     
  5. Add the Subform-Orders to the form Customers.
    Size the controls and the forms so that there are no horizontal scroll bars. That can take a lot of tweaking!
     
    You may arrange and format your form to look quite different from the illustration below. This is just a suggestion. One alternative would be for all customer info to be at the top with Order info below it and the items ordered at the bottom with the total for the whole order.

    Form View: Customers form with Order subform, containins OrderItems subform

     

  6. Check Print Preview. If necessary for your layout, make changes that will let the form fit within the width of the page. You may need to change Page Setup to use Landscape orientation or perhaps change the margins.
     
    What happens with customers who have more than one order?
    Each order is on a separate page. The Order subform does not include the customer's name or ID number. That's fine for form view, but possibly a problem for printed forms!
     
  7. Add your name to the Page Footer.
    If you wish, you may create a more printer-friendly version by removing the background colors from each section before doing the next step.
     
  8. Icon: Save Save your changes.
  9. Icon: Print Print the  2 pages for CustomerID #3.

    Print Preview: customer #3, page 1 Print Preview: customer #3, page 2
    The Form Header does not print with this customer. It prints only with the first record. For forms that you will be printing often, it may work better to put such title info in the Page Header or even in the Detail section.

Report: Counting Orders

  1. Create a query named QOrdersbyMonth that will be the source for a report.
    • Use the tables Orders, Customers, and Salesmen.
    • Include all of the fields from Orders.
    • Include the name parts from Customers and from Salesmen.
    • Create two new fields, CName and SName, that concatenate the name parts, last name first, for Customers and Salesmen. Icon: New Skill You will have to include the name of the table in the concatenation expression. Both the table of customers and the one for salesmen use the same field name for the parts of a name. Not good choices!
      For example, instead of using [LastName] in the expression for the new column, you must use [NameOfTable]![LastName].
      Be careful not to introduce any spaces into the expression!
       
  2. Create a report named Orders by Month and Year, based on the query you just created.
    • Group the orders by Year and then by Month. Show group headers and footers.
      (You will use the same field but different Group On values.)
    • Sort the orders by OrderID after the groupings.
    • In the Year header, add a text box labeled Year which has as its control source the expression =DatePart("yyyy",[DateReceived]) This will display the year value that the Sorting and Grouping dialog is using.
    • Similarly,  for the grouping on the month, use the same expression but change "yyyy" to "m". The month number will show. The DatePart function will not show the name of the month.
    • Count the number of orders in each month and show the total in the group's footer.
    • Count the number of orders for each year and show the total in the group's footer.
    • Count the number of orders for the whole report and put the total in the Report Header.
    • Add your name to the Page Footer.
    • Create two columns, going Down and then Across.
    • Make adjustments so that the report fits on 2 pages.
    • Icon: Save Save your changes.
    • Icon: Print Print (2 pages).

    Your report may look a lot different from the illustration, which is just a suggestion for formatting.

    Print Preview: Orders by Month, page 1 Print Preview: Orders by Month, page 2
     

Report: Summary only

  1. Summary reportCopy the report and rename it Orders by Month and Year, Summary 
  2. Remove the details, leaving only the group and report totals.
  3. Change the title to Orders Summary.
  4. Icon: Save Save your changes.
  5. Icon: Print Print (1 page).
    Your summary report may look much different from the illustration, which is just a suggestion.
     

Report: Group Totals of a Calculated Value

Computers Today wants to know commission paid to each salesman. You will have to calculate the total of sales and multiply by the commission rate for each salesman in the underlying query, otherwise you cannot get the totals you want in the report. Remember the problems with trying to add up calculated values!

  1. Create a new query to be the record source for the report. You will need a full name for salesmen. You already did that in another query, so you might as well start with that one and adapt it.
    • Copy the query QOrdersbyMonth and paste it to the Database Window. Rename it as QSalesman Commissions.
    • Delete the 3 fields about customers and delete the Customers table from the query.
    • Add the CommissionRate field from the Salesmen table to the grid.
    • Add the tables Products and OrderItems to the query.
    • Drag the fields SubscriptionLength and PricePerYear to the grid.
    • Add a calculated field named ItemPrice to calculate the total paid for each subscription.
       
  2. Report: Salesman CommissionsCreate a new report named Salesman Commissions
    • For the source for the report, use a query QSalesman Commissions.
    • Do not use AutoReport or Report Wizard.
    • Group on the full name of the salesmen, sort ascending, and show the Group Header.
    • Reduce the height of the Detail section to zero. You only need the group header for this report.
    • In the Group Header, add the following controls:
      • Control to add up the ItemPrice values.
        This is the total amount of sales by the current salesman.
      • Name this control TotalSalesman and edit its label to read just Total:.
      • CommissionRate. Edit its label to read Commission Rate (%):
      • Control to calculate the amount of commission.
        (Multiply the rate times the amount of sales and then by 0.01 since the values are supposed to be percentages. That means that a rate of 9 is actually 0.09).
    • Move the labels for the new controls to the Page Header.
      (Cut and Paste and drag to new position)
    • Format all of the controls that are dollar amounts as Currency.
    • Add a label that shows your name in the center of the Page Footer.
    • Icon: Save Save your changes.
    • Icon: Print Print (1 page).
      Your report may be quite different from the illustration.
       

Report with Subreport

To the report you just created, you will add a subreport that will list the orders for each salesman.

  1. Report to be used as a subreportCreate a new report named Subreport-OrderItems that will be a subreport for each order.
    • Include the OrderID.
    • For OrderID, change the property Hide Duplicates to Yes.
    • Show the name of the publication ordered and not its ItemID
    • Include the price and number of years subscribed.
    • Show the calculated total price, ItemPrice, for each subscription.
       
  2. Create a new report:
    • Copy and paste the report Salesman Commissions to the Database Window.
    • Name the new report, Salesman Commissions, with subreport
  3. Open the report Salesman Commissions, with subreport in Report Design View.
     
  4. In the Sorting and Grouping dialog, add a grouping on OrderID and show just the group header and set its Keep Together property to With First Detail.
     
  5. In the new group header, add a subreport control using the Subform/subreport tool from the Toolbox bar. Drag to create a control that is about as wide as the controls in the SName header.
     
  6. In the wizard for the subreport, choose:
    • Use the existing form/report Subreport-OrderItems.
    • Accept the first suggestion for linking
    • Accept the default name.
       
  7. Check the Print Preview. Make any adjustments/corrections needed.
    Your document may look different from the illustration but all the same parts should be there.
     
  8. Icon: Save Save your changes.
  9. Icon: Print Print (2 pages).
    Your report may be quite different from the illustration.

Preview: Commissions for Salemen with subreport of orders - page 1 Preview: Commissions for Salemen with subreport of orders - page 2

Export

  1. Export the report Salesman Commissions, with subreport to your databases project4 folder on your Class disk, using the default names, in the following formats:
    • TXT
    • RTF
    • XLS
    • HTML
    • Snapshot
  2. Icon: Save Save the report as a Data Access Page to your databases project4 folder on your Class disk. Accept the default name.
     
  3. View each of the new files in an appropriate program.
    Any unexpected results??

TXT:  Exported reported in TXT format, page 1 Exported report in TXT format, page 2

RTF: Exported report in RTF format, page 1 Exported report in RTF format, page 2

XLS: Exported report in XLS format

HTML: Exported report in HTML format, page 1 Exported report in HTML format, page 2

Snapshot: Exported report in Snapshot format, page 1 Exported report in Snapshot format, page 2

Data Access Page: Data Access Page, collapsed Data Access Page, salesman expanded