Microsoft Access

Microsoft Access is a database management system (DBMS) that allows users to store, manage, and analyze data. It is part of the Microsoft Office suite and is designed for small to medium-sized databases. Here are the basics:

1. Components of Microsoft Access

• Tables – Store data in rows and columns, similar to Excel, but with more structure.

• Queries – Retrieve, filter, and manipulate data from tables.

• Forms – Provide a user-friendly interface for entering and viewing data.

• Reports – Format and display data in a structured way for printing or sharing.

• Macros & VBA (Visual Basic for Applications) – Automate tasks and add functionality.

2. Creating a Database

• Open Microsoft Access and choose Blank Database or a template.

• Save the database file with a .accdb extension.

• Start adding tables, queries, forms, and reports as needed.

3. Tables: Storing Data

• Each table consists of fields (columns) and records (rows).

• Define field types (e.g., text, number, date/time).

• Set a primary key (unique identifier for records).

• Use relationships to link tables together (one-to-one, one-to-many).

4. Queries: Retrieving Data

• Use Select Queries to filter and sort data.

• Use Action Queries to update, delete, or append records.

• Use SQL (Structured Query Language) for advanced queries.

5. Forms: User Input & Interaction

• Create forms to simplify data entry.

• Add buttons and controls for navigation.

• Use form properties to control input validation.

6. Reports: Presenting Data

• Design reports for printing and sharing.

• Summarize and group data for better readability.

7. Relationships & Data Integrity

• One-to-Many Relationship (e.g., one customer has multiple orders).

• Referential Integrity ensures linked data stays consistent.

8. Automation with Macros & VBA

• Macros – Automate simple tasks like opening forms or running queries.

• VBA – Use code for complex automation and customization.

9. Importing & Exporting Data

• Import from Excel, CSV, or other databases.

• Export data to PDF, Excel, or text files.

Building Relationships in Microsoft Access

Relationships in Access are used to connect tables, ensuring data integrity and reducing redundancy. By creating relationships, you establish links between tables that share related data.

1. Why Use Relationships?

• Avoid Data Duplication – Store data in separate tables and link them logically.

• Ensure Consistency – Changes in one table update related data in another.

• Enhance Data Accuracy – Prevent orphaned records and enforce business rules.

2. Types of Relationships

a) One-to-Many (Most Common)

• A single record in Table A relates to multiple records in Table B.

• Example: A customer (one) can have multiple orders (many).

b) One-to-One

• A single record in Table A relates to only one record in Table B.

• Example: A person can have only one passport.

c) Many-to-Many (Requires a Junction Table)

• Multiple records in Table A relate to multiple records in Table B.

• Example: Students can enroll in multiple courses, and each course has multiple students.

• You need a junction table (e.g., "Enrollment") with foreign keys from both tables.

3. Steps to Create Relationships in Access

Step 1: Open the Relationships Window

1. Open your database in Access.

2. Go to the Database Tools tab.

3. Click on Relationships.

Step 2: Add Tables

1. Click Show Table (if it doesn’t open automatically).

2. Select the tables you want to relate and click Add.

3. Close the Show Table window.

Step 3: Create a Relationship

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

2. The Edit Relationships window will open.

3. Select the relationship type (e.g., One-to-Many).

Step 4: Enforce Referential Integrity

1. Check Enforce Referential Integrity to maintain data consistency.

2. Optional: Select Cascade Update Related Fields (updates related records automatically).

3. Optional: Select Cascade Delete Related Records (deletes related records automatically).

4. Click Create.

Step 5: Save the Relationship

• Click Save in the Relationships window.

4. Viewing & Editing Relationships

• Open Relationships under Database Tools.

• Right-click a relationship line to Edit or Delete it.