Video Rental Store M.S. Access Module

1
Video Rental Store M.S. Access Module

• CAS 133 Basic Computer Skills/MS Office
• Russ Erdman

2
Video Rental Store
What are some of the Tables in the video store?
Video Store
Database
Inventory
Customers
Employees
Financial
Tables
1. 2. 3.
4.
You may have sub-categories and have to break
them down and create more tables. In Access DB
everything is based on the tables. Without the
tables you cant do anything. Queries are based
on tables reports are based on tables or
queries.
3
Video Rental Store
Relationships of the Tables 1 and 2 Customers
rent videos 1. One customer can rent many videos
Two way relationship 2. One video can
be rented by many customers 1 and 4 1. One
customer pays to rent 2. Customer owes money 3.
Customer overpays (accounts receivable) 1 and
3 1. Employees get a deal and can rent at a
discount or for free may
involve financial table Potential
Relationships 2 and 3 1. Employee could be a
customer 2. Incentive program based on how many
customers (new?) you get to
rent videos
4
Video Rental Store

• Before you start any database design there are a
  couple of rules things to know.
• Purpose of the design in our case to get a
  grade, but ostensibly to
• manage issues in a video store.
• 2. Always design on paper first create a flow
  chart.
• 3. Determine what the fields will be for every
  table - Try to determine what some of the fields
  are for the tables, such as, the Inventory Table.

Three things you have to know about fields to
design DB 3a. Field Name 3b. Field
type 3c. Field Length
5
Video Rental Store
3a. The fields should make sense and be logically
distinct from each other (Last and First
Name should they be split apart or does it
matter?)
3b. Every table has a unique field or Primary Key
(This is a very important field type.
Also, you cant do math on text and you
cant mix text and numbers in a number data
type.)
3c. Field length it is important in Access to
know how long is the field length, that
is, how many characters you can put into that
field. Default length is 50, can go to
64 characters. Why field length? Question How
long does the Title field have to be? A logical
answer might be As long as it needs to be for
the longest title of a video. If default is 50,
then each time a record is input it takes up as
much space in bytes (characters) as has been
allowed (in this case 50) no matter how long the
actual title is up to the 50 characters. Access
database file can become radically large because
of this storage process. You should abbreviate
where possible.
6
Video Rental Store
What are some field possibilities? We will
limit our table to 10 fields Title Category Ac
tors (first two only full names) Director MPAA
Rating Running Time (minutes) Year Format
(DVD) BW/Color (BW) ID Number (Auto number)
What are some Category field suggestions this
will vary depending on what movies we have, but
might include Comedy, Drama, Action, Sci-Fi,
Self Help, Documentary, Family, Musical, Foreign,
Classic, Horror.
7
Video Rental Store
LETS GET STARTED Create Database - Start
Programs Office Access - choose Blank
Database save it and name it Video
Store Create Tables we will create just one
table called Inventory using design view. Design
Area you need to specify a few things in the
design area when you are creating the
table. Validation Rule Specify a rule must
be under the area the rule is to apply to, such
as MPAA Rating. The validation rule wont let
you enter any data that doesnt qualify, so if
you misspell the rule then you have to know how
to misspell the data. You cant move on until
you enter correct data or leave the field data
box empty. Validation Text Write the error
message you want to appear if the wrong data is
entered.
8
Video Rental Store

• Set field size If you try to enter more data
  than the field size allows and you dont have a
  validation rule, you will just run out of room to
  enter data.
• Data entry after creating the tables you then
  can enter your data.
• Data is automatically saved to the DB as you
  enter it and move to the next field box.
• Why save automatically?
• DB is usually used by multiple people across a
  network. DB records save as you go so
  others can immediately access the record.
• As long as you are in entry mode
  (little pencil) no one else can use that
• record and see the updated table
• Also prevents loss of data if system
  crashes.
• Adjust the field width to accommodate your
  entries (You can double-click to resize just like
  in Excel).

9
Video Rental Store
Title 30 (default is 50)
Text Category 11 (Documentary is longest)
Text Actors (first two only full names)
30 Text Director 25
Text MPAA Rating 5 Text Running
Time (minutes) (dont need to specify with
numbers) Number Year (dont need to specify
with numbers) Number Format (DVD) 1
Yes/No BW/Color (BW) 1
Yes/No ID Number (Auto number) Auto
number
Field Name Field Length Field Type
categories have a limited number of characters
for the field or you can tell the length to
use, such as, G, PG, PG-13, R, NR, which are
predefined lengths or limit the number of
choices you can make.
Formats DVD or VHS Only two choices, one or
the other. Make the format one of the two and
then a check means Yes and a null (no
entry) means No. DVD checked VHS not
checked BW checked Color not checked Go
with the choice that is less common so less often
will you have to enter a data (check) mark.
10
Video Rental Store

• Queries and Reports
• Queries
• Based on tables, but they can be based on other
  queries.
• Allows us to ask a question of the database.
• For example, sort alphabetically by title or list
  movies over 2 hours long.
• Queries are live data, if you change the table,
  the change affects the query and vice versa.
  Dont delete data in a query as it will affect
  the table as well. Queries are very powerful.
  Queries are dynamic.
• Dont use the wizard to create, use design view
  at first it can be confusing, but easier as you
  learn it.
• Create in design view then run it to see if you
  got the desired results.
• RUN the query using the run button on the design
  view toolbar this is the best way to run the
  query it avoids confusion.

11
Video Rental Store

• Queries (cont.)
• Ways to add fields to query
• Double click on a field and it will jump to the
  next available field position.
• Drag and drop if drag on top of a field that is
  already there, the field will move over for the
  new one.
• means use all fields
• Dont accidentally add a second copy of the table
  this can mess up the query when you run it.
  Dont use fields twice either.
• In the query design view the PK will appear as
  bold.
• Query by Example grid is where you layout
  fields needed for your query. They need to be in
  the order they should appear when you run the
  query.

12
Video Rental Store

• Reports
• Used to communicate database information at
  meetings, with customers or clients, etc.
• Use Report Wizard
• Easy to use, similar to Chart Wizard in Excel.
• Allows you to create a pretty picture at the end.
• Dont use design mode, it is hard to control.
• You can use either tables or queries or both for
  a report.
• You can choose from more than one query or table
  for your report.
• You only want the fields you need for the report.
  

13
Video Rental Store
STOP!