To Homework

IT 240 -- Final Project - Part I

Midwest Sports League Database (continued)

Due date:  Wednesday, June 4, 2008 (to be submitted together with Part II)

Reference:   The Midwest League logo.

That's right, there is still more to do for the Midwest League database. We still don't have a Team Rosters report or a Master Coaching list and then we'll throw in a few extras just so we won't feel too guilty charging the Midwest League a few thousand dollars more.

 

The Team Roster report

The Team Roster report is mainly used to print the rosters, showing all players on each team, with their birth date and telephone number. The teams are listed alphabetically by team nickname, and by last name within each team. Create the report using the report template (not the Report Wizard) in order to maintain a consistent design throughout the application.

  1. Create Team Rosters query (use Teams table and Players table). This query should show four fields: Nickname, Player, Phone Number, and Birth Date. The Player field is a concatenation of LastName with FirstName.
  2. Using the Report Template (copy/paste) start designing the Team Roster report. The Record Source should be your Team Rosters query.
  3. View >> Sorting and Grouping. This allows you to insert a Group Header for each team nickname. Keep the whole group together - this prevents a page break within a team. Sort by Nickname and then by player.
  4. Click and drag the Nickname field from the field list to the header you just created. Delete the attached label. Click and drag the remaining fields to the Detail area. Delete the attached labels because the template already contains formatted labels in the Page Header. You will have to change the text of each label to match the column headings: Nickname, Player, Birth Date, Phone Number. Move, size and align the controls as needed.
  5. Here's a snap shot of the first page of the Team Roster Report.

The Master Coaching List report

The Master Coaching List report is based on a query. You create the query and then the associated report. Again, base the report on the report template that is already in the database.
  1. Create the query in Design view. Add the Teams and Coaches table to the query. Remember all that Join stuff we discussed earlier in the course? You'll want a Right Join for this query (because you want every coach, regardless of whether the coach is assigned to a team or not).
  2. Your query should have four fields: Name, Nickname, Title, PhoneNumber
    Name is a concatenation of LastName and FirstName. Title is computed: it's either Head Coach or Assistant (depending on value of Status. Hint: use the IIF function). The other two come right from the field list. Save the query as Master Coaching List.
  3. Copy and paste the Report Template. Call the new report Master Coaching List. Open in Design view. Open the Properties list of the report. In the Record Source dropdown box, pick the Master Coaching List query. The field list should open automatically. Close the property sheet.
  4. In turn, click and drag each field and drop on Detail area of report. Delete each label. Change all the label names in the Page Header to match the field names. Move, align, resize, refont, etc. to make everything presentable.  Save.
  5. Here's a snap shot of the Master Coaching List Report.

The Report Switchboard

This expanded version of the Report Switchboard will contain 7 buttons: Display Report Template, Team Rosters, Master Coaching List, Relationships Diagram, Total Query - Number of Players Per Team, Unmatched Query - Teams Without a Coach, and Return to Main Menu. The first three items and the last item are done. The fourth you know how to do. That leaves the fifth and sixth items.
  1. The totals query displays the number of players per team. It contains only two fields: the team nickname from the Teams table and the player's last name from the Players table. The Count function is used in the Total row of the player's last name field to display the number of players on each team. Note, however, that you can not run a query directly through the switchboard. Thus, you have to create a macro to open the query, and then run the macro from the switchboard.
  2. Use the Unmatched Query Wizard to create a query that will list all the teams that do not have a coach. Create a macro to open the query, then run the macro from the switchboard.
  3. You're almost home. Now all seven items have been created. Use the Switchboard Manager to modify the existing Report Switchboard to contain all seven buttons.
  4. Here's a snap shot of the Report Switchboard.