MS Access Revision Notes
Relational Database Management System · Exam‑Focused Study Guide
1‑Minute Revision
Quick summary of the most essential MS Access facts for competitive exams.
DeveloperMicrosoft
DBMS TypeRDBMS
Latest Extension.accdb
Old Extension.mdb
Stores DataTable
Retrieves DataQuery
Data EntryForm
PrintingReport
AutomationMacro
ProgrammingModule (VBA)
Unique RecordPrimary Key
Connect TablesForeign Key
Short Text Max255 chars
Long Text Max65,535 chars
BooleanYes/No
Auto IDAutoNumber
Query LanguageSQL
Relationships1:1, 1:M, M:N
Remove RedundancyNormalization
Search FasterIndex
Input RestrictionValidation Rule
FormattingInput Mask
Default Extension.accdb
TQFRMMTable, Query, Form, Report, Macro, Module
Memory Trick: TQFRMM — Table, Query, Form, Report, Macro, Module.
1. Architecture & Main Objects
A single MS Access file (.accdb) contains six main objects.
Remember the mnemonic TQFRMM.
┌─────────────────────────────────────────────────────────┐
│ MS ACCESS DATABASE (.accdb) │
└─────────────────────────┬───────────────────────────┘
│
┌──────────────┬──────────┼──────────┬──────────────┐
│ │ │ │ │
┌───┴───┐ ┌───┴───┐ ┌───┴───┐ ┌───┴───┐ ┌───┴───┐
│ TABLES│ │QUERIES│ │ FORMS │ │REPORTS│ │ MACROS│
│(store)│ │(filter)│ │(input)│ │(print)│ │(auto) │
└───┬───┘ └───────┘ └───────┘ └───────┘ └───┬───┘
│ │
└────────────────── MODULES (VBA code) ──────────────┘
Tables – raw data
Queries – search/filter
Forms – user interface
Reports – print/export
Macros – no‑code automation
Modules – VBA programming
2. Table Structure: Records vs. Fields
Rows = Records (Tuples), Columns = Fields (Attributes).
┌────────────────────────────────────────────────┐
│ TABLE │
│ (e.g., "Student") │
├───────────────┬───────────────┬───────────────┤
│ FIELD │ FIELD │ FIELD │
│ StudentID │ Name │ City │
├───────────────┼───────────────┼───────────────┤
│ 101 │ Rahul │ Jaipur │ ← RECORD
│ 102 │ Neha │ Delhi │ ← RECORD
│ 103 │ Amit │ Agra │ ← RECORD
└───────────────┴───────────────┴───────────────┘
3. Database Keys
Primary Key (PK)
- Uniquely identifies each record.
- No NULL, no duplicates.
- Only one per table.
Foreign Key (FK)
- References PK of another table.
- Maintains referential integrity.
- Can have duplicates and NULL.
┌──────────────┐ ┌──────────────┐
│ STUDENT │ │ ENROLLMENT │
│ (Parent) │ │ (Child) │
├──────────────┤ ├──────────────┤
│ PK StudentID │───1───► │ FK StudentID │
│ Name │ │ FK CourseID │
│ City │ │ Grade │
└──────────────┘ └──────────────┘
4. Relationship Types
1:1
One‑to‑One
Student ↔ Passport
Student ↔ Passport
1:N
One‑to‑Many
Teacher → Students
Teacher → Students
M:N
Many‑to‑Many
Resolved via Junction Table
Resolved via Junction Table
┌─────────┐ ┌──────────┐
│ Student │ ──────── │ Passport │ 1:1
└─────────┘ └──────────┘
┌─────────┐ ┌──────────┐
│ Teacher │ ───┐──── │ Student │ 1:N
└─────────┘ ├──── │ Student │
└──── │ Student │
└──────────┘
┌─────────┐ ┌─────────────┐ ┌──────────┐
│ Student │ ── │ Enroll │ ── │ Course │ M:N
└─────────┘ │ (Junction) │ └──────────┘
└─────────────┘
5. Data Types & Field Properties
| Data Type | Usage | Capacity |
|---|---|---|
| Short Text | Names, codes | 255 chars |
| Long Text | Descriptions | 65,535 chars |
| Number | Calculations | 1–16 bytes |
| Date/Time | Dates, times | 8 bytes |
| Currency | Monetary values | 8 bytes |
| AutoNumber | Auto‑increment ID | 4 bytes |
| Yes/No | Boolean | 1 bit |
| Hyperlink | URLs | 2,048 chars |
| Attachment | Images, documents | up to 2 GB |
| Calculated | Formula | varies |
Field Properties
- Validation Rule: restricts input (e.g.,
>= 18). - Validation Text: error message on violation.
- Input Mask: formats entry (e.g., phone).
- Default Value: auto‑filled on new record.
- Index: speeds up searches.
6. Query Types & SQL Commands
Select (read) and Action (write) queries.
┌───────────────┐
│ QUERIES │
└───────┬───────┘
│
┌───────┴───────┐
│ │
Select Queries Action Queries
(fetch) (modify)
│ │
├── Parameter ├── Update
└── Crosstab ├── Append
├── Delete
└── Make‑Table
SQL Examples
-- 1. Select: students from Jaipur
SELECT * FROM Student
WHERE City = 'Jaipur';
-- 2. Insert: add a new record
INSERT INTO Student (StudentID, Name, City)
VALUES (104, 'Pooja', 'Udaipur');
-- 3. Update: change city
UPDATE Student SET City = 'Jodhpur'
WHERE StudentID = 101;
-- 4. Delete: remove from Agra
DELETE FROM Student WHERE City = 'Agra';
7. Normalization Basics
Eliminate redundancy and prevent anomalies.
UN‑NORMALIZED NORMALIZED
┌──────┬───────┬────────┐ Student Table City Table
│ ID │ Name │ City │ ┌────┬───────┐ ┌────┬────────┐
├──────┼───────┼────────┤ │ ID │ Name │ │ ID │ City │
│ 101 │ Rahul │ Jaipur │ ──► ├────┼───────┤ ├────┼────────┤
│ 102 │ Neha │ Jaipur │ │101 │ Rahul │ │101 │ Jaipur │
│ 103 │ Amit │ Jaipur │ │102 │ Neha │ │102 │ Jaipur │
└──────┴───────┴────────┘ └────┴───────┘ └────┴────────┘
(repeated 'Jaipur') (redundancy removed)
50 Important One‑Liners
Key facts frequently asked in competitive exams.
MS Access is an RDBMS.
MS Access is developed by Microsoft.
Access is part of Microsoft Office.
Table stores data.
Query retrieves data.
Form is used for data entry.
Report is used for printing.
AutoNumber automatically generates numbers.
Short Text stores up to 255 characters.
Long Text stores 65,535 characters.
Validation Rule restricts data entry.
Input Mask formats data entry.
Primary Key cannot contain NULL values.
Foreign Key creates relationships.
Index increases search speed.
Normalization removes redundancy.
SQL stands for Structured Query Language.
Access supports VBA.
Default extension is .accdb.
Older extension is .mdb.
MS Access is part of Microsoft Office suite.
A table is composed of rows (records) and columns (fields).
A record is also called a tuple.
A field is also called an attribute.
Query can be used to update data (Action Query).
Select Query retrieves data without modifying.
Parameter Query asks user for input each time.
Crosstab Query summarizes data in a grid.
Append Query adds records from another table.
Delete Query removes records.
Make‑Table Query creates a new table from data.
Forms can include subforms for related data.
Reports can include grouping and summary functions.
Macros can run a series of actions automatically.
Modules contain VBA code for complex logic.
Relationships enforce referential integrity.
One‑to‑One means each record matches one in another.
One‑to‑Many is the most common relationship type.
Many‑to‑Many is resolved with a junction table.
The Junction Table contains foreign keys from both tables.
Data Types include Number, Currency, Date/Time, Yes/No.
Currency data type prevents rounding errors.
Date/Time stores dates and times as 8 bytes.
Yes/No is stored as 1 bit (True/False).
Input Mask can be used for phone numbers, SSN, etc.
Default Value automatically fills a field with a preset value.
Validation Text displays error message when rule is violated.
Required property forces a field to have a value.
Indexed property can be set to Yes (No Duplicates) or Yes (Duplicates OK).
Compact and Repair Database reduces file size and fixes corruption.
The Ribbon, Navigation Pane, and Status Bar form the Access interface.
9. Practice MCQs (25 Questions)
Select an answer and click Check to see the explanation.