Advanced databases

Apply and change data types such as text, memo, hyperlink, currency, date & time in the field (column);

Understand consequences of changing data types;

Create, modify, delete a LOOKUP in a field (column);

Create, edit the rule of data verification (validation rule) for the field (column);

Create, edit the input mask in the field (column);

Apply, change the default values for the field (column);

Set a field (column) that requires mandatory data entry

Tables design – Relations/Connections:

Create, modify relationships between tables of type one–to–one and one–to–many;

Understand and change the relationship between the tables of the many–to–many type;

Apply, modify inner join, outer join and self join;

Apply referential integrity and automatic deletion of related records;

Connect/merge data when creating queries

Create and use a query to update the data in the table;

Create and use a query to delete records from the table;

Create and use a query to save selected data as a new table;

Add records to a table using the query;

Group information in the query using: SUM, COUNT, AVERAGE, MAX, MIN;

Create, run a crosstab query;

Use wildcard characters in a query;

Use arithmetic and logical expressions in a query

Query design (Queries) – Adjusting a query:

Display duplicates;

Create, run a query to show unmatched records in related tables;

Show highest, lowest range of values in the query;

Allow the entry of arbitrary parameters in the query via the data input box (parameter query);

Adjusting a query by using the NULL and NOT

Create, modify, delete bound and unbound controls;

Create, modify, delete controls: combo box, list box, check box and option groups;

Create controls with arithmetic or logical expressions on the form;

Insert a data field that will appear in the header of the form, the footer of the first page or all pages

Forms design (Forms) – Subforms:

Create, delete a linked subform;

Modify a subform to change displayed records

Report design – Computing:

Create controls for arithmetic or logical computation on the report;

Create a control to calculate the percentage on the report;

Use formulas and expressions on the report, e.g. SUM, COUNT, AVERAGE, MAX, MIN, CONCATENATE;

Create the current totals on the report

Report design – Presentation:

Insert a data field that will appear in the header of the report, the footer of the first page or all pages;

Force page breaks for groups of data on the report

Macros – Recording and assignment:

Record a simple macro, e.g., closing a form;

Run a macro;

Assign/attach a macro to a form, a report, a control

Import, export and linking data – Data management:

Export data as a spreadsheet, text, dBase, Paradox;

Link external data to the database