Jan's Working with Databases

Exercise Access 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 Move columns in Query Design View Create 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.

Tables

1. Create a new blank database.
2. Save it as ex2-3-citysoccer-Lastname-Firstname.accdb
   
3. Create a table named Teams in Design View and add the records shown in the table below.
   Fields:
   • TeamID - AutoNumber field
   • TeamName - Text or Short Text data type
   • Coach - Text or Short Text data type
   • TeamColors - Text or Short Text data type

   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

4. Create a table using the Table Template/Application Part named Contacts.
   

   Access 2007: Switch to Design View and change the table name to Members.

   Access 2010, 2013, 2016:
   

   • Choose the relationship "One 'Teams' to many 'Contacts'".
   • Choose to show TeamName.
   • Sort Ascending.
   • Name the Lookup field TeamID.
   • Delete the other objects that the template created:
     query ContactsExtended, forms ContactDetails, ContactDS and ContactList, reports ContactAddressBook, ContactList, ContactPhoneBook, and Label.
   • Close the table and change the name of the table to Members.
     If we had kept those objects that you just deleted, you would have to edit them all to use the new source name.
5. In Design View for the table Members, delete the following fields:
   
   • Company
     

     Access 2010, 2013, 2016: You will get a warning that a calculated field depends on this field. That's OK. You are going to delete the calculated fields ContactName and FileAs, also.

   • JobTitle
   • FaxNumber
   • WebPage
   • Access 2010, 2013, 2016: ContactName and FileAs
6. Change the field ID to MemberID.
   Edit the property Caption for MemberID in Table Design view to read Member ID.
7. Change the field BusinessPhone to WorkPhone.
   Change its Caption property to Work Phone.
8. Add new field: Team Position - Text or Short Text data type
9. Access 2007: Add a field TeamID and pick Lookup Wizard... in the Data Type column.

   • Pick the table Teams.
   • Select fields TeamID and TeamName.
   • Sort Ascending on TeamName.
   • Label the Lookup column TeamID.
10. Arrange the fields in Design View in the same order as in the data table below to make it easy to enter that data.
    You can do this by selecting and dragging the rows by the selector at the left end of the row.

    The records you will enter do not have any data for Notes or Attachments.
    

    First  Name	Last Name	TeamID	Team Position	Address	City	State/ Province	Postal Code	Country/ Region	Email Address	Home Phone	Work Phone	Mobile Phone

11. Save and close the table.

Relationships

Using a Lookup field automatically creates a relationship between the two tables.

1. View the Relationships window.
2. If necessary, enlarge the table lists so that all fields are showing.
3. Create and print the Relationships Report. (1 page)

Form

1. Create an AutoForm based on the table Members.
2. Save the form with the name Members Form - Firstname Lastname, using your own first and last names.
3. Use the form to enter the records below. 
   If you did not arrange your form properly above, it will be difficult to enter this data.

   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
   Jacqueline	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

4. Open Print Preview for the form and inspect.
   Identify the problems to fix.
   Make changes in Form Design View, if necessary, by moving controls, adjusting widths and heights, etc. Don't forget to reduce the width of the form itself. Be sure that all fields and their contents are showing completely, that one record fits across just one page, and that records are not split between two pages.
   
5. In Form Design View, in the Property Sheet for the Form, change the Default Form property from Single Form to Continuous Forms.

6. 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.

   Note - The AutoNumber field may start with 2 instead of 1. You want to select records 3, 4, and 5.

7. Print records 3 through 5.
   Hint: Open the Print dialog with CTRL + P and choose Selected Record(s) and then print.
   (Do not open Print Preview, which will show and print ALL of the records, not just the selected ones.)

   There will be 2 pages. You may not see the alternate color background or the control borders, depending your printer. These may print too pale to see.

Query

1. 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.
   • Save the query as QMembers on Teams.
2. In Query Design View:
   • Rearrange the fields so that they are in the order:
     TeamName, LastName, FirstName.
   • Sort Ascending by team and then Ascending by LastName and then Ascending by FirstName. 
     The order that sorting is done depends on the order of the columns in the query.
     

Report

1. Create a report using the Report Wizard.
   • Pick the query you just created as the record source.
   • Group the report on by Team Name only.
     No sorting is needed since the query already sorts.
   • Pick the Stepped layout in Portrait orientation.
   • Access 2007: Select style Access 2007.
   • Save your report with the name Teams Report - Firstname Lastname, using your own first and last names.
   • View the report.
     It's a bit hard to read. Team names are on a separate line above the list of people for that team. It is hard to see where one team stops and the next begins because of the colored lines and the wide spacing between columns.
2. Print your report.
   It should have just one page.
3. In Report Design View, change the following properties:
   • Detail section: Alternate Back Color to match the section's Back Color
     This removes the alternating colors on records.
   • TeamHeader section: Select a background color and use it for both Back Color and Alternate Back Color. Think about how black text will show on top of the background.
     This creates a colored bar across the page with each team name. Remember that what you see on screen may not be quite the color that prints. It may be darker or lighter, depending on the printer.
   • Access 2010, 2013, 2016: TeamName control: Set Back Style to 'Transparent'.
4. Resize and rearrange the controls in the Detail section to make the report easier to read.
5. Print your revised report.
   The illustration is an example. Your report may look different