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:
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.
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.
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).
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.
Create forms to simplify data entry.
Add buttons and controls for navigation.
Use form properties to control input validation.
Design reports for printing and sharing.
Summarize and group data for better readability.
One-to-Many Relationship (e.g., one customer has multiple orders).
Referential Integrity ensures linked data stays consistent.
Macros – Automate simple tasks like opening forms or running queries.
VBA – Use code for complex automation and customization.
Import from Excel, CSV, or other databases.
Export data to PDF, Excel, or text files.
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.
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.
A single record in Table A relates to multiple records in Table B.
Example: A customer (one) can have multiple orders (many).
A single record in Table A relates to only one record in Table B.
Example: A person can have only one passport.
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.
Open your database in Access.
Go to the Database Tools tab.
Click on Relationships.
Click Show Table (if it doesn’t open automatically).
Select the tables you want to relate and click Add.
Close the Show Table window.
Drag the primary key field from one table to the foreign key field in another table.
The Edit Relationships window will open.
Select the relationship type (e.g., One-to-Many).
Check Enforce Referential Integrity to maintain data consistency.
Optional: Select Cascade Update Related Fields (updates related records automatically).
Optional: Select Cascade Delete Related Records (deletes related records automatically).
Click Create.
Click Save in the Relationships window.
Open Relationships under Database Tools.
Right-click a relationship line to Edit or Delete it.