Access Basics:
Exercise 2-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 maybe learn a little more. 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 does not use any resource files.
Full floppy disk How to handle a full disk


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro
Project 2: Access Basic Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access ObjectsTo subtopics
    RelationshipsTo subtopics
    Summary
    Quiz
    Exercises Arrow: subtopic open
    Icon: Exercise Ex. 2-1 Employees
    Icon: Exercise Ex. 2-2 Productions
    Icon: Exercise Ex. 2-3 City Soccer League
    Icon: Exercise Ex. 2-4 On Your Own

Project 3: Tables & Queries

Project 4: Forms & Reports

Search  
Glossary  
Appendix

Exercise Database 2-3: City Soccer League

What you will do:

Create a new blank database
Create tables manually and with Table Wizard
Enter data
Create a form
Enter data using form
Print selected records from form
Create a query with Simple Query Wizard
Icon: New Skill Move columns in Query Design View
Icon: New SkillCreate a report using Report Wizard, based on a query
Print report

Start with:   Access open

The City Soccer League needs a database to keep track of the members, staff, teams, and officials.
You will create several tables and database objects to help them manage this information.

  1. Create a new blank database.
  2. Icon: Class disk Save it as citysoccer.mdb
     
  3. Tables:
    • Create a table named Teams in Design View.
      Fields:
      • TeamID - AutoNumber field
      • TeamName - Text
      • Coach - Text
      • TeamColors - Text

    Teams

    TeamName Coach TeamColors
    Fireballs Garcia yellow, red
    Lightning Wagner orange, white
    Wolves Vega black, gold
    Tornadoes Yamoto blue, yellow
    Sharks Black green, orange
    Piranhas Simmons blue, red
    Bulls Sanchez yellow, green
    Mustangs Miller white, blue
    no team    
    • Create a table named Members using the Table Wizard with the Mailing List sample table.
      Choose the following fields from the sample table:
      • MailingListID (the primary key)
      • First Name
      • Last Name
      • Address
      • City
      • State/Province
      • Postal Code
      • Country/Region
      • Home Phone
      • Work Phone
      • Mobile Phone
      • Email Address
    • Change the field MailingListID to be called MemberID.
    • Edit the property Caption in Table Design view to read Member ID.

    Add new fields:

    • TeamID - Lookup field from Teams table. Select the TeamID and TeamName fields for the list.
      Hide the AutoNumber field.
      Set the number of items to show in the list to 9, which is the number of records in the Teams table.
    • Team Position - Text
       
    • Relationships: Create a relationship between the two tables, joining with the TeamID.
  4. Form:
    • Create a form based on the table Members.
    • Save the form with the name Members Form.
    • Use the form to enter the records below. 

Printable version of table below. (Use Landscape orientation when printing the table.)

Members
First
 Name		 Last
Name		 TeamID Team
Position		 Address City State/
Province		 Postal Code Country/
Region		 Email Address Home Phone Work Phone Mobile Phone
Luis Garcia Fireballs Coach 555 West Oak St. Nashville TN 37889 USA lgarcia@worldtravelinc.net 615-555-4567 615-555-8965 615-555-9992
Sam Wagner Lightning Coach 11235 Mimosa Ave. Hendersonville TN 38556 USA samtheman@jegsworks.com 615-555-7745 615-555-6633 615-555-8242
Jorge Vega Wolves Asst. Coach 55 West Willow Ave. Franklin TN 38777 USA coachvega@jegsworks.com 615-555-4422 615-555-9964 615-555-4876
Al Yamoto Wolves Coach 235 Willow Road Apt. 443 Franklin TN 38777 USA aly@jegsworks.com 615-555-8884 615-555-6645 615-555-2234
Marvin Black Tornadoes Coach 102 South Pine St. Nashville TN 38546 USA blacky@jegsworks.com 615-555-2215   615-555-4421
Jacquiline Simmons Sharks Coach 3310 Westchester Dr. Apt. 2 Antioch TN 38564 USA jackies@jegsworks.com 615-555-7788 615-555-7788 615-555-8456
Jorge Sanchez Piranhas Coach 89 Loma Ave SW Columbia TN 38567 USA sanchez_jorge@jegsworks.com 615-555-2200 615-555-3310 615-555-0045
Frieda Miller Bulls Coach 1123 Maple Dr. Smyrna TN 38665 USA mamamiller@jegsworks.com 615-555-7770 615-555-7770 615-555-2223
Manuel Vega Mustangs Coach 992 Pine Circle Apt. 4 Nashville TN 38223 USA vegamanuel@jegsworks.com 615-555-4422   615-555-8887
Tad Gunter Fireballs Player         USA   615-555-4423    
Alexander Green Fireballs Player         USA   615-555-9996    
Jack Akimbo Fireballs Player         USA   615-555-4447    
Joan Popov Fireballs Player         USA   615-555-0032    
Lynn Landers Fireballs Player         USA   615-555-6665    
George Landers No team Official 55 Lauderdale Ave. Antioch TN 38645 USA glanders@jegsworks.com 615-555-8877 615-555-4466 615-555-2233
Justin Wagner Wolves Player 11235 Mimosa Ave Hendersonville TN 38556 USA   615-555-7745    
Dustin Harvey Wolves Player         USA   615-555-4466    
Crystal Williams Wolves Player         USA   615-555-8811    
Helen Armstrong No team Official 1443 West Fourth St. Apt. 42 Dickson TN 37231 USA harmstrong@jegsworks.com 615-555-6002   615-555-3340
  • Print-out: records 3 through 5 in Form ViewVerify that all fields are showing completely in the print preview of the form.
    Make changes in Design View if necessary, adjusting widths and heights, etc.
  • Change to Form Design View. In the Form properties, change the Default View property from Single Form to Continuous Forms.
  • In Form View, scroll and then select records 3, 4, and 5 by clicking on the record bar at the left of record 3, hold the SHIFT key down and click on the bar at the left of record 5. All three are selected, as shown by the bar turning dark.
  • Icon: Printer Print records 3 through 5 by choosing File | Print... | Print selection. There will be 1 page. (Do not open Print Preview, which will show ALL of the records, not just the selected ones.)
     
  1. Query:
    • Create a new query with the Simple Query Wizard.
      Select the fields FirstName and LastName from the Members table
      and the field TeamName from the Teams table.
      Name the query as QMembers on Teams.
    • In the query's Design View, rearrange the fields if necessary so that they are in the order:
      TeamName      , LastName, FirstName.
      Sort ascending by team and then by LastName and then FirstName. 
       
  2. Teams ReportReport: Create a report using the Report Wizard.
    • Use query you just created as the record source.
    • Group the report on each team.
    • Sort on LastName and then FirstName.
    • Choose the Stepped layout in Portrait orientation.
    • Choose Bold style.
    • Title your report Teams Report.
    • Icon: Printer Print your report. (It should have just one page)
       
  3. RelationshipsRelationships:
    • View the Relationships window. If necessary, enlarge the table lists so that all fields are showing.
    • Icon: Printer Print the Relationships. (1 page)