Microsoft Access 2013 Tutorial -A Definitive Guide To Access

Discover what a database is—an organized collection of data—and learn how electronic systems like Access 2013 enable flexible sorting, grouping, and custom data storage compared to paper databases.

Discover why Microsoft Access 2013 is a relational database management system, using linked parent and child tables with one-to-many relationships and primary keys.

Learn how to create a database using Access 2013 templates, download a desktop template, name the file, and customize tables, queries, forms, and reports, and enable content in trusted locations.

Create a blank desktop database in Access by choosing File > New and selecting blank desktop database; name the file and choose a storage location, understanding accdb and lock files.

Explore Access data types for new table fields: short text, long text, number, date/time, currency, auto number, yes/no, ole object, hyperlink, attachment, calculated, and lookup wizard.

Identify how a primary key provides a unique reference for each record in a table, using employee and bed examples in Access, and linking records.

Plan the database design on paper before Access, outlining two tables for clients and sales with prefixes, and define keys and data types.

Explore date time field properties in Access 2013, including default value with date(), display formats, and the date picker options for data entry.

Explore how field size sets maximum characters for short text, limits long text, and selects numeric field options such as byte, integer, long integer, and decimal for memory and range.

Explore how input masks safeguard data entry in Access by restricting characters in short text, numeric, and date fields, using the input mask wizard and predefined formats.

Learn how to safeguard data in Microsoft Access by applying validation rules and validation text to stop incorrect entries, such as dates not in the future and minimum name length.

Add calculated fields in Access 2013 to perform in-table calculations, replacing queries, by building formulas that derive bonuses and follow-up dates.

Learn to enter, edit, and delete records in Access tables using keyboard shortcuts, auto-generated IDs, and input masks, including copy from above and using drop-downs efficiently.

Learn to find and replace data in a table using Access's find utility, choosing the current field or the whole table, and controlling case, direction, and replace options.

Learn how to sort data in an Access table by any field, apply ascending or descending orders, and combine text and date filters to refine results.

Learn how select queries extract data from tables, filter results (such as by hair color or eating), and optionally sort the output.

Create a select query in Microsoft Access 2013 using query design; choose the customers table, include title, first name, last name, and email, save with a q-prefixed name, and run.

Sort data within queries by multiple fields, arranging last name before first name, and adjust sort order by field position and by showing or hiding fields in the query grid.

Filter text fields in Access 2013 by using the criteria row in a design view query, learn case-insensitive text matching with quotes, and save as reusable queries.

Explore numeric filtering in Access queries by using comparison operators—equals, less than, greater than, not equal to, less than or equal to, greater than or equal to, and between—on the total number of children.

Filter date fields in access with a design view query on the customers table. Use operators such as equal to, greater than, less than, and between, plus the date function.

Explore how to use wildcards in Microsoft Access 2013 queries to enhance criteria, employing like, asterisk, and question mark for begins with, ends with, and date patterns.

Identify missing titles and other empty fields in a customers table by building a query, using criteria like empty strings to flag missing data.

learn how to apply multiple criteria on the same field in Access 2013, using or logic across lines or on a single line, for text, date, and number fields.

Learn to apply multiple criteria across fields in an Access query, using criteria rows, and/or logic, wildcards, and dates to refine results to a targeted subset.

Learn why sql view matters by switching from design or datasheet to the sql view to see and edit the actual query code, including select, where, and order by.

Learn to build a design query in Access 2013 that adds calculated fields like profit (22% of total purchase YTD) and percentage of home children, with currency formatting.

Learn to create calculated text fields in a query, concatenate first and last names with an ampersand and a space to form salutation, and extract initials with the left function.

Learn to create query-based calculated fields that extract year, month, and day from date fields, then nest functions to generate month names, abbreviations, and uppercase values for flexible criteria.

Craft a query that calculates each customer's age from birth date using today's date, divide by 365.25, and convert to an integer for age-based filtering.

Group and summarize data in Access using the totals row to aggregate total purchase YTD by country region, with options for average, max, count, and more.

Create user interfaces in Access with the form wizard, selecting fields and layouts to view one record at a time and choosing column, datasheet, or tabular forms.

Explore the quick form method in Access to generate a form directly from a table or query, with layout and form views and navigational controls.

Learn how to view, edit, and delete records using different forms in Access 2013, including single and continuous forms, with changes committed when leaving a record.

Learn how to add new records to the customers table using forms, click the new record button, and use the tab key and date picker for date entry.

Learn to search records in Access forms using the find tool, choosing current field or all fields, matching whole, part, or start of a field, with optional case sensitivity.

Sort records in Access 2013 forms by a field, ascending or descending. Use filter by form or advanced filter to combine criteria like city equals Seaford and purchases over 10000.

Explore how layout view and design view in Access let you customize forms built with quick forms, preview data, and adjust height, width, and captions.

Learn how to color and format Access 2013 forms to boost readability by styling labels, data, and headers, spacing labels, and applying cohesive color schemes.

Add fields from the table to an Access form, drag controls, and align labels and data boxes for a consistent layout; use design tools to set equal width and spacing.

Control the tab order of added fields in Access forms by using design view, auto order, or manual moves, and adjust tab stop so the form starts at first name.

Add static images to access forms, insert logos in the header, resize with corner handles, and align color and text for a corporate, more presentable user interface.

Add a calculated field on an Access form to compute per-record profit using an unbound text box, an equals expression, currency formatting, and read-only protection.

Use the report wizard to build a presentable Access report from a table or query, select fields, set sorting and layout, preview, and export for printing.

Group access reports with the wizard by country region name, sort within groups, and use summary options to show total YTD and percent of total.

Explore how to use Access 2013's quick report after building a focused query of big spenders, adjust layout, landscape, and export a ready-to-send report for management.

Learn to edit wizard-generated reports in Microsoft Access 2013 by using layout, design, and print preview views; adjust column widths, spacing, and currency totals to fit pages, header and footer.

Learn how to print and export an Access report as a pdf, including print preview, data-only printing, page range, landscape setup, and sending as attachments.

Design a relational database by splitting a single table into related tables—clients, sales, country—using primary and foreign keys; store country IDs numerically to save space and speed queries.

Define one-to-many relationships by linking client id as a primary key in clients to the foreign key in sales, and link country id to client country.

Create and link three tables in Access by defining sales and country tables, setting primary and foreign keys, and building one-to-many relationships in the relationships window.

Enforce referential integrity to safeguard data by linking primary and foreign keys, preventing orphaned records, and applying cascade update or delete across related tables.

Discover how the subdatasheet tool in Access 2013, based on one-to-many relationships, streamlines data entry by expanding a plus sign to view and add sales records from a customer record.

Master multi-table queries in Access by leveraging existing relationships to join customers and countries, vary join types, and include non buying customers for a complete view of sales data.

Learn to build a single Access form that displays customer data with a sales subform from two tables, and use a combo box to select country from a countries table.

Create and customize reports from multiple tables using the report wizard, grouping by country or customer, sorting by name, and calculating totals with joined data.

Learn database normalization in Microsoft Access 2013 by splitting fields into smallest meaningful parts, using primary keys, and linking tables to reduce anomalies and speed queries.

Explore how to normalize a customers table in Access 2013 by splitting fields, creating lookup tables for repeating values, and linking tables with numeric IDs.

Use lookups in Access data tables to display descriptive text values from related tables while storing IDs, enhancing human readability and maintaining referential integrity through relationships.

Save 2013 databases to an older format with save as to enable opening in Access 2002, 2003, or 2000. Avoid using new features like attachments or calculated fields.

Explore how to create indexes in Access and why indexing speeds retrieval, using city or birth date with the primary key. Note that indexing slows input, so index select fields.

Learn how to create a composite primary key in Access 2013 by combining two fields, ensuring record uniqueness across client and date and preventing duplicates.

Create and print relationship reports from the relationship window to snapshot current table links, including customers, countries, sales, and yearly income, saving and exporting snapshots as ptf files for reference.

Import or link the customers table from another access database via the external data ribbon, then decide to import data or create a linked table for live access.

Learn how to import Excel data into Access as a new table or append to an existing one, or link to Excel to reflect changes, ensuring column headings match.

Learn how to import data from plain text files into an Access database, choosing append or link options, defining delimiters, and aligning field headings with the destination table.

Explore using more than one table in an Access query by building joins across countries, customers, and sales, and aggregate totals by country.

Learn how to change join types in Access queries, switching from inner to left or right outer joins to include more records, without altering table relationships.

Be careful of cross joins and Cartesian joins when building multi-table queries in Access; without proper relationships, records multiply unsafely. Establish joins to prevent massive, unrealistic results.

Create parameter queries in Microsoft Access 2013 using bracket prompts to filter by country, last name, or sales totals, including between ranges and greater than filters.

Create a base query from multiple tables to pull country name, yearly income, and sales. Then build a crosstab to summarize sales by income bracket and country.