Mastering Microsoft Access 2010 Made Easy Training Tutorial

Create a new blank database in Access 2010 by file, new, and blank database; name the file, set a save location, click OK, then click create.

Design a simple, logical database with related tables to store and retrieve data, enter data via forms, pull information with queries, generate reports, and automate processes with macros and modules.

Explore how the flat-file method stores data in one-to-one records and why it fails with one-to-many sales data. Apply the relational model to avoid redundancy and scale in Access.

Model the relational data storage to reduce redundancy by structuring customers and sales. Identify entities and keys, and connect tables with primary and foreign keys through a sales details table.

Discover tips for creating a relational database in Access, including identifying entities and attributes, sketching relationships, and applying normalization forms to ensure sound table design.

Create relational database tables by designing structure in design view and viewing data in datasheet view, assigning unique, descriptive field names and appropriate data types.

Assign a primary key to each table using a field that uniquely identifies records. Index the table, reduce duplicates, and create composite keys or an auto number field as needed.

Explore datasheet view as a quick way to add, edit, and delete records, while recognizing its limits for data types and field properties; open design view to verify structure.

Navigate datasheet view with tab, enter, or arrow keys (including shift+tab), click fields to edit data, and use scroll bars to move between records.

Learn how to add records in datasheet view by entering data into the new blank row, with a pencil icon indicating the active new record.

Edit a record in datasheet view by clicking the field; changes save when you exit the cell. Delete a record by selecting the row, pressing delete, confirming; deletions are permanent.

Insert new fields in Access 2010 tables via design view, using the row selector to insert, name the field, choose a data type, then save and switch to datasheet view.

Rename fields in table design view, but proceed with caution since changes may not update related reports, forms, or queries; double check objects and save via the Quick Access Toolbar.

Delete unused table fields only after ensuring no queries, forms, reports, or macros reference them; in design view, select the field and click delete rows, then confirm.

Set table field properties in design view via the table design grid and field property section; use the general tab for properties and the lookup tab for lookup fields.

Set the field size to limit text input to 255 characters; number fields use types such as integer, long integer, single, double, replication ID, and decimal, defaulting to long integer.

Set the format property for date/time fields to control display in the data sheet view, choosing general date, long date, or long and short time formats.

Set the format property for logical fields to control display in forms and reports, using the design grid and format dropdown to choose true, yes, or on, or off.

Set the default value property to auto-populate new records with a chosen value—text, yes/no, number, or a calculated expression—for almost any field type.

Apply input masks to text and date/time fields with the input mask wizard, guiding field selection and finishing the mask after saving the table, while watching for format property conflicts.

Learn to enforce data entry with validation rules in Access 2010 by using the expression builder to create conditions (such as no future birth dates) and display custom error messages.

Set the required property for a selected field to yes or no to enforce entry into the field and prevent skipping.

Set the allow zero length property on a selected field to yes or no, enforcing data entries with length greater than zero and preventing empty or invisible characters.

Learn to relate tables in Access using the Relationships window, adding tables, and creating joins between shared fields. Understand one-to-many relationships and one-to-one relationships and how Access assigns joins.

Configure referential integrity in Access by establishing table relationships with primary keys and compatible data types to prevent orphan records in joins, and enable cascade updates or deletes as needed.

Create lookup fields in a table using the lookup wizard to pull values from another table, query, or list. Enforce referential integrity and review joins in the Relationships window.

Apply indexing in Microsoft Access by creating indexes on fields used for query criteria to speed sorting and data retrieval, while avoiding fields with duplicates.

Create and manage indexes in Access by selecting a field, naming the index, and setting sort order, with primary, unique, and ignore nulls options.

Delete unwanted indexes by opening the table in design view, using the indexes dialog to select and press delete, then save the structural change via the quick access toolbar.

Use the simple query wizard in Access to pull data from one or more tables, select fields, and choose detail or summary outputs for reporting and analysis.

Design queries in design view by adding needed tables, using the QBE grid to select fields, apply criteria and sorting, join related tables, then run and save.

Drag and drop fields between tables in the query design view to create joins, including temporary joins for unrelated tables, then use join properties to select options and influence results.

Use the qbe grid in design view to filter records by a city criterion—Detroit—by placing the customer table and selecting city, then running the query to display Detroit customers.

Run a query from design view to view results, and refine its structure if needed; run a saved query by selecting its name or double-clicking it in the navigation pane.

See how the qbe grid in Access visually builds SQL code, and view the query as ASCII code in design view using the view command to inspect how it works.

Sort query results by any field in design view using the sort row to choose ascending or descending, and apply left-to-right sorting for multiple fields.

Hide fields in a query by de-selecting the show row checkbox in the QBE grid, preserving criteria and sorting while preventing the field from displaying in results.

Use comparison operators in the cube section of the query design view to filter records via the criteria row in the QBE grid, with date values enclosed in pound signs.

Filter query results by applying and or conditions across multiple fields, using the same criteria row for and conditions and separate rows for or conditions.

Use the between and condition to search for records with values in a field between two inclusive bounds, such as dates from February 1 to February 28, 2010.

Learn how wildcard characters in criteria add flexibility to Microsoft Access 2010 queries, using the asterisk and question mark, and understand the criteria row syntax and grid behavior.

Create calculated fields in Access queries using design view, reference fields with brackets and table prefixes, perform arithmetic, and view the result in the query output.

Create top value queries in Microsoft Access 2010 to display top or bottom records, sorted by values in ascending or descending order, using the return dropdown and numbers or percentages.

Create summary queries using aggregate functions on grouped fields for reporting, and use the totals button to sum sales for each group.

Create parameter queries in Access that prompt for input values, making queries reusable and reducing editing time; use between with two prompts for date ranges, such as February sales.

Create a new permanent table from a select query using make-table queries, restricting fields and rows with criteria, and choosing the destination in the current or another database.

Create and run update queries in Access to update selected records by criteria, updating fields with expressions such as right and mid, using the QBE grid and update to row.

Create append queries in design view to copy records from one table to another based on criteria, mapping fields with compatible data types to the destination table.

Use delete queries to remove records that meet specific criteria, improving performance and reducing redundancies. Create a design view query, set criteria, then run and confirm deletion.

Learn to build cross-tab queries in Access 2010, using a single base table to summarize data by row and column headings with aggregate calculations via the cross-tab query wizard.

Use the find duplicates query to identify duplicates by selecting a table and fields. Do not delete duplicates with this tool; copy the table structure and create a new table.

Identify and fix unmatched records to preserve referential integrity by using the find unmatched query wizard to locate orphaned child records and link them to valid parent records.

Design data entry interfaces in Microsoft Access 2010 using forms, including switchboard forms to run queries and produce reports, and enter data securely into table fields instead of datasheet view.

Master the form wizard in Access 2010 and 2007 to create basic data entry forms by selecting a table or query, moving fields, and finishing to open or modify.

Create data entry forms in Access 2010 by selecting a table or query and choosing a form type from the Create tab; compare split forms, datasheet views, and multiple items.

Use a data entry form to edit and create records, navigate the table with tab keys and form navigation buttons, and add new records.

Switch between layout view and design view to edit forms, adjust field placement, and apply formatting. Layout view shows data; design view offers more properties for form objects.

Use the same design and layout views for forms and reports; open design view via right-click in the navigation pane or create a new form from the Create tab.

Toggle the ruler and grid in form design view to precisely place and align controls in Access 2010 forms and reports.

Toggle the snap to grid feature to align controls with the underlying grid in form or report design view, or disable it for flexible placement.

Create a form in design view by adding existing fields from a table or query, drag fields onto the form, and view the field list pane with paired controls.