Which Access object stores the actual data?

Table. Only tables hold data. The other objects read from tables and would be empty without them.

Deleting a query from an Access database:

deletes no data at all. A query stores only the question. Removing it removes the saved question and leaves every record untouched.

The property that rejects a mark above 100 is:

Validation Rule. The Validation Rule tests each entered value against a condition. Format only changes how a stored value is displayed.

Which property prevents a field being left empty?

Required. Required = Yes is the presence check. A Default Value would fill something in rather than refusing a blank.

An AutoNumber field is a good primary key because it is:

unique, never empty and never changing. Those are exactly the three requirements of a primary key. Being meaningless is what guarantees the third.

Enforcing referential integrity stops you:

entering a foreign key that matches no primary key. It also blocks deleting a record while others still refer to it. Both prevent orphaned records.

Cascade Delete on a CUSTOMER–INVOICE relationship would:

delete all a customer's invoices when the customer is deleted. That is usually the wrong outcome — invoices are financial records that must be retained even after a customer leaves.

A clinic outgrowing Access would most likely be limited by:

the number of simultaneous users. Access is a file-based system designed for a handful of concurrent users. Growth in simultaneous access is the usual reason to move to a client-server DBMS.

Introduction to Microsoft Access — Computer Science Notes, Worked Examples and Quiz

Introduction to Microsoft Access

The objects a database file contains, and what each is for

This topic appears in:

One file, several kinds of object

Microsoft Access is a relational database management system. Unlike most, it keeps everything in a single file — the data, the forms, the queries and the reports — which makes it easy to copy and easy to outgrow.

That file contains four kinds of object, and the exam expects the purpose of each. They are not alternatives; a working database uses all four, each doing the job the others cannot.

Object	Purpose	Holds data?
Table	stores the data, in rows and columns	yes — the only one that does
Query	selects, filters, sorts and calculates	no — it re-reads the tables each time
Form	a screen for entering and viewing records	no
Report	formatted output designed for printing	no
Macro	automates a sequence of actions	no

Only tables store data

A query holds no data of its own — it stores the question, and answers it afresh from the tables each time it is opened. That is why editing a record through a form updates every query and report that uses it, and why deleting a query never deletes any data. Students routinely write that a query "stores the selected records", which is exactly wrong.

Building a table

A table is defined in Design View, where each field is given a name, a data type and a set of properties. Datasheet View then shows the rows and is where data is actually typed — but it is the design that determines what may be typed at all.

The field properties are where most of the marks are, because they are how integrity rules from the previous chapter are actually enforced.

Property	Effect
Field Size	limits the length of text or the range of a number
Format	how the value is displayed, without changing what is stored
Input Mask	forces a pattern as the user types, e.g. a CNIC layout
Validation Rule	rejects values failing a condition, e.g. `>=0 And <=100`
Validation Text	the message shown when the rule rejects a value
Required	the field may not be left empty
Default Value	a value filled in automatically for new records
Indexed	speeds up searching and can enforce uniqueness

Worked example

A STUDENT table needs a Marks field accepting whole numbers from 0 to 100, which must not be left blank. State the design settings.

Data Type: Number, Field Size Integer.Arithmetic will be done on marks, and no fractional mark is awarded.
Validation Rule: >=0 And <=100.This is the range check, enforced by the DBMS rather than by whoever wrote the data entry form.
Validation Text: "Marks must be between 0 and 100".Without it the user sees a generic error and does not know what to do.
Required: Yes.This is the presence check. A blank mark is different from a mark of zero, and both must be impossible here.
Do not set this field as the primary key.Marks are neither unique nor stable — the two things a primary key must be.

Number/Integer, validation rule >=0 And <=100 with a message, and Required set to Yes.

Setting the primary key and the relationships

A table is given a primary key in Design View, and Access will create an AutoNumber field if you do not choose one — a meaningless, automatically incrementing integer, which is usually exactly what a good primary key should be.

Relationships are then defined in the Relationships window by dragging a primary key onto the matching foreign key. Access shows the cardinality it has inferred, and offers to enforce referential integrity. Ticking that box is what stops a record being created with a foreign key matching nothing, and stops a parent record being deleted while children still refer to it.

The Relationships window is this diagram, drawn by dragging. Access infers the cardinality from the keys — one to many appears automatically when the field is a primary key on one side and an ordinary field on the other.

Cascade update and cascade delete

With referential integrity enforced, two further options appear. Cascade Update changes the foreign keys automatically if a primary key changes — rarely needed, since a good primary key never changes. Cascade Delete removes all related child records when a parent is deleted, which is right for a customer's order lines and dangerous for a customer's invoices. Consider what should happen before ticking it.

When Access is the right tool, and when it is not

Access suits a small organisation with a handful of simultaneous users and a database of moderate size. Its advantages are real: forms and reports can be built without programming, everything lives in one file, and it integrates with the rest of the Office suite.

Its limits are equally real. It is designed for a small number of concurrent users and becomes unreliable with many. The single file has a size ceiling. It runs on Windows, not on a phone or a web server. A system that grows beyond those limits moves to a client-server database such as MySQL, SQL Server or PostgreSQL — often keeping the same table design, which is the reward for having designed it properly rather than in the tool.

Before you leave this chapter

Tables, queries, forms and reports — only tables store data.
A query stores the question and re-answers it from the tables each time.
Design View sets field types and properties; Datasheet View shows the rows.
Validation Rule plus Validation Text is how a range check is actually enforced.
Enforcing referential integrity is a tickbox; cascade delete needs thinking about first.

Practice questions

6 questions · 20 marks · full working on every one

Try each one on paper first, then open the working. The marks are shown where they are actually awarded, because that is where they are actually lost.

Short questions

3 · 6 marks

Two marks each, in the style of the short-question section of the paper. Answer in two or three lines.

SQ1[2 marks]

Name the four main objects in a Microsoft Access database and state which stores the data.

Model answer

Tables, queries, forms and reports. Only tables store data; queries, forms and reports all read from the tables and hold no data of their own.

Examiner tip. The second half is the mark that separates a list from an understanding. State it explicitly.

SQ2[2 marks]

What is the difference between Design View and Datasheet View?

Model answer

Design View defines the structure — field names, data types and properties such as validation rules. Datasheet View displays the records in rows and columns and is where data is entered and edited.

Examiner tip. Structure against content is the distinction. The design decides what the datasheet will accept.

SQ3[2 marks]

What is an AutoNumber field, and why does it make a good primary key?

Model answer

An AutoNumber is an integer Access generates automatically, increasing for each new record. It makes a good primary key because it is guaranteed unique, is never empty, and — being meaningless — has no reason ever to change.

Examiner tip. Meaninglessness is a virtue here, which is counter-intuitive and therefore worth stating explicitly.

Solved numericals

2 · 8 marks

Full working, one step per line, with the marks shown where they are awarded.

N1[4 marks]

A field stores a student's age, which must be between 11 and 19 and cannot be blank. State four design settings.

Full working

Data Type: Number, with Field Size Integerage is counted and compared[1]
Validation Rule: >=11 And <=19the boundary values are included[1]
Validation Text: a clear message such as "Age must be between 11 and 19"without it the user sees a generic error[1]
Required: Yes, so the field cannot be left emptythe presence check[1]

Number/Integer, validation rule, validation text, and Required = Yes.

Examiner tip. Use >= and <= rather than > and <. A student aged exactly 11 must be accepted, and the strict operators would reject them.

N2[4 marks]

Explain what enforcing referential integrity does in Access, and describe the effect of Cascade Delete.

Full working

It prevents a record being added whose foreign key does not match an existing primary keyno enrolment in a class that does not exist[1]
And prevents a record being deleted while other records still refer to it, avoiding orphaned records[1]
Cascade Delete instead allows the deletion and automatically removes every related child record[1]
It must be used carefully — deleting a customer would silently delete their entire order history, which may be needed for accountsthe caution is the mark[1]

It blocks unmatched foreign keys and unsafe deletions; Cascade Delete removes the children instead, which is often not what is wanted.

Examiner tip. Cascade Delete is correct for data that has no meaning without its parent, such as order lines, and wrong for anything that must be retained, such as financial records.

Long questions

1 · 6 marks

Theory and numerical together, as they appear in the long-question section.

LQ1[6 marks]

A small clinic is choosing between Microsoft Access and a spreadsheet for its patient records.

Give two advantages of Access over a spreadsheet.
Give two limitations of Access the clinic should be aware of.
State one situation in which the clinic would need to move to a different DBMS.

Mark scheme

Access enforces data types, validation rules and referential integrity, so invalid or inconsistent data is rejected at entry — a spreadsheet accepts anything in any cell[1]
Related data is stored once across linked tables rather than duplicated, and forms and reports can be built for staff who never see the raw data[1]
Limitation: it supports only a small number of simultaneous users reliably[1]
Limitation: the single file has a size ceiling, and it runs only on Windows — not on a phone or a web server[1]
It would need to move if the number of concurrent users grew substantially, or if access were needed over the web or from mobile devices[1]
A client-server DBMS such as MySQL or SQL Server would be used, and the existing table design would largely transferthe design survives the move[1]

(a) enforced integrity and linked tables (b) few concurrent users, size and platform limits (c) growth in users or a need for web access

Examiner tip. The final point is worth making: a properly normalised design moves between database products almost unchanged, which is the practical payoff of designing on paper first.