Microsoft Access Database Basics

Lookup Wizard

• Enables creating a drop-down list of values for user selection in a table, making data entry easier and reducing errors.
• Values can be typed in manually or dynamically based on another table in the database.

Field Properties

• Sets boundaries on data entry in table fields to prevent bad data (GIGO).
• Field Size: sets the maximum number of characters that can be entered into a text field.
• Format: sets how data is displayed in the table field.
• Input Mask: sets rules for correct data entry.
• Default Value: sets the default value that appears in the table field when a new record is entered.
• Caption: provides alternative field names for database objects that reference the field.
• Required: sets whether a value is required to be entered into the table field.
• Validation Rule: sets specific data entry rules that must be followed strictly.
• Validation Text: displays an error or warning message when the user enters invalid data.

Primary Key

• A field or combination of fields that uniquely identifies a record in a table.
• Must be unique and follows the rule of thumb for primary keys.

Microsoft Access Database

• A collection of data organized to allow access, retrieval, and use of that data.

Tables

• Store data in a database and contain records (rows) and fields (columns).
• Can be viewed in two ways: Datasheet View and Design View.
• Datasheet View: used to add, edit, and delete records in a table.
• Design View: used to create and modify the structure of a table.

Queries

• Allow asking questions about the data in a database.

Fields

• Represent a single piece of data or attributes of every record in a table.

Forms

• Used for entering data and display the data or build a menu system.
• Built off of tables and queries.

Reports

• Typically built off of tables and queries.
• Used to format and present data in a professional, logical, and useful manner.

Macros

• A tool that allows automating repetitive tasks and operations.
• Often used to open forms and reports to automate portions of the database.

Report Wizards

• A tool used for building reports and forms.

Database Hierarchy

• Field → Records → Table → Database (from smallest to largest).

Designing Database Tables

• Include necessary data determined by reporting needs.
• Store data in its smallest parts (e.g., split last and first names).
• Avoid storing calculated data due to continuous updating.
• Avoid data redundancy.

Table Wizard

• Not recommended for use.

Primary Key

• Must be unique and follows the rule of thumb for primary keys.

Field Data Types

• AutoNumber: automatically assigns a unique number to each record.
• Short Text: stores up to 255 characters of text, numbers, and symbols.
• Number: stores numbers used in mathematical calculations.
• Long Text: stores up to 63,999 characters of text, numbers, and symbols.
• Date/Time: stores dates and times.
• Currency: stores currency values up to four decimal places.
• Yes/No: stores binary representation of data (e.g., Yes/No, True/False, or ON/OFF).
• OLE Object: stores links to objects such as Microsoft Word documents, image files, or Microsoft Excel spreadsheets.
• Hyperlink: stores website addresses, email addresses, and hyperlink data.
• Attachment: stores various file formats in the database with the option of editing files within the database.
• Calculated: a special data type used to display results of calculations or expressions based on other fields in the table.