In this article Join Tables in MS Access we give the information about A Join is used to combine rows from two or more tables based on a related column between them—usually a foreign key in one table that references the primary key in another.
Join Tables in MS Access
Joining Tables and Queries Using Wizard in MS Access
- What is a Join in MS Access?
A Join is used to combine rows from two or more tables based on a related column between them—usually a foreign key in one table that references the primary key in another.
Types of Joins in MS Access:
| Join Type | Description |
| Inner Join | Shows only matching records from both tables. |
| Left Join | Shows all records from the left table and matching records from the right. |
| Right Join | Shows all records from the right table and matching records from the left. |
| Full Outer Join | Not directly supported in MS Access; must use UNION manually for both sides. |
- How to Join Tables Using Query Wizard
Let’s consider two tables:
- Students (StudentID, StudentName, CourseID)
- Courses (CourseID, CourseName)
Steps to Join Tables Using Wizard:
Step 1: Create Both Tables
- Create Students and Courses tables.
- Set CourseID as a primary key in Courses.
- In Students, CourseID should be a foreign key.
2: Set Relationship (Optional but Recommended)
- Go to Database Tools → Relationships
- Add both tables and drag CourseID from Courses to Students.
- Enforce referential integrity if required.
3: Use Query Wizard
- Go to Create → Query Wizard → Simple Query Wizard.
- Choose fields from both Students and Courses:
- From Students: StudentName
- From Courses: CourseName
- Click Next, then choose Detail Query.
- Name the query, e.g., Student_Course_Join, and click Finish.
MS Access automatically creates an Inner Join query between the two tables using CourseID.
- View or Modify the Join Type (Optional)
- After the wizard runs, switch to Design View of the query.
- Double-click the join line between CourseID fields.
- Choose:
- Option 1: Inner Join (default)
- Option 2: Left Join (all from Students)
- Option 3: Right Join (all from Courses)
- Example Output (Inner Join)
| StudentName | CourseName |
| Ravi | BCA |
| Anjali | BBA |
- SQL Behind the Wizard (Inner Join Example)
SELECT Students.StudentName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;
Notes:
- You can also manually create join queries using Query Design View for more control.
- To perform calculated joins or filter data, add criteria in the query grid.
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