In this article Tables in MS Access we give the information about a table is the basic building block of a database that stores data in rows and columns.
Tables, DDL DML and DQL queries using wizards
-
Tables in MS Access
A table is the basic building block of a database that stores data in rows and columns.
- Row (Record): A single complete set of related data.
- Column (Field): A specific category of data (e.g., Name, Age, ID).
Creating a Table Using Wizard:
- Open MS Access → Create a Blank Database.
- Click “Table Design” or “Table” under the Create tab.
- Enter Field Name, Data Type, and Description.
- Set the Primary Key (Right-click a field → Set Primary Key).
- Save the table with a meaningful name (e.g., Students).
- DDL Queries (Data Definition Language)
DDL is used to define or modify the structure of the database.
| Command | Description | Example |
| CREATE | Creates new tables or objects | CREATE TABLE Students (ID INT, Name TEXT); |
| ALTER | Modifies an existing table | ALTER TABLE Students ADD Email TEXT; |
| DROP | Deletes a table or object | DROP TABLE Students; |
Using Wizard:
- MS Access doesn’t provide a wizard for DDL directly.
- But you can:
- Use Design View to define or alter structure (CREATE, ALTER).
- Use SQL View under the Create → Query Design → SQL View to write DDL manually.
-
DML Queries (Data Manipulation Language)
DML is used to manipulate data within tables.
| Command | Description | Example |
| INSERT | Add new records | INSERT INTO Students (ID, Name) VALUES (1, ‘Amit’); |
| UPDATE | Modify existing records | UPDATE Students SET Name = ‘Anita’ WHERE ID = 1; |
| DELETE | Remove records | DELETE FROM Students WHERE ID = 1; |
Using Wizard:
- Create → Query Wizard → Simple Query Wizard (for viewing).
- For Insert/Update/Delete:
- Go to Create → Query Design.
- Add table, switch to SQL View and write DML commands.
- DQL Queries (Data Query Language)
DQL is used to retrieve data from tables.
| Command | Description | Example |
| SELECT | Retrieves records | SELECT * FROM Students; |
| SELECT Name FROM Students WHERE ID = 1; |
Using Query Wizard:
- Create → Query Wizard → Simple Query Wizard
- Select the table and fields you want to retrieve.
- Choose Detail or Summary query.
- Click Finish – it will generate a SELECT query behind the scenes.
Example Use-Case: “Student Table”
Table Name: Students
| Field Name | Data Type |
| ID | Number |
| Name | Text |
| Course | Text |
| Marks | Number |
Sample DML Queries:
INSERT INTO Students (ID, Name, Course, Marks) VALUES (1, ‘Ravi’, ‘BCA’, 78);
UPDATE Students SET Marks = 85 WHERE ID = 1;
DELETE FROM Students WHERE ID = 1;
Sample DQL Queries:
SELECT * FROM Students;
SELECT Name, Marks FROM Students WHERE Course = ‘BCA’;
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