Relational Databases with Microsoft Access

 
Scheme of Work
System design
Access skills
Right click the link and select Save Link As...
Week 1
 In this exercise you will create a relational database that models the rental of videos. The main principle seen here is that as little data as possible is duplicated in more than one table. All the members are listed in the members table; all videos are listed in a videos table. Loans are recorded in a separate table that brings together videos and members.
  • use seperate tables
  • set data types and other field properties
  • set validation
  • use lookups
  • create relationships between tables
  • data dispay forms
  • use sub forms.

Word Icon

Worksheet 1
Access Icon Example database 1
Excel Icon Member Data
Excel Icon Video Data
Week 2
 The purpose of queries is to process data by filtering or sorting or to combine data held in tables in new ways. Queries can also link data from other queries together. You will modify the system so that you can check that the member is old enough to take out a video and setup a more sophiticated inteface
  • complex queries that relate data from more than one table
  • new data display forms
  • data entry forms
  • use macros
 Word Icon

Worksheet 2
Access Icon Example database 2
Week 3
 You will modify the system so that you can check that videos that have been borrowed can be reurned and generate overdue letters.
  • complex queries
  • Access reports
 Word Icon

Worksheet 3
Access Icon Example database 3
Week 4
 You will modify the system so that you can borrow more than one video in each loan transaction and produce a summary of all the charges associated with that transaction
  • new forms
  • complex queries
  • reports based on queries
 Word Icon

Worksheet 4
Access Icon Example database 4