Access 2016 Intermediate

Review table design principles in access 2016, including normalizing data, separating products and suppliers, and ensuring each field relates to the primary key via lookups.

Open the employees table in design view to adjust field size, apply date and number formats, and set captions like hours per week and pay rate for labels in forms.

Set default values in design view to auto-fill fields, such as weekly hours with 40, and require fields like phone number, weekly hours, and hourly rate to ensure complete records.

Learn to restrict data entry in Access 2016 by setting a validation rule to cap weekly hours at 40, with a 'no overtime' validation text.

Learn how input masks restrict data entry and enforce formats like phone numbers and employee IDs; use the wizard or manual setup to require specific characters, symbols, and placeholders.

Export a table or query from Access to Excel using external data, choosing location and format, optionally opening the destination file and saving export steps for reuse.

Learn how to import data from Excel into Access 2016 by cleaning the file, using named ranges, and importing as a new table with configurable field formats.

Export data to a text file by selecting text file format, choosing a tab-delimited option with field names included, saving to desktop, and preparing for easy import into other programs.

Explore how to link external data sources in Access, through linked tables for Excel and Access databases to view and query data without moving it.

Access 2016 intermediate demonstrates a Word mail merge using data from an Access table to create envelopes, linking to an existing Word document or creating a new one.

Explore action queries in access 2016, which perform actions inside tables, unlike select queries that pull data, and learn append, delete, update, and make table queries.

Preview beverage price changes with a select query, then convert to an update query to raise prices by 5%. Verify the updated rows in the table.

Convert an append query into a delete query to remove records from a table based on a where condition, such as category ID equals two (condiments).

Create a make table query to move discontinued products into a new table named Tbl Disc Prods by filtering for discontinued = yes.

Learn how query joins connect tables within a query. Use inner joins for related records and outer joins for comparison, including left and right variants.

Explore using the find duplicates and find unmatched query wizards in Access 2016 Intermediate to identify duplicate product IDs and products with no sales in order details.

Learn to create parameter queries in Access, using brackets to prompt users for a category and filter results, by building a user-friendly criteria prompt in design view.

Use the like function with asterisks to make parameter queries; append * to user input so con returns results starting with con, and leave it blank to return all records.

Refine query results in Access 2016 by using top x options in query design, selecting top numbers or percentages such as top five or 25%.

Create calculated fields in a query using the expression builder to multiply unit price by quantity for sale currency format. Concatenate first and last names into a sales associate field.

Create a summary query in Access 2016 to total sales by product using the totals feature and grouping by product name. Save the results as qry product totals.

Use the where condition on a summary query to filter results by a criteria field, such as quantity greater than 50, yielding a different set of totals.

Create a crosstab query in Access 2016 to show total sales by category and product name per year, using the wizard and naming the result qryCategorySalesPerYear.

Discover how to create and use macros in Access 2016, automate tasks with predefined actions, and trigger a form opening (suppliers form) from a button via the on click event.

Modify an Access 2016 macro to open the suppliers form to the current product’s supplier ID using a Where Condition and the expression builder.

Learn to build a macro for the products form that validates unit price before save, cancels the record when zero, and shows a 'must enter a unit price' message.

Create a macro in Access to automate data entry by setting country to Canada when region is B.C. or Quebec, triggered on region exit, then move to home phone.

Automate data entry in Access 2016 by creating a macro that uses dlookup to populate city, region, and country fields from a zip code table based on the postal code.

Apply conditional formatting to a products form by using an expression that formats units in stock green when units in stock is greater than or equal to the reorder level.

Design a two-tab access form using tab controls to organize fields by business and personal pages, link to the Employees table, arrange and caption tabs in design and form views.

Create a combo box on a supplier form to display a dropdown of company names sourced from another table or query, and store the selection in the company name field.

Create an option group in an Access 2016 form using the option group wizard, defining choices like print, internet, in store, and other, named how did you hear.

Create a sub-form on the customers form to show related orders from the orders table, linked by customer ID, updating as you navigate records.

Learn how to split an Access 2016 report into two columns using page setup, keep the whole group together on one page, adjust spacing, and preview in print layout.

Insert a subreport in Access 2016 to show order details under each order by linking to the order details table via order ID and adjusting formatting.

Configure parameter queries in Access 2016 to drive reports by category, prompting for a category value, and implement a no data macro to cancel printing with a no data message.

Export your access reports to Word, PDF, or other formats with simple, location-specific exports and optional open after export, enabling easy digital sharing.

Review core concepts from the Access 2016 intermediate course, including designing tables with normalization rules, sharing data with other applications, and mastering action queries, macros, forms, and reporting.