Tables in MS Access

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.

Some More:

POP- Introduction to Programming Using ‘C’

DS – Data structure Using C

OOP – Object Oriented Programming

Java Programming

DBMS – Database Management System

RDBMS – Relational Database Management System

Join Now: Data Warehousing and Data Mining