Project 10 : CD Recordings Database

Using the Access Database

Directions by Heather Lloyd, modified by Ryan Tierney

Creating the CD Rom Database

Run Microsoft Access by the following:

Go to Start, Messiah Programs, Office Suites, Microsoft Office 2000, Microsoft Access 2000.

Select a new blank database

Select Blank Database and Click on OK.






Next name and save the database.

Choose a directory to save your database (The picture shows what it would look like if you chose the T:\ drive [Don't use T: but choose your K: drive]. Choose a name like "records", and click on Create to save the file.



The main database window will be displayed



Adding Fields to the Database

Create the Fields of the Recordings database using the Wizard

Make sure the Tables button is selected and double click on the Create table by using wizard option.



Select the database template for Recordings

1. Click on the circle next to Personal to select personal templates.
   
2. Scroll down the Sample Tables until you find Recordings.
   
3. Click on the >> button to move all the Sample Fields to Fields in my New Table.
   
4. The resulting window should look like the graphic below.
5. Use the < button to remove several unnecessary fields:
   
   1. NumberofTracks
   2. DatePurchased
   3. PurchasePrice
6. Complete the process by clicking on Next button.



Complete the Field definitions with the next window.

1. When the next window appears choose a name for your table and click on the circle next to "Yes, set primary key for me." [ The primary key is an index for ordering the database - alphabetizing by last name.]
2. Click on the Next button.
3. In the next window, click on the circle next to "Enter data directly into the table."
   
4. Click on the Finish button.



View the database in table form.

This is your database in a table in table or spread sheet form.
You could use this form for data entry, but we are going to make a form for data entry.



But before you can do that, you need to change the types of the RecordingArtistID and the MusicCategoryID fields.
Currently they allow only numeric values. We need to change them to allow text.


1. Right click on the gray area of the table and choose Table Design to open the table in design mode.
   This screen allows you to change the type of each field.
   
2. Click the Number in the RecordingArtistID Row, you will see a new pull down menu.
   
3. Next, select the Text field.

   

4. Repeat the process to change the number in MusicCategoryID to Text also.
   (Note I edited the RecordingArtistID to be just RecordingArtist.)
   
5. When the editing is completed, click on the top right button {X} to close this window.
   
6. Answer YES to the question, "Do you want to save changes to the design of table 'XXX'?
   You are now ready to make a form for data entry. Minimize the table display to access the main database window.

Create an Input Form for the Data Base

Move to the Form Options

Click on the Forms button of the main window.
Then double click on Create form by using wizard to display the Form Wizard.



Use the Form Wizard to Create a Form

1. Click on the >> button to move all fields into Selected Fields, and click on Next.

   

2. Choose a layout option. Then click on Next.

   

3. Choose a style and click on Next button.

   

4. Name your form [ RecordingInput]

   Click on the circle next to "Open the form to view or enter information."
   Click on Finish.

   

Enter Data into the Data Base Using the Input Form

1. The form should be displayed
   Fill in the fields of the form. Use the tab key or the enter key to move between feilds.
2. After the information has been entered into the fields, it is necessary to save this record and move to the next record.
   To enter the data for this record into the data base
   Click on the > {small solid triangle} button at the bottom of the screen.]
   The record will be saved and a blank form will appear on the screen.

3. Repeat this process to enter the data for 10 to 20 recordings.
   
4. Here is an example.

   

5. Close the Form

Creating Reports

General Procedure for reports

1. Start from the Main window shown below.

   

2. Click the Reports button, and then double click on Create report by using wizard. This opens the Report Wizard.

3. To put all of the fields in the report, use >> to move all the fields to Selected Fields. Then click on the Next button.

   

4. When the next box appears, click on Next.

   

5. Choose one of the different fields to sort your records in each of the four boxes. Then click on Next.

   

6. Choose a lay out option for your report and click on Next.

   

7. Choose the style you want and then click on Next.

   

8. Title your report and click on the circle next to Preview the report.
   Then click on Finish.

   

9. Your report should appear. Use the magnifying glass to view your report in more detail. Print out a copy of the report.

   

Other Reports For This Project

1. Create another report that orders the database by the year released.

2. Create a report that prints out only the titles, artist, and the cost in table form ordered alphabetically by title.

Create a Query

Move to the Query Options

Click on the Queries button of the main window.
Then double click on Create query by using wizard to display the Query Wizard.



Use the Query Wizard to Create a Query

1. Click on the >> button to move all fields into Selected Fields, and click on Next.

   

2. Make sure "Detail (shows every field of every record)" is selected. Then click on Next.

   

3. Name your query [ Recordings Query]

   Click on the circle next to "Open the query to view information."
   Click on Finish.

   

Use Your Query to Find Entries of a Certain Genre

Your query should be displayed.
Currently it is a list of all your recordings. We want only those of one genre.
1. Right click on the gray area below your query and select "Filter By Form."

   

2. Select the "Music Category" field, click on the arrow, and choose on one of the genres of your recordings.

   

3. Right click on the gray area below the form and select "Apply Filter/Sort."

   

4. You now have a list of only your recordings of that genre.

   

Use the Filtered Query to Create a Report

1. Go back to the main window, but DO NOT close the filtered query! (Closing the window unfilters it.)
   Click on the "Reports" button and double click on Create report by using wizard.
2. Click on the arrow under "Tables/Queries" and select your query.
   Click on the >> button to move all fields into Selected Fields, and click on Next.

   

3. Continue using the same procedure outlined above to create the report of the selected recordings.

   

An Alternate Method to Create a Query

Make several reports using queries on your database.

           Return to CSC 171 Syllabus

This page is Copyright by J. Barry DeRoos. All Rights Reserved.
This page may be distributed and used freely, provided that the copyright notices remain intact.
Last Modified: Jan 10, 2002.