CSA 213 Database Management

Practical Session (Max points: 50)

As the manager of a womens college basketball team, you have created a database called Basketball-9 that tracks the players, games and player statistics. You have to export a report to a Word document.

Open the database Basketball-9.accdb

In the Relationships window, connect the Games and Stats tables with a one-to-many relationship based on the common GameNo field. Connect the Players and Stats tables with a one-to-many relationship based on the common PlayerNo field. Be sure to enforce referential integrity on both relationships. Save and Close relationship window.

Export the Player Statistics report to a word file with the name PlayerStats.rtf. Save the PlayerStats.rtf document in the location where you store your data files. Open the destination file after the export operation is complete.

Press [Enter] three times to enter three blank lines at the top of the document. The press [Ctrl][Home] to position the insertion point at the top of the document.

Type your name on the first line of the document, enter todays date as the second line, then read the information and write a sentence or two that explains the data that follows. Save and close the PlayerStats.rtf document.

Exit Word. Close the Basketball-9.accdb, then exit Access.

10 points

As the manager of a college womens basketball team, you want to enhance the Basketball-10 database by creating several action queries.

Open Basketball-10.accdb

Create a new query in Query Design View, and select all fields from the Stats table

Add criteria to find all of the records with the GameNo field equal to 1, 2 or 3, then view the datasheet. It should display 26 records.

In Query Design View, change the query to a Make Table query to paste the records into a table called 123Stats in the current database.

Run the query to paste the 26 rows, save the query with the name MakeStatsBackup.

Create another query that includes all the fields from the Stats table.

Add criteria to find all of the statistics for those records with the GameNo field equal to 4 or 5, then view the datasheet. It should display 12 records.

In query design view, change the query to an Append query to append the records to the 123Stats table.

Run the query to append the 12 rows, save it with the name AppendStatsBackup, then close the query.

Open the 123Stats table to confirm that it contains 38( 26 + 12) records now. Rename it as 12345Stats.

Close the Basketball-10.accdb database and exit Access.

20 points

As the manager of a music stores instrument rental program, you created a database to track instrument rentals. Now that several instruments have been rented, you need to create a report listing the rental transactions for each instrument.

Open Music-8.accdb database.

Use the Report Wizard to create a report based on the FirstName and LastName fields in the Students table, the RentalDate field from the Rentals table and the Description and MonthlyFee fields from the Instruments table.

View the data by instruments, do not add any more grouping levels, sort the data in the ascending order by RentalDate, use a Stepped layout and Portrait orientation and title the report Instrument Rentals.

Open the report in Design View, change the first grouping level from SerialNo to Description so that all instruments with the same description are grouped together, and open the Description Footer section.

Add a new text box to the Description Footer section with the expression =Count(LastName). Change the label to Number of Rentals. And position the controls to the right side of report.

Change the Force New Page property of the Description Footer section to After Section.

Add your name as a label to Report header section and use format painter to copy the format from Instrument Rentals label to your name. Align the top edges of both the labels in the Report header.

Save and preview the report as shown below.

Close the Music-8.accdb database and exit Access.