MOS1.4. Access Advanced (English)

We assume that you have been working with Access 2016 for at least six months and that you know how to carry out fundamental tasks that are not specifically mentioned in the objectives for this Microsoft Office Specialist exam.

The certification exam and the content of this book address the processes of designing and building Access databases. We assume that you are familiar with the Microsoft Office ribbon and that you understand basic Access features—for example, that you know how to enter and edit data. We also assume you are familiar with the definition and function of relational databases and database objects such as tables and forms. To provide context and an opportunity for review, the following list provides brief explanations of five important terms:

• Table Defines the data stored in a database. Tables are composed of fields, and each field is defined as a specific data type (text, number, date, or another data type). Each field also has certain properties. For example, you can specify that a field is required. You can also define the size of a field (such as the maximum number of characters a field can contain). Users of a database fill in fields (and must fill in required fields) with values to create a record in the database. In most tables, each record is identified by a unique value called a primary key, which might be a single field (such as a product ID) or a combination of fields.
  
  
• Relationship Helps maintain the integrity of the information in a database and reduce data redundancy. You can create several types of relationships between tables in an Access database. In a one-to-many relationship, a record in one table can be related to one or many records in another. You can also create one-to-one relationships and many-to-many relationships. Relationships are created by linking a table's foreign key (such as a customer ID field in an order table) with another table's primary key (the customer ID field in the customer table). Relationships protect data integrity by preventing you from creating orphan records (for example, an order with no customer). Relationships help reduce data redundancy by letting you store information in separate tables that you link together. For example, you can create a customer table and then relate each order in an order table to the record for a specific customer. This prevents you from having to enter a custom record for each separate order.
  
  
• Query Can be used to select records that meet specific criteria and to perform actions such as updating a group of records. To build a select query, you add fields from one or more tables and then define criteria that Access uses to retrieve the records you want to view. For example, you might want to retrieve records with a certain value in a date field (all records created after 1/1/2017, for example) or records associated with a specific project. Using criteria, you can also create and run action queries that insert, update, or delete selected records.
  
  
• Form Used to display, enter, and edit data. Forms are often bound to tables (or to queries) that serve as the form's record source. Forms use controls such as text boxes, check boxes, and list boxes to provide a user interface for a database. Forms can also be used to confirm and execute database operations and to navigate from one database object to another. Access provides several built-in form designs, a gallery of form controls, and tools you use to design and lay out a form.
  
  
• Report Used to share and present data and to summarize data for a specific field or fields. You might print reports for a meeting or distribute them electronically as PDF files or in email.

---