Course: Microsoft Access (COMP1238) (Year 3 Students)

Databases are the most important method of storing business data. With ever increasing amounts of electronically held data they are a vital tool in the warehousing and analysis of business intelligence. This course consists of six two-hour lab sessions in which students will learn how to design, build and use Microsoft Access databases.

1: Lab Session - Designing Databases

This session introduces the concept of databases in business, why they are used and how they are designed. It explores the structure of a typical database and introduces the various parts of the database that will be studied in detail in later sessions. In the lab sessions students will be using Microsoft Access 2007 but handouts and sample files will be available for Access 2003 (and earlier) versions which are different in some respects.

• Handout: Microsoft Access 2007 - Database Structure (fsAccess2007IntroUG_1.pdf)
  Handout: Microsoft Access 2003 - Database Structure (fsAccessIntroUG_1.pdf)
  These documents detail the essential components of a Microsoft Access database. The text is largely the same for both (some small differences) but the screenshots have been revised for the Access 2007 version.
• Sample File: AccessIntroUG_1.zip
  This zip file contains completed copies (in Access 2007 and Access 2000 format) of the database that will be built during this lab session.
• Sample File: Staff_xls.zip
  A zip file containing a Microsoft Excel workbook to demonstrate importing data into a database and also linking to external files.

2: Lab Session - Designing and Building Tables

This session concentrates on how data should be organised and stored in a database. You will build a number of tables, define the properties of their fields and create relationships between them.

• Handout: Microsoft Access 2007 - Table Relationships (fsAccess2007IntroUG_2.pdf)
  Handout: Microsoft Access 2003 - Table Relationships (fsAccessIntroUG_2.pdf)
  These documents describe the purpose of relationships in a Microsoft Access relational database, shows how to set them up and illustrates how they are useful when creating forms and reports.
• Sample File: AccessIntroUG_2.zip
  This zip file contains copies (in Access 2007 and Access 2000 format) of the database illustrated in the above handouts.

3: Lab Session - Designing and Building Forms

In addition to covering basic form design, both manually and with the aid of wizards, in this session you will find out how to build a form which includes two subforms (essential for the coursework exercise) and how to create that most useful of form controls, the combo box.

• Handout: Microsoft Access 2007 - Forms: Combo Boxes (fsAccess2007IntroUG_3.pdf)
  Comprehensive notes describing the combo box techniques used in the class. The notes apply equally to all Access versions so there is only one version of the notes.
• Sample File: AccessIntroUG_3.zip
  This zip file contains copies of the database (in Access 2007 and Access 2000 format) that is used as a starting point for this lab session.

4: Lab Session - More on Forms and Introducing Queries

This session completes the work on forms and shows you how to display calculations on a form. It continues with an introduction to queries focussing organising and filtering data with Select queries and using queries to perform calculations.

• Handout: Microsoft Access 2007 - Query Criteria Reference (fsAccess2007IntroUG_4.pdf)
  A comprehensive reference of the criteria expressions commonly used in Access queries.
• Handout: Microsoft Access 2007 - Calculations (fsAccess2007IntroUG_5.pdf)
  Illustrates how to perform calculations in Access using Forms and Queries.

5: Lab Session - Building Powerful Queries

Building on earlier sessions in which simple Select queries were explored, this session introduces several useful and easy techniques for adding power and flexibility to your queries. In addition to revising basic query practice it covers Parameter Queries, the query Totals Tool and Crosstab Queries.

• Handout: Microsoft Access 2007 - Improving Your Queries (fsAccess2007IntroUG_7.pdf)
  Notes covering the basic techniques used in the class.
• Sample File: AccessIntroUG_4.zip
  This zip file contains copies (in Access 2007 and Access 2003 format) of a database that will be used in the class to demonstrate the various query techniques.

If you want to learn more about these techniques you can download more comprehensive notes and accompanying sample files see the Access Handouts section on my Free handouts page at www.fontstuff.com/ebooks/free/index.htm#access

6: Lab Session - Introducing Reports

This session introduces Access database reports. It concentrates on building reports with the help of the Report Wizard and modifying their design with the Report Design tool. We will use the same database file as in the last session. There is no handout for this session.

Coursework Assignment

This document details the assignment that students are required to carry out in their own time for the assessment for this course.

NOTE: There is an error in the submission date quoted in the following document. The last date for submission should read 27th APRIL 2010 and not 27th March 2010 as stated in the document. Please accept my profuse and humble apologies for this oversight.

• Handout: DFA_Access_Assignment_2010.pdf

PowerPoint Slides

This PowerPoint Presentation is a copy of the one used during the course.

• PowerPoint 2007 Show: Microsoft Access Databases.ppsx

[Updated: 15/03/2010]