ICDL Using Databases cheat sheet

A database is an organised collection of related data stored so it can be accessed, managed, and updated efficiently.

A table stores data in rows (records) and columns (fields); each row is one complete entity, each column one attribute.

A field is a single column and holds one type of data; a record is a full set of fields describing one item.

A primary key uniquely identifies each record in a table and cannot contain duplicate or null values.

A foreign key is a field in one table that references the primary key of another table, creating a relationship between them.

Relational databases link tables by keys; a one-to-many relationship is the most common (one customer, many orders).

Data types constrain a field: text, number, date/time, currency, yes/no (Boolean), and auto-number are typical.

A query extracts data matching criteria; a form is for data entry; a report is for formatted printed output.

Validation checks data on entry (e.g. range, required, format); a validation rule rejects invalid input before it is stored.

A database object is a table, query, form, report, or macro; objects are listed in the navigation pane.

Records are added in Datasheet view or through a form; the design of a table's fields is set in Design view.

A filter shows a subset of records temporarily; removing the filter restores all records without deleting data.

Sorting arranges records by one or more fields, ascending (A-Z, 0-9) or descending; it does not change stored order permanently.

The primary key field must be set before relationships are created and cannot be left blank in any record.

A relationship is created in the Relationships window by dragging the primary key onto the matching foreign key field.

Referential integrity prevents orphan records: a foreign key value must exist in the related table's primary key.

Print preview shows the output layout; page setup controls margins, orientation, and paper size for reports.

Each field has a name, data type, and optional description; the data type determines what values are allowed.

Field size limits text length or numeric range; setting it too small truncates or rejects valid data.

An input mask controls the pattern of entered data (e.g. a date or phone number) without changing the stored type.

A validation rule is an expression (e.g. >0 or Between 1 And 100) that rejects values failing the test.

A default value is automatically inserted into a new record when the user enters nothing for that field.

Required (not null) forces a value to be entered; a field left optional may store null.

An index speeds up searching and sorting on a field but adds storage and slows inserts; the primary key is indexed automatically.

A lookup field presents a list of values from another table or a fixed list, storing the selected value in the record.

A query selects records from one or more tables using criteria; the result is a dynaset that reflects current table data.

The design grid holds fields, table sources, sort order, show checkbox, criteria, and OR rows.

Criteria on the same row are combined with AND; criteria on separate OR rows are combined with OR.

Wildcards in criteria: * matches any number of characters, ? matches one character, used with Like (e.g. Like "A*").

Comparison operators (=, <>, >, <, >=, <=) and Between ... And ... define numeric or date ranges.

Is Null finds empty fields; Not Is Null finds fields containing any value.

Aggregate queries use functions such as Sum, Avg, Count, Min, Max, grouped by a chosen field.

A parameter query prompts the user for a value at run time, so one query can serve many criteria without editing.