Unit 1: MS Access database practice

• Microsoft Access is a desktop relational DBMS in Microsoft 365 that combines the Jet/ACE database engine with a graphical interface; a database is saved as a single .accdb file.

• Relationships: Database Tools → Relationships; drag Student.RollNo to Result.RollNo; tick Enforce Referential Integrity (with cascade update and delete as required).

• Create → Query Design → close the table list → SQL view, type the command, then Run.

sqlCREATE TABLE Course (
  CourseID TEXT(10) PRIMARY KEY,
  Title    TEXT(50) NOT NULL,
  Fee      CURRENCY
);

CREATE TABLE Result (
  RollNo   LONG,
  CourseID TEXT(10),
  Marks    INTEGER,
  CONSTRAINT pk_result PRIMARY KEY (RollNo, CourseID),
  CONSTRAINT fk_course FOREIGN KEY (CourseID) REFERENCES Course (CourseID)
);

ALTER TABLE Course ADD COLUMN Duration INTEGER;
ALTER TABLE Course DROP COLUMN Duration;
CREATE INDEX idx_title ON Course (Title);
DROP TABLE Result;

sqlINSERT INTO Course (CourseID, Title, Fee) VALUES ('BSCIT', 'B.Sc IT', 42000);
INSERT INTO Student (RollNo, Name, Course, Marks) VALUES (101, 'Aman', 'B.Sc IT', 78);

UPDATE Student SET Marks = Marks + 5 WHERE Course = 'B.Sc IT' AND Marks < 40;

DELETE FROM Student WHERE RollNo = 105;

SELECT Name, Marks FROM Student WHERE Marks >= 60 ORDER BY Marks DESC;
SELECT Course, COUNT(*) AS Students, AVG(Marks) AS AvgMarks
FROM Student GROUP BY Course HAVING AVG(Marks) > 50;
SELECT * FROM Student WHERE Name LIKE 'A*';          -- Access wildcard is *
SELECT * FROM Student WHERE DOB BETWEEN #01/01/2006# AND #12/31/2006#;   -- dates in #

• Access has no CREATE VIEW in normal use; a saved select query acts as a view and can be used like a table in forms, reports and other queries.

sql-- saved as qryDistinction
SELECT s.RollNo, s.Name, c.Title, r.Marks
FROM (Student AS s INNER JOIN Result AS r ON s.RollNo = r.RollNo)
     INNER JOIN Course AS c ON r.CourseID = c.CourseID
WHERE r.Marks >= 75;

• Form types: single record, multiple items, split form, and main form with a subform (student with results).

• Report sections: report header, page header, group header, detail, group footer (totals), page footer (page numbers), report footer (grand totals).

• Crystal Reports (SAP) is a business reporting tool that connects to databases (Access, SQL Server, Oracle, MySQL through ODBC) and produces formatted, interactive reports; it is also embedded in many .NET and ERP applications.

• Key elements: sections (report header, page header, group header, details, footers), formula fields, parameter fields, running totals, charts and cross-tabs.

• Create the College database with Student, Course and Result tables, keys and relationships.
• Apply validation rules, input masks, lookup fields and default values.
• Run CREATE, ALTER and DROP commands in SQL view.
• Insert, update and delete records using SQL; write select queries with conditions, sorting and grouping.
• Create parameter, crosstab and join queries and save them as views.
• Design a student data-entry form with command buttons and a subform.
• Design a grouped report of results with averages and page numbers.
• Create a Crystal Report of course-wise results with a chart.