In this article Relationships in MS Access we give the information about A relationship in MS Access defines how data in two or more tables are connected using common fields, typically primary and foreign keys.
Relationships in MS Access
- Relationships in MS Access
What is a Relationship?
A relationship in MS Access defines how data in two or more tables are connected using common fields, typically primary and foreign keys.
Types of Relationships:
| Type | Description |
| One-to-One (1:1) | Each record in Table A relates to only one record in Table B. |
| One-to-Many (1:N) | A record in Table A relates to many records in Table B (most common). |
| Many-to-Many (M:N) | Implemented using a junction table between two tables with one-to-many links. |
Steps to Create Relationships:
- Go to Database Tools → Relationships.
- Add the required tables (e.g., Students and Courses).
- Drag the primary key from one table (e.g., CourseID from Courses) to the foreign key in the related table (CourseID in Students).
- In the Edit Relationships dialog:
- Select the relationship type.
- Check “Enforce Referential Integrity” (to prevent orphan records).
- Optionally check cascade options (update/delete related records).
- Click Create.
MS Access will draw a join line showing the relationship.
Example:
- Table 1: Courses → CourseID (Primary Key)
- Table 2: Students → CourseID (Foreign Key)
One-to-Many Relationship: One course can have many students.
Benefits of Relationships:
- Maintains data integrity.
- Avoids data duplication.
- Supports relational queries.
- Enables referential constraints.
-
Macros in MS Access
What is a Macro?
A Macro is a tool in MS Access used to automate tasks such as opening forms, running queries, printing reports, or validating data. It’s a no-code/low-code alternative to VBA (Visual Basic for Applications).
How to Create a Macro:
- Go to Create → Macro.
- In the macro builder, select an action (e.g., OpenForm, RunQuery, MessageBox).
- Set parameters for the action (e.g., Form Name = StudentForm).
- Add more actions as needed.
- Click Save and name the macro (e.g., OpenStudentForm).
Common Macro Actions:
| Action | Purpose |
| OpenForm | Opens a specified form |
| OpenReport | Opens a report for viewing/printing |
| RunQuery | Executes a query (select, update, etc.) |
| MessageBox | Displays a custom message box |
| SetValue | Sets the value of a field or control |
| GoToRecord | Navigates to a specific record in a form |
Types of Macros:
| Macro Type | Use |
| Standalone Macros | Created independently and can be run from buttons, events, or manually. |
| Embedded Macros | Placed directly inside form/report controls (e.g., button click). |
| Data Macros | Triggered automatically by data events (e.g., After Insert, Before Delete). |
Example: Macro to Open a Form
| Action | Parameter |
| OpenForm | Form Name: StudentForm |
| View | Form |
| Filter Name | (leave blank) |
| Where | (leave blank) |
You can link this macro to a button in a form using the On Click property.
Benefits of Macros:
- No programming knowledge required.
- Faster development for repetitive tasks.
- Easy automation of user actions.
- Enhances user interaction in forms and reports.
POP- Introduction to Programming Using ‘C’
OOP – Object Oriented Programming
DBMS – Database Management System
RDBMS – Relational Database Management System
Join Now: Data Warehousing and Data Mining