1. What is MS Access?

Simple definition: Microsoft Access is an application for creating and managing databases. It stores data in tables and lets you design screens (forms), ask questions (queries), and print results (reports).

Think: Word for documents, Excel for calculations, Access for structured large-data storage with relationships.

2. Why use MS Access?

• Easy graphical interface — less coding than server databases.
• Great for small-to-medium systems: shops, schools, clinics.
• Works well with Excel, Word, and external databases (via ODBC).
• Has automation (macros) and programming (VBA) when needed.

3. Main Objects (simple definitions)

Tables

Store raw records (rows) and fields (columns). Example: Students table stores each student as a row.

Queries

Ask questions of the data. Example: "All students with marks > 80". Queries can select, update, delete, append or create tables.

Forms

Friendly screens to enter or view data. Example: a form a teacher uses to enter marks for one student at a time.

Reports

Formatted output for printing or PDF: mark sheets, invoices, lists.

Macros & Modules

Macros automate tasks (no code). Modules use VBA for advanced logic and error handling.

4. Start MS Access and Create a Database

Step-by-step (visual)

1. Open Windows Start menu → Type Access → Open it.
2. Choose: Blank desktop database or pick a Template.
3. Enter a file name (example: SchoolDB.accdb) and click Create.
4. Access opens and creates an empty table or loads the template objects (tables, queries, forms).

Shortcut method

Windows key → type Access → Enter. Inside Access press Ctrl + N to create a new blank database quickly.

5. Create Tables

Always plan your tables first — other objects depend on them.

Common fields in a Student table (example)

Method A — Datasheet View (quick)

1. Create → Table (opens Datasheet View).
2. Click column header to rename (e.g., change ID to StudentID).
3. Click "Click to Add" to add fields and choose data type.
4. Save (Ctrl + S) → give a name: tblStudents.

Method B — Design View (recommended)

1. Create → Table Design.
2. Type field names and choose Data Types explicitly.
3. Select the primary key field → Click Primary Key button.
4. Save and switch to Datasheet View to enter sample rows.

6. Common Data Types (short and clear)

• Short Text — text and short combinations (up to 255 chars). Good for names, addresses.
• Long Text — long notes/descriptions.
• Number — numeric values used in calculations (specify Field Size when needed).
• Date/Time — date and time values.
• Currency — monetary values (keeps formatting & precision).
• AutoNumber — automatic unique number (use for ID).
• Yes/No — boolean fields (checkbox).
• Attachment / OLE — store files (images, docs) inside records.

7. Add, Edit and Delete Data

Add Data

1. Open table → Datasheet View.
2. Click in the first blank row → type values (like Excel).
3. Move away from the row to commit (Access auto-saves when you move).

Edit Data

Click any cell, change value, then press Enter or move to another cell; changes are saved automatically.

Delete Data

1. Select the whole row (left-most selector) → press Delete.
2. Confirm deletion when prompted.

8. Queries — Ask Questions

Query types: Select (view), Action (Update, Append, Delete, Make Table), Parameter, Aggregate.

Create a Select Query — Step-by-step

1. Create → Query Design.
2. Select table(s) and add fields you need (double-click or drag).
3. In the grid add criteria under a field (e.g., >80 under Marks).
4. Run (Click Run ▶) to see results.

Shortcut (quick)

Open table → Home → Advanced → Filter for quick filtering. Or select fields and press Alt + Q to quickly open search box (Access Quick Access).

Example queries

• Top performers: SELECT FirstName,Marks FROM tblStudents WHERE Marks > 80;
• Create table from query: Make-Table Query to produce a new tblTopStudents.

9. Forms & Reports (practical)

Forms

1. Select table/query → Create → Form. Access auto-builds a basic form.
2. Design view: add labels, textboxes, command buttons (e.g., Save, Next, Print).
3. Use the Form to enter data one record at a time — great for non-technical users.

Reports

1. Select table/query → Create → Report.
2. Use Report Design or Layout view to adjust grouping, page headers, and totals.
3. Export → PDF for distribution or print directly.

10. Relationships & Normalization

Relationships connect tables so you avoid duplicate data and keep integrity.

Types

• One-to-One — rare, one record links to one record.
• One-to-Many — common: One Class → Many Students.
• Many-to-Many — use junction table (e.g., Students & Courses via Enrollments).

Create Relationship — Steps

1. Database Tools → Relationships.
2. Drag primary key (e.g., StudentID from tblStudents) to foreign key (StudentID in tblMarks).
3. Check Enforce Referential Integrity → Click Create.

11. Practical Examples & Student Exercises

Example 1 — School Markbook

Tables: tblStudents, tblSubjects, tblMarks (StudentID, SubjectID, Marks). Use forms for entry, queries for top performers, reports for printed mark sheets.

Example 2 — Small Shop

Tables: tblProducts, tblSales, tblSuppliers. Queries show low stock (Quantity < reorder level). Make a report for daily sales.

Exercises

1. Create tblStudents with 6 sample records.
2. Create a query showing students with marks > 60.
3. Create a form for adding students and a report that prints students sorted by last name.

12. Useful Keyboard Shortcuts

Tip: Use Ctrl + S often while designing objects (table design, forms).