Advanced Microsoft Access 2010 Tutorial

Learn to use input masks in advanced Microsoft Access 2010 to enforce data formats for dates, numbers, and text, apply masks to fields or forms, and customize placeholders.

Explore how indexing in Access 2010 speeds data retrieval by creating index tables for key fields like date of birth and country, while balancing insert costs.

Create a dual primary key in Access 2010 by selecting two fields, ensuring unique combinations, removing duplicates, and safeguarding related item data.

Learn how to return distinct items in an Access 2010 query by enabling unique values or using the totals feature to group results, and compare these approaches with practical examples.

Learn how to use parameter queries in Access 2010 to prompt for criteria at runtime, including dynamic date and month filters, with minimal queries.

Create calculated fields in an Access 2010 query to compute line totals (quantity times price) and display the month name derived from date started, with field naming in design view.

Apply a 10 percent discount in a calculated field using iif when the line total (quantity times price) exceeds $20, in an Access 2010 environment.

Create crosstab queries in Microsoft Access 2010 to summarize data by cross tabulation, using item titles as rows and years as columns to total line totals.

Create a cross-tab query from scratch in Access, using formulas to set row headings by month and column headings by year, summing line totals to show yearly monthly totals.

Learn to change join types in Access queries, from inner to left outer joins, to show unsold stock and unmatched records.

Learn to write SQL select queries in Access, choosing specific fields or all with an asterisk from a table, and apply WHERE and ORDER BY for filtering and sorting.

Learn to use the expression builder in Access to build calculations like line total (item cost times quantity), explore built-in functions, and reference form fields for query criteria.

Discover how to use a subquery inside an in clause to filter results, by selecting item IDs or basket details with nested queries in Access.

Learn to create a union query in Access to merge distinct customer and supplier data into a single result table.

Use the make table query to copy all fields from the basket table into a new backup table, preserving data types, and confirm prompts when running the action query.

Learn to create a make table query from multiple tables to produce a separate December 2010 sales table, enabling safe what-if analysis without affecting live data.

Learn to craft update queries in Access to change table data, such as increasing stock prices by 50 cents or 10 percent, with careful criteria and design view.

Create a parameter-driven update query in Access 2010 to adjust stock prices at runtime. Use a user-entered percentage to calculate new prices with stock_price * (1 + percent/100).

Learn to use an append query to add filtered records to an existing table, matching fields and consolidating monthly sales data from 2009 into 2010.

Learn how to create and execute delete queries in Access 2010, including selecting records via criteria, understanding the risk of deleting entire records, confirming deletions, and backing up data.

Back up and restore data with an archive table using make table, delete, and append queries; move old records past 90 days to archive and append them back when needed.

learn to add an ole object field, embed or link images in an Access 2010 form, and display bitmap pictures directly on the form for a richer data presentation.

Learn how to create and configure bound versus unbound subforms in Access 2010, including switching to continuous and tabular layouts, linking data, and using simple queries for totals.

Use a tab control to display multiple subform data in one form, with tabs like description, sales, and related items, linked by master and child fields.

discover how to access data from extra columns in an unbound combo box in Microsoft Access 2010, hide the first column, set column widths, and use column(index) to retrieve values.

Learn to use the new navigation forms in Access 2010 to load forms into an unbound area via top, left, or right tabs and buttons.

Create a menu form in access by turning off record selectors and navigation buttons, hiding scroll bars, centering the form, and using a modal dialog with ok and cancel.

Turn forms into navigable menus by adding buttons with the wizard, customize form properties, and set actions to open other forms or close the current form, all without code.

Create a new blank form, disable the wizard, and add a named command button; define an on click event in VBA to close or open a form using DoCmd.

Open another form filtered by the current record using a button, by setting the second form's data source to filter on the item id from the first form.

Learn to open another form filtered by the current form's selected item using a where clause in VBA, letting you view related items without permanently altering the source.

Create a modal search form with a text box named search that filters the item list by title using a like wildcard, then close the search box.

Validate search input in a Microsoft Access 2010 form by using an if statement to block empty criteria, show a message box, and refocus the search box.

Learn to use a list box double-click to open a related data form and filter by the selected item ID using a where clause and open form event procedure.

Learn to dynamically change a list box or combo box row source in Access 2010 using filters, sorting, and VBA controls to display in stock items and custom views.

Create a Microsoft Access 2010 report from scratch without the wizard, set page size and orientation, bind to a data source, and use layout view and print preview.

Insert images into the report header, adjust their size, and position them so the header appears on every page via the page header; use the image gallery to reuse assets.

Compute totals on an Access report by adding a calculated field that multiplies quantity by unit price, formats like currency, and uses the expression builder for accuracy.

Sort and group reports in Access 2010 using design view, setting date sorts newest to oldest, creating grouped headings, and applying year and month groupings with repeating headers.

Learn to add totals to grouped reports in Access by month, using footers, calculated fields, and currency formatting, then preview results.

Learn to create and configure subreports in Microsoft Access 2010, link master and child fields, tune layouts, borders, and summaries, and preview printouts for integrated item and sales data.

Master creating address labels in access by concatenating fields and adding carriage returns with special characters. Use if statements to skip empty fields and format lines.

Add a chart to an Access report using the chart tool and wizard to plot monthly sales data from a query, customize the line graph and legend, and print preview.

Create a line chart in an Access report filtered by the report section, using the item id as the filter to show sales data for a single item.

Create and customize pie charts in Access 2010 reports to visualize sales by year, compare quantities, and display percentages with adjustable slices, colors, and legends.

Create a macro in Access 2010 using the macro builder or Visual Basic. Configure a message box with text, icon, beep, and a title, then save and run.

Explore how macros automate access tasks like opening forms and filtering. Learn to run update queries, increase price by 5 percent, manage warnings, and display messages as a mini program.

Assign macros to buttons on forms. Create two macros: one opens the items form with all records, the other opens the items form filtered by the current item id.

Use an if statement in an Access macro to validate a user-entered price increase from a form text box, then update the item price via a query.

Learn how to add comments to macros in Access 2010 to clarify logic, assist future programmers, and simplify amendments as macros become more complex.

Discover the embedded macro, tied to a single event and sometimes called an embedded micro, and how to build or edit it with the macro builder using full path syntax.

Learn how to organize multiple macros in Access 2010 by wrapping six form-opening macros inside a single main macro, using sub macros to streamline menu buttons and form navigation.

Learn to trigger access macros from the ribbons and quick access toolbar by adding macros, renaming groups, changing icons, and editing tooltips.

Configure the autoexec macro to run at database startup, open the main menu, and log the current user via an append query to the login data table.

Learn to create an auto keys macro in Access that stores keyboard shortcuts—named after each shortcut—and triggers actions like opening forms or displaying messages within the database.

Add an ok/cancel confirmation box to a macro using a VBA message box. The prompt asks 'are you sure you want to run this increase' and proceeds only if ok.

Compact and repair reduce bloated access databases by reclaiming unused space, shrinking a file from 412 megabytes to about 57.4 megabytes, accessible via the database tools ribbon or file menu.

Save your Access 2010 database to older formats, selecting 2003 or 2000 as the target. Remove incompatible features before saving to ensure older versions can open.

Learn to back up your Access 2010 database using the backup database command, which saves a dated copy you can overwrite or rename for safe storage on removable media.

Track how objects in an Access database depend on each other with the object dependencies panel, refresh to reveal related forms and tables, and open items to edit relationships.

Configure start-up behavior in Access 2010 with startup settings to choose the startup form, title, icon, window style, and navigation pane options, and use F11 and Shift shortcuts.

Tidy the main menu form by setting a caption, removing scroll bars and min/max buttons, and centering the window in design view for a stable menu.

Compile and distribute a restricted access application by converting an ACCDB to ACCDE, locking design access to forms and reports while leaving query and table editing possible as potential workarounds.

Download and distribute the free Access 2010 runtime to run your app on PCs without full licenses, choosing 32-bit or 64-bit and locking down the backend.

Split the database into a front end and back end, password protect the back end, and link them to run a secure application with restricted design access.

Learn to upgrade an Access application to use SQL Server as the back end by using the upsizing wizard to export tables and link them for centralized data access.

Learn how to link a new Access front-end to an existing SQL Server using a system DSN via ODBC, configure authentication, and access linked tables and views from Access.

Run a stored procedure from an Access project by selecting it, supplying the contact id parameter, and executing to update salaries, then refresh the form to display 5 percent increase.