Database

Field properties (table Design View)

Property	What it does
Field Size	maximum characters for Short Text (default 255) or the number type for numbers
Required	Yes = the field cannot be left empty
Input Mask	forces the pattern of typed data using mask characters (see Annexure A)
Validation Rule	an expression the value must satisfy, e.g. >=5000 And <=50000
Validation Text	the message shown when the rule is broken. It must be meaningful
Lookup (combo box)	choose a value from a list, or from another table
Calculated data type	a field whose value is computed from other fields in the same record

These properties all exist for the same underlying reason: a database is only as useful as the data inside it is trustworthy, and Access enforces that trust at the moment data is typed in, rather than hoping every user types correctly. Each property polices a different kind of mistake — Field Size stops text being needlessly long or a number field overflowing; Required stops a record being saved with a genuinely missing value; Input Mask stops a value being typed in the wrong shape (letters where digits belong, or missing punctuation) before Access even evaluates what the value means; Validation Rule stops a value that's the right shape but outside a sensible range; and Lookup stops a value being typed as free text at all, replacing it with a controlled, consistent list of options instead. Validation Text matters as much as the rule itself because a rule with no explanatory message just rejects the user's entry and leaves them guessing why — "meaningful" means the message actually states the rule in plain language (e.g. "Grant amount must be between R5 000 and R50 000"), not a generic "Invalid entry."

A Calculated field is different in kind from the others: instead of restricting what can be typed, it computes its own value from other fields in the same record, using an expression you write once in Design View. Because it's a formula rather than stored typed data, it automatically stays correct if the fields it depends on ever change — the same "single source of truth" idea that makes Word styles and Excel SUBTOTAL powerful.

Input mask characters you need

• L = letter, required. ? = letter, optional.
• 0 = digit, required. 9 and # = digit, optional.
• > = convert following characters to upper case. < = lower case.
• \ = show the next character literally. A dash - is a separator symbol that depends on regional settings, so write \- to force a real dash.

An input mask is a per-character template: each position in the typed value must match one mask character, and the mask character tells Access both what kind of character is allowed there and whether it's compulsory. That's the whole logic behind the required/optional pairs above — L and 0 must be filled in before the record can be saved at that position, while ?, 9 and # leave that position blank-able. Getting "required vs optional" backwards is the single most common input-mask mistake: using 9 instead of 0 for a code that must always have four digits would accept a code with only two, silently defeating the whole point of the mask.

The \ escape character exists because a handful of characters — most importantly -, ., ,, :, / — are separator placeholders in Access's mask syntax: Access actually replaces them with whatever the matching separator is under the computer's regional settings (date separators, decimal points, and so on can differ between countries), rather than guaranteeing they're the literal character you typed. Writing \- forces Access to treat that dash as a literal, unconditional dash on every machine, regardless of regional settings — which is exactly why a mask like >LLL\-0000 is written with the backslash rather than a bare -.

Queries

• Criteria on the same row = AND. Criteria on different rows (the "or:" rows) = OR.
• Comparison for numbers: >2021 means strictly after 2021 (2022 onwards); >=2021 includes 2021.
• Same field with two options on one row: "FinTech" Or "EdTech" or In("FinTech","EdTech").
• Totals row (Σ button): Group By for the category, Sum/Count/Avg for the values.
• Calculated field in a query: type Name: expression in a blank Field cell, e.g. Eligible: IIf([PitchScore]>=75,"Yes",""). IIf(test, true, false) is the Access version of IF.

The query design grid's row-vs-column logic is the one rule that unlocks almost every multi-condition query: Access evaluates every criterion on the same row together, as an AND — a record only appears if it satisfies all of them simultaneously — while separate rows are evaluated independently and combined with OR, so a record appears if it matches any one of the rows. This is why a query for "female-led start-ups in FinTech or EdTech, founded after 2021" is a genuine trap if built carelessly: splitting FinTech and EdTech across two separate criteria rows is correct for the sector OR, but the gender and year criteria must then be repeated identically on both rows — otherwise the second row has no gender or year restriction at all, and the OR accidentally widens to "female FinTech after 2021, OR any EdTech of any age." Writing the sector condition as In("FinTech","EdTech") on a single row sidesteps the whole problem, because the OR happens inside one field's criterion rather than across the grid.

The Totals row turns an ordinary query into a grouped summary: Group By on a field collapses every record sharing that value into a single output row (one row per township, one row per sector…), and any other field set to Sum, Count, Avg and so on then calculates across just the records inside that group. A calculated field (Name: expression) is the query-level equivalent of a calculated table field — IIf(test, true_result, false_result) is simply Access's name for the same three-part decision that Excel calls IF, just with an extra trailing "I" for "Immediate If."

Forms

• Form background: click the form selector (top-left square), Property Sheet → Format → Picture.
• Form footer holds items shown at the bottom. Add a Label control there for static text such as your name.
• Consistent formatting: Home → Format Painter copies formatting from one control to another. You can check font, size, colour and border in the Property Sheet.

A form in Design View is a stack of named sections — Form Header, Detail, Form Footer (and, for grouped forms, group headers/footers too) — and every control you place belongs to exactly one section, which determines where and how often it appears: the Detail section repeats once per record, while the Form Header and Form Footer each print or display exactly once, at the top or bottom of the whole form. That's precisely why a label meant to show your name once (not once per record) must specifically go in the Form Footer rather than the Detail section — placing it in Detail would repeat your name alongside every single record. The form selector (the square where the rulers meet) is the only way to select the form itself as an object, rather than a control sitting on it, which matters because the background Picture property belongs to the form, not to any individual control.

Format Painter copies an entire bundle of formatting properties (font, size, colour, borders and more) from one control to another in a single click, which is faster and more reliable than matching each property by eye — but because a label and a text box are two different control types, each with its own formatting, making one control's label and its text box consistent with the others always takes two separate Format Painter clicks: one from a correct label to the mismatched label, and one from a correct text box to the mismatched text box.

Reports

Reports group data with the Group, Sort and Total pane (Design → Group & Sort). Each group level can have a header and footer section. Put summary text boxes such as =Count(*) in the footer so the count is per group.

A report's grouping works on the same section logic as a form, extended with group header/footer sections that appear once per group rather than once for the whole report. An expression like =Count(*) placed in the Detail section would evaluate once per record and mean very little; the identical expression placed in a group footer resets and recalculates for every new group, which is what turns it into "count of mentors in this sector" rather than "count of mentors overall." The same expression placed in the Report Footer instead would calculate exactly once, across every record in the whole report — the section it sits in, not the expression itself, is what decides whether a total is per-group, per-page or grand-total.

Useful tools for this paper

A handful of Access tools aren't tied to any one question above, but make working across several tables, queries, forms and reports in one database much faster:

• Navigation Pane (left-hand side): lists every object in the database, grouped by type (Tables, Queries, Forms, Reports) by default. Right-click its title bar → Category: Object Type if it's ever grouped differently, and use the search box at its top to jump straight to an object by name in a database with many objects.
• Design View vs Datasheet/Form/Report View (View button, bottom-right or Home tab): every object has at least two views — a "design" view for changing its structure (fields, controls, layout) and a "data" view for looking at the actual records. Toggling between them is how you check a structural change actually produced the expected result, without closing and reopening the object.
• Property Sheet (F4): toggles the properties panel for whatever field, control or section is currently selected, in Design View of a table, form, query or report — the single fastest way to open it rather than hunting through ribbon tabs.
• Expression Builder (the "…" button next to an expression-based property): opens a dialog listing every available field, function and operator for building a Validation Rule, calculated field or control source, which is especially useful for recalling an exact function name and its argument order without needing to type it from memory.
• Totals row in Datasheet View (Home → Totals): adds a row at the bottom of a plain table or query datasheet that can Sum, Count or Average a column instantly, without building a separate Totals query — handy for a quick check of a number before committing to writing the full query.
• Relationships window (Database Tools → Relationships): shows every table and how they're linked by shared key fields, which is worth opening early when a query or lookup needs to pull data that lives in a different table than the one you're currently looking at.
• Compact & Repair Database (File → Info → Compact & Repair Database): Access database files can grow and occasionally develop minor corruption with heavy editing; this built-in maintenance command shrinks the file and fixes small structural issues — worth running if a database starts behaving unexpectedly (objects not saving properly, unusually slow).
• Find (Ctrl+F) in Datasheet View: searches the currently open table or query's visible data for a value, which is faster than scrolling through a long table by eye to check whether a particular record exists.