Access Movie Database Part 1

Access Movie Database Part 1.

Part 1 should be an individual effort. That is, you have your own file but, you could work side-by-side with your team members. No copying files. You'll be creating your own database with 5 tables that you will eventually join to a friends. One of the tables will be imported from an Excel spreadsheet that you can download from the assignment link in Blackboard.

Part 1's data is used in the group project, so make sure you do it.

1. Open Microsoft Access. (If your screen doesn't look like this, try going to the Office Button (upper left) and creating a new blank database. Browse through and select an area where your project will be stored. Select a filename student_yourlastname. Click Create to start your project.

2. The Tool Bar

Home

This ribbon is what you might expect. Formatting text like MS Word, simple totals, sorts and find and replace.

Create

In Access, you create and manipulate data by using Tables, Forms, Queries and Reports. Tables are what hold your data and key fields in the tables are how the tables relate to each other. Forms are a nice way for you to enter the data without making a mistake. Queries allow you to design questions for the database. Report let you sort and manipulate the data based upon your queries.

External Data

This ribbon allows you to import data from other sources. You will use this during the group project.

Database Tools, Add-Ins, Acrobat This project does not require you to use these.

Data Sheet

This ribbon lets you set up your tables and fields.

For this individual tutorial, each team member will be creating 5 tables with a minimum of 25 records each. The Movie table is an exception, that table will be imported. The tables and their data are shown below.

Table Name Data

CUSTOMER CustomerID (primary key), Lastname, Firstname, Address, City, State, Zipcode, Phone

MOVIE ISBN (primary key), Title

RENTS CustomerID , ISBN, Duedate

ACTOR ActorID (primary key), ActorLast, ActorFirst

HAS ISBN, ActorID

From this, you should be able to see how you might link all these tables to find information about who has rented what movie, what actors are in different movies or what customers have movies that are overdue.

Creating the Tables

3. Start by entering the CUSTOMER table. Click the Datasheet option on the ribbon if you are not already there. It should look like the picture below.

Double click where it says ID under Table1 and rename that filed CustomerID. Double click where it says Add New Field, and add the text Lastname. You can tab through the top of the datasheet and add the field names for the remaining fields.

Click Ctrl+S to save you file. Access will ask you to name the table. Name this table CUSTOMER.

Click the button on the upper left that says 'View' and switch to 'Design View'. This screen will allow you to define the attributes of your fields. For example, CUSTOMERID will be a unique identifier for each specific customer and Lastname will be something you make up.

Click where DataType says AutoNumber and change it to Text. Then in the lower General tab, change the text Field Size to 5. Under the required option, select Yes.

Define your field attributes as follows:

* CustomerID (primary key) Change to 5 text characters. It should have a tiny key icon to the left of the field name, indicating it is a primary key.

* Lastname 20 text characters. Required.

* Firstname 15 text characters. Required.

* Address 40 text characters. Required.

* City 20 text characters. Required.

* State 2 text characters.