Microsoft Access Tutorial: Comprehensive Guide for Creating Databases, Queries, Forms, and Reports

Microsoft Access: Summary Notes for Revision and Practical Use
1. Introduction to Microsoft Access

Microsoft Access is a powerful database management system (DBMS) used for storing, managing, and manipulating large amounts of data. It allows users to create databases, tables, queries, forms, and reports, all within an integrated environment.

2. Key Components of Microsoft Access

  • Database: A container for data that includes tables, queries, forms, reports, macros, and modules.

  • Table: The basic structure used to store data. It consists of rows (records) and columns (fields).

  • Query: A way to extract, modify, and display data from one or more tables based on specified criteria.

  • Form: Used to enter, edit, and view data in a more user-friendly format.

  • Report: A way to format, summarize, and print data.

  • Macro: Automates repetitive tasks.

  • Module: A set of programming instructions written in VBA (Visual Basic for Applications) to automate complex tasks.

3. Creating a Database

  1. Open Microsoft Access.

  2. Select File > New.

  3. Choose Blank Database.

  4. Enter a File Name and click Create.

4. Working with Tables

Tables are where data is stored in an Access database. Each table consists of rows (records) and columns (fields).

  • Creating a Table:

    1. In the left panel, click Table Design to manually create fields.

    2. Define Field Name, Data Type (e.g., Text, Number, Date/Time), and other properties like Primary Key.

  • Example of a Table:
    A “Customers” table might look like this:

    CustomerID (PK) FirstName LastName Email
    1 John Doe john@example.com
    2 Jane Smith jane@example.com

5. Data Types in Access

  • Text: For alphanumeric data (e.g., name, address).

  • Memo: Long text fields (more than 255 characters).

  • Number: For numeric data (integers, decimals).

  • Currency: For monetary values.

  • Date/Time: For date and time.

  • Yes/No: For binary values (True/False).

  • AutoNumber: Automatically generates a unique number for each record.

6. Primary Key and Relationships

  • Primary Key (PK): Uniquely identifies each record in a table (e.g., CustomerID in the “Customers” table).

  • Foreign Key (FK): A field in one table that links to the primary key in another table.

  • Relationships: Used to link tables together, such as one-to-many (one customer can have many orders).

To create a relationship:

  1. Go to Database Tools > Relationships.

  2. Drag the primary key from one table to the foreign key in another table.

7. Queries

Queries allow users to search, filter, and manipulate data from one or more tables.

  • Creating a Query:

    1. Click on Create > Query Design.

    2. Choose the tables you want to query.

    3. Add the fields you want to view or filter.

    4. Set criteria for the query (e.g., filter customers from a specific city).

  • Example of a Query:
    To find customers from a specific city:

    SELECT FirstName, LastName, City
FROM Customers
WHERE City = 'New York';

  • Types of Queries:

    • Select Query: Retrieves data.

    • Action Queries: Modify data (Insert, Update, Delete).

    • Parameter Query: Prompts the user for input at runtime.

8. Forms

Forms are used to enter, display, and manage data in a more user-friendly interface.

  • Creating a Form:

    1. Click Create > Form Design or Form Wizard.

    2. Select the table or query to base the form on.

    3. Customize the layout and design as needed.

  • Example of a Form:
    A customer data entry form might include fields for FirstName, LastName, Email, etc., with a button to save the form data to the “Customers” table.

9. Reports

Reports are used to format data for printing and presentation.

  • Creating a Report:

    1. Click Create > Report.

    2. Select the data source (table or query).

    3. Customize the report layout using the Design View or Layout View.

  • Example of a Report:
    A sales report summarizing total sales by region for each salesperson.

10. Macros

Macros automate repetitive tasks, such as opening forms, running queries, or sending emails.

  • Creating a Macro:

    1. Go to Create > Macro.

    2. Add actions (e.g., OpenForm, RunQuery, MsgBox).

    3. Save and run the macro to automate tasks.

11. Advanced Features

  • Validation Rules: Ensure data integrity by restricting the type of data entered into a field.

  • Lookup Fields: Allow users to select from a list of values instead of typing data manually.

  • Import/Export Data: Import data from external sources (e.g., Excel) or export data to formats like Excel, CSV, or PDF.

12. Example Database Use Case

Sales Management System:

  1. Tables:

    • Customers: Stores customer information.

    • Orders: Stores order details (linked to Customers by CustomerID).

    • Products: Lists products available for sale.

  2. Queries:

    • View all orders by a specific customer.

    • Calculate total sales for a given period.

  3. Forms:

    • Customer Data Entry Form.

    • Order Entry Form for salespeople.

  4. Reports:

    • Sales Summary Report by product, region, or salesperson.

13. Tips for Efficient Use

  • Use Data Validation: To ensure accurate data entry (e.g., restrict age to be greater than 18).

  • Use Templates: Access provides templates for common use cases (e.g., inventory, contact management).

  • Leverage Relationships: Design your database with proper relationships to avoid redundancy and ensure data integrity.

  • Create Backup: Regularly back up your database to avoid data loss.

14. Keyboard Shortcuts in Access

  • Ctrl + N: Create a new database.

  • Ctrl + S: Save the current object (table, form, query, etc.).

  • Ctrl + C: Copy selected data.

  • Ctrl + V: Paste copied data.

  • Ctrl + P: Print the current report.

Self-Guided Practical Exercises

  1. Create a New Database: Start by creating a database for managing your personal library (tables for Books, Authors, and Borrowers).

  2. Create Relationships: Set up one-to-many relationships between books and authors, and borrowers and books.

  3. Write Queries: Create a query to list all books borrowed by a particular borrower.

  4. Design a Form: Create a form to add new book records.

  5. Create a Report: Design a report to print a list of all borrowed books, including borrower names..

DATABASE NOTESDownload