Final Review

1
Final Review

• Sean Banerjee
• sean.banerjee_at_mail.wvu.edu

1
2

• Access Review
• General overview
• Creating tables
• Selecting proper datatypes
• Selecting a good primary key
• Creating relationships
• Importing data appending to a table and
  creating a new table
• Queries

3

• Access General Overview
• When you start up Access you start with a blank
  sheet. To create a new database, go to File
  New and on the left select Blank Database

4

• Access General Overview
• The initial window contains
• Tables allows you to create the tables, or
  placeholder for your data
• Queries allows you make queries to find items
  from your tables
• Forms allows you to create forms to easily
  enter data
• Reports allows you to create a report to
  display a summary of either a query, table, etc
• Pages dont worry about this
• Macros dont worry about this
• Modules dont worry about this
• Favorites dont worry about this

5

• Access Creating a table
• There are three main ways to create tables
• Design view we will use this more often
• Wizard quicker, but sometimes not the easiest
• By entering information
• Unless otherwise specified always use the design
  view to create tables

6

• Access Selecting Proper DataTypes
• It is important to select the proper datatype
  for example StudentID if it is purely a number
  should be selected as number. Or, if you have an
  entry for CourseSection it should be a text since
  it contains both numbers and text.

7

• Access Selecting a good primary key
• The Primary Key is a unique identifier, for
  example your StudentID is a primary key because
  everyone has a different student id. If a
  primary key cannot be selected from the specified
  data, create your own and make it either a
  autonumber or just a number.
• Composite keys this is when you have two
  primary keys. To create a composite key, select
  the first item hold down SHIFT and then select
  the next item. Then if you hit the Primary Key
  icon it will make both of them a primary key.
• Remember that if you delete an entry with a
  autonumber it will not return. So fret not if
  your entries are not in perfect sequential order.

8

• Access Creating a relationship
• Relationships must be between like items with
  identical datatypes containing the same
  information.
• You cannot relate StudentID with a text datatype
  with StudentID with a number datatype.
  Similarly, StudentID containing a 700 digit
  number cannot be related to StudentID containing
  the social security number.

9

• Access Creating a relationship (Error Types)
• The database engine could not lock the table
  because it is already in use by another person or
  process
• Click OK, close the table that they are referring
  to and recreate the relationship
• Relationship must be on the same number of fields
  with the same data types
• Click OK, check the Data Type of the two fields
  you are trying to relate. Make sure they are
  both of the same type.
• Microsoft Access cant create this relationship
  and enforce referential integrity
• Click OK, the two fields you are trying to relate
  do not have the same information. Go back and
  make sure they both contain the same thing.

10

• Access Importing Data
• You can import data and create a new table. Or
  you can import data and append to an existing
  table.
• To import Data simply go to File Get External
  Data Import. Then select the correct file type
  and import. If you need to append data select
  the option on the bottom and make sure it says
  append data to existing table.

11

• Access Review
• Creating single table queries
• Creating multiple table queries
• Creating totals queries
• Creating conditional and calculated field
  queries
• Creating queries with the wizard

12

• Access Review Single table queries using
  Access3.mdb
• 1) Create a query to show only the students from
  FL
• 2) Create a query to show students who do not
  have financial aid
• 3) Create a query to show only the business majors

13

• Access Review Complex single table queries
  using Access3.mdb
• 4) Create a query to show the business majors who
  have more than 60 credit hours
• 5) Create a query to show the students from CA
  who are communication majors and have more than
  20 credit hours

14

• Access Review Multiple table queries using
  National Bank.mdb
• 6) Create a query to show all customers, their
  state and the amount they borrowed
• 7) Create a query to show all customers who have
  an interest rate of 7
• 8) Create a query to show customers who have a
  loan term of less than 15

15

• Access Review Complex multiple table queries
  using National Bank.mdb
• 9) Create a query to show all customers who
  borrowed more than 100k and have an interest
  rate of atleast 6
• 10) Show all customers who borrowed money between
  February 28 and March 22

16

• Access Review Totals queries using Access3.mdb
• 11) List all majors and their average quality
  points
• 12) List all majors and their total credit hours
• 13) List all majors and count the number of
  students in each major
• 14) List all majors and find the minimum and
  maximum credit hours

17

• Access Review Calculated and conditional
  queries using Access3.mdb
• 15) Create a query to display the GPA
• 16) Create a query that will display the students
  name as lastname, firstname and call the field
  Name
• 17) Create a query to display a field called
  distinction that will show Deans List if the
  student has a GPA greater than 3.5 or Probation
  if the student has a GPA lower than 2.0. If
  neither criteria is met display N/A.

18

• Access Review Creating queries with the wizard
• Same process as creating queries in Design View.
  Add only the tables/fields you need and nothing
  extra.
• Click on Summary if you need to do Avg, Min, Max,
  etc.

19

• Excel
• Remember that the trendline must not only fit the
  data but also make logical sense in the value it
  predicts.

20

• Word/PowerPoint
• You will probably create a word doc or
  presentation to answer the critical thinking qs.

21

• Tomorrow
• Final Exam at 9AM DONT BE LATE
• Bring a student ID or a drivers license
• Exam is 2 hours long