Microsoft Office Access 2010 Advanced

Learn to use the structured query language inside Microsoft Access, create SQL statements including subqueries, and attach them to events such as button clicks or form openings.

Learn how to use the structured query language inside Microsoft Access to query data, build effective filters, and manage databases efficiently.

Learn sql basics through select and from to query data from product and order item tables, retrieving specific fields or all columns. Focus on data manipulation language for selecting records.

View any query in sequel view to see the structured query language behind it. Switch from design view to sequel view for advanced queries like union queries.

Open the orders database and view the sample query in design view to see how Access uses SQL, via the SQL view, to select fields from a single table.

Enter sequel statements directly in sequel view to create queries from scratch, or use design view and switch to sequel view to modify as you learn the sequel language.

Create a sql statement using a design view query. Switch to sql view from the ribbon or status bar, enter the statement, and save the query for later use.

Create your own sql select statements from scratch in access, using select and from clauses with the table called TBO retailer. Save and run the query after choosing the fields.

Learn how to filter records in Access queries using the where clause after from, specifying criteria with equality, comparison, and range (between values) to refine results.

Filter retailer data in a sequel query using a where clause to apply criteria and return only matching records, then save changes.

Rename fields in your Access query using aliases in the select clause, such as retailer ID as vendor, address as address, and phone as contact, from TBO retailer.

Explore how Microsoft Access 2010 uses caption property to name query columns and how to alias fields in design view, as with retailer ID as vendor and address and contact.

Display matched records by selecting fields from related tables, listing the tables in the from clause, and defining their relationship in the where clause (customers and orders).

Master multi-table queries in Access 2010 advanced by linking product and order item tables on product ID, selecting fields from both to display matching records.

Explore how the unmatched records query wizard in Microsoft Access uses a select statement with a not in subquery to find customer IDs lacking orders.

Learn to build an unmatched records query in access using a subquery and not in, identifying retailers with no orders by comparing retailer IDs from the order table to retailers.

Discover context sensitive help in Microsoft Access query view: place the cursor in a clause such as select or where and press F1 to view clause descriptions, variations, and syntax.

Explore how Microsoft Access provides context-sensitive help for SQL, using F1 to view the select and where clause syntax and usage in design view.

Learn to attach SQL queries to events from controls on forms in Access, within Microsoft Office Access 2010 advanced.

Explore SQL-specific queries that go beyond design view, including union queries that stack results, pass-through queries for cross-database execution, and data definition queries for creating, deleting, or modifying structures.

Attach a SQL query to form button to run on demand; create and save the query, then use the wizard to add a command button and run it on click.

Add a command button on a form and use the command button wizard to define its action. In the wizard, select the miscellaneous category and run query before clicking next.

Create a union query from two retailer tables for New York, save it as q r y retailer info query, and assign it to a form button.

Explore using SQL in Microsoft Access, build union queries and multi-table join queries, and attach them to events like button clicks on forms.

Create crosstab queries to summarize and analyze large data sets. Build parameter queries that prompt users for information, and implement action queries that modify data inside the database.

Master advanced techniques to create crosstab queries in Microsoft Access 2010. Topic a explains creating crosstab queries inside Microsoft Access.

Explore crosstab queries in Access 2010, pivot-table style, using the wizard to create row and column fields from unique values and summarize intersections with sum, average, count, min, or max.

View an existing cross-tab query in access, run the q r y retailer crosstab query, and observe rows by retailer IDs and columns by products with a summary.

Create a crosstab query in Access 2010 using the query wizard. Choose a table or view, set row and column headings, select the summary field and function.

Explore the steps of the crosstab query wizard by selecting the field to summarize, choosing a summary function, and previewing fake data for columns, rows, and row sums or totals.

Learn to build a crosstab query in Access 2010 using the crosstab query wizard, with product names as rows, retailer names as columns, and quantity summed.

Create parameter queries in Microsoft Office Access 2010 advanced that prompt the user for information each time they run.

Create a single-criterion parameter query by prompting for input in the criteria row. Use square-bracket prompts such as please enter the monthly quota to filter the sales amount column.

Create a single-criterion parameter query in Access 2010 by using a parameter prompt to filter unit prices greater than a user-specified value, and run the query with different thresholds.

Create a multiple criteria query by using parameter prompts in design view to filter on several columns or a date range, then save and run it.

Create a query with two parameter prompts using the between syntax to filter order dates from a start date to an end date in design view.

Create a wildcard query in design view using the like operator, parameter prompts in square brackets, and ampersand-concatenated asterisk or question mark to match partial customer names.

Learn to use wildcards in a parameter query with the like operator, concatenating an asterisk or question mark to the prompt to filter retailer names.

Learn to create action queries to insert rows, modify rows, delete rows, and to create entirely new tables within your database.

Explore action queries in Microsoft Access to append, delete, update, or create new tables from selected rows across existing tables.

Use an append query to move selected rows from one table into another, often for archiving data; design the query, click append on the design tab, save it, and run.

Create an append query in Access to add two records from TBO new order to TBO order by converting a select query in design view and running it.

Learn to create a delete query in design view to remove records from a single table, and for multiple tables perform a multi-step process, confirm deletions, and save the query.

Demonstrates creating an update query in Access design view, using parameters for retailer ID, phone, and fax to update a subset of records and verify changes.

Use an update query to modify existing records by designing the query in design view, adding tables and fields, setting parameters for criteria and values, and running to update rows.

Create an update query in Access design view to modify retailer table records by ID, using parameters for ID, phone, and fax, and run the query to apply new values.

Turn a select query into a make table query in design view, name the new table, and run it in the current database to create a table from the results.

Create a new table by using a make table query that selects columns from the employee table and populates the TBO employee table in the current database.

Explore crosstab queries to summarize large data, parameter queries that prompt user input, and action queries to insert, update, delete rows and create new tables from existing data.

Learn to create macros to automate events in Microsoft Access, run and modify them, and attach macros to events on database objects such as forms and buttons.

Create and modify macros to automate events inside your database, and learn how to run them to streamline database workflows.

Learn how Microsoft Access macros automate user actions, validate data, and open forms, reports, or tables, using if statements and sub macros to build modular workflows.

Use the macro tools on the design tab to run and single-step test macros, view if branches, convert to Visual Basic, and enable restricted actions in trusted databases.

Create and run a macro in Access to automate opening the employee table in datasheet view for editing. Test it by adding two new employee records and verify the updates.

Modify a macro in access by adding a message box action, prompting ‘click OK to edit records,’ then edit a record and learn how not saving changes affects the action.

Attach macros to events on database objects to automate responses in Access databases. Learn how these macros respond to various events that occur to objects in your database.

Use the event tab in the property sheet to connect events on controls, or the form itself—such as open, close, resize, or key press—to macros.

Attach a macro to the open event of a form in Access 2010 advanced so the macro MC our employee runs automatically whenever the form opens.

Attach a macro to a command button by opening the form in design view, selecting the on click event in the property sheet, and choosing the macro to run, save.

Attach a macro to a command button so clicking runs MC are open retailers, opening the retailer form from the product form and enabling navigation between forms.

Create, run, and modify macros to automate actions in a Microsoft Access database, and attach them to events like button clicks or form openings.

Expand your mastery of macros by building user-interactive solutions for data validation via parameter queries. Explore auto-run macros, keyboard shortcuts, and transferring objects into and out of Access databases.

Learn to create macros in Microsoft Access 2010 advanced that provide user interaction by inserting default values into forms and giving feedback when required data is missing.

Learn to build form macros with conditions that validate data, alert users, and cancel events when required fields like address are missing, providing immediate feedback.

Create a data validation macro using the if action to enforce a condition, notify users with a message box, and attach it to a form event.

Learn to build an Access 2010 data validation macro that checks the address field on a form before update, cancels the save, and shows a message box prompt.

Create a data entry macro in Access to auto-fill form fields by testing for null values and supplying defaults, such as today's date, on lost focus.

demonstrates creating a data entry macro in access 2010 that auto fills a missing order date and sets a purchase order payment method for select retailer ids on lost focus.

Learn how to create a macro that involves interaction with the user and gathers data from them in Microsoft Access 2010 advanced.

Create and save a parameter query, then build a design view macro that opens the query as the first action; attach it to the form’s on click button and test.

Create a macro that prompts for a product ID to run a parameter query showing stock levels, and trigger it from a form button to check stock while entering orders.

Design and implement auto-running macros for Access 2010 that execute when the database opens and assign keyboard shortcuts to other macros using AutoKeys and AutoExec.

Create an auto keys macro to connect keyboard shortcuts to macros, defining sub macros invoked by ctrl-a and ctrl-b to open forms in standard form view or edit mode.

Create an AutoKeys macro in Access 2010 to assign keyboard shortcuts for opening forms, using sub macros named with the caret symbol to trigger retailer and order item forms.

Create an autoexec macro to run on database open, launching a main switchboard form with buttons for subforms and reports, and save it as AutoExec so Access runs it automatically.

Rename a macro to auto exec to have it run automatically whenever the Access 2010 database opens, enabling actions like opening forms, clearing tables, and running queries or reports.

Explore how to create macros for transferring data into and out of Microsoft Access, enabling efficient data movement in Access 2010 advanced.

Use the import export data action in macros to import or export data between an Access database and sources such as Excel spreadsheets, comma delimited text files, or SML files.

Demonstrates using a macro to import a table from one Access database into another, creating a repeatable process that transfers data and structure for the department table.

Create a macro to export data to a spreadsheet. Specify the table and path, enable field names in the first row, then save and run the macro to export.

Develop macro-driven data exports by exporting order table to an export spreadsheet in excel worksheet, including field names, saving the file in the current topic folder, and running the macro.

Design Access 2010 macros that validate fields, provide defaults, require input via parameter queries, assign actions to keyboard shortcuts, run on open, transfer data in and out of Access databases.

Import objects from Access databases, Excel spreadsheets, text files, and SML documents; export objects to other Access databases; link Access objects across databases; create and use hyperlink fields.

Import objects from another Access database, from an Excel spreadsheet, and from text files to enhance data management in Access 2010 advanced.

Import objects from another Access database to copy tables, forms, queries, or reports into a destination database, using the External Data tab to select and import.

Use the import objects dialog box to select tables, queries, forms, reports, macros, and modules across tabs, then click okay to import all chosen objects into your database.

Import objects from one Access database to another using the external data tab, then copy tables, queries, and forms as independent objects.

Import data from Excel into Access by ensuring a header row and no blank rows, then browse with the External Data tab and import to a new table.

Learn to import data from an Excel worksheet into a new Access table, set the primary key, and save the import steps for a seamless workflow in Access 2010.

Save import specifications to automate frequent imports in Access, and use the manage data tasks window to rerun saved imports or exports on demand.

Learn to save and reuse an import specification in Access 2010, then rerun the import to update the order item table after modifying data in Excel.

Import text files into Access using fixed width or delimited formats, where rows become records. Map each data element to a field and ensure data types match the table.

Import data from comma delimited or fixed width text files into a new Access 2010 table using the import wizard, name it tb employee one, and set the primary key.

Master exporting objects from one Access database to another within the Microsoft Office Access 2010 advanced course, using practical steps.

Export objects such as tables, forms, queries, and reports from one Access database to another, one object at a time, via the external data tab.

Use the export table dialog box to select the destination database and choose between exporting the table definition only or with data, creating or appending as needed.

Export Access database objects to various destinations using the external data tab, choosing definition and data, then verify row counts and export query results to Excel.

Save and reuse an export specification to export retailer query results in Access, run the saved export, confirm the file update, and verify the updated data in Excel.

Import and export XML documents in Microsoft Office Access 2010 Advanced. Explore working with XML documents in Access.

Export your Access table as SML using the External Data tab's export group, choosing to export data, structure, or both to a destination SML file, then complete the export wizard.

export xml dialog box lets you include data, schema, and presentation when exporting an Access object; choose the desired portions and click ok.

Use the Import XML dialog in Access to import structure only, or structure and data for a new table, or append to an existing table with matching XML format.

Export data and schema from a Microsoft Access table to an XML document, then import the XML into another database, selecting schema-only or structure and data options.

Export access data to excel with an xsl file to define presentation; select the table on the external data tab, choose xsl, specify name and location, and finish the wizard.

Export data to an XML file from Access, including the SSL stylesheet to define its formatting, then view the XML and stylesheet as a formatted HTML table.

Export data from your database to non-editable formats by publishing as pdf or xps, choose a location, configure optimization for printing or online publishing, and publish to create read-only outputs.

Export database objects as a non editable format by saving a table as an XPS file using save and publish, then open with the XP viewer for portable data sharing.

Learn to link access objects from one Access database to another. Learn to link access objects from one Access database to another.

Link a table across Access databases to reuse the same data without duplicating copies, so you can open, edit, and build queries, forms, and reports using the linked table.

Link to access tables across databases to view and edit shared data as if local, using the External Data tab to create linked tables and see changes in all databases.

Maintain access to linked tables by updating their paths when the source database moves. Use the External Data tab, linked table manager, and update.

Learn to use the linked table manager to update links for linked tables, browse via my network locations using UNC paths, and share databases without relying on mapped drives.

Update links when a linked database moves by using the linked table manager on the external data tab to browse to the new archive location and refresh the table.

Link an Excel workbook to an Access database as a one-way linked table; changes flow from Excel to Access, but updates do not propagate back.

Learn how to link an Excel workbook in Access 2010, creating a read-only linked table that reflects Excel data, with edits possible only in Excel and not in Access.

Explore how the hyperlink datatype in Access stores emails, websites, or network document paths and opens the default program when clicked, and create a hyperlink field in design view.

Demonstrates adding a hyperlink field in an Access table to store email addresses and invoke the link when clicked, including using design view to create a new field named hl_email.

Create hyperlinks by typing display text or using the insert hyperlink dialog box, set a screen tip, and choose a link type (file, web page, or email) before clicking ok.

Demonstrates inserting data into a hyperlink field in a data sheet view by adding an e-mail address in the first row's new email column and saving changes.

Modify hyperlink data by opening the table in datasheet view, editing the hyperlink column, or using edit hyperlink to open the dialog and apply changes with OK.

Edit a hyperlink field in Access to create friendly mailto links and update the display text. Add a screen tip and review the history of recently used addresses.

Import objects from access databases, excel, and text files; export to an access database; link objects for cross-database use; and manage sml documents with data, presentation, structure, and hyperlink fields.

Explore tools in Microsoft Access to optimize and maintain databases over time, protect the data file from theft, and configure database properties and usage options.

Explore the resources and tools inside Microsoft Access for optimizing our database in this topic.

Convert an Access 2010 database to older formats for compatibility. Open in Access 2010 and save as a legacy format, preserving the original while understanding feature loss.

Convert an Access 2010 database to a prior version using Save and Publish. Save as Access 2002-2003 to create a working copy while keeping the original in the newest format.

Use the performance analyzer in Microsoft Access to analyze your database structure and generate recommendations to improve performance, such as establishing relationships or splitting tables.

Run the performance analyzer to view results, apply optimized recommendations automatically when possible or manually implement fixes; rerun analysis to see updates and close the results when done.

Run the performance analyzer in Access 2010 to analyze the database and generate recommendations, then relate TBO product to TBO order and compile with option explicit and review relationships.

Split the database to improve performance in a multi-user environment by placing tables on a network back-end and forms, reports, and queries on the local front-end linked to the back-end.

Learn how to split Microsoft Access 2010 database into front end and back end on a shared network, using linked tables to reduce traffic and enable forms and reports.

Compact and repair in Microsoft Access reduces database file size by reclaiming unused space and fixes small errors; access the file tab, info page, and click compact and repair database.

Explore how Microsoft Access uses the compact and repair database utility to shrink the database, remove whitespace, and fix minor errors by running it periodically from the File tab.

back up the active database regularly using the file tab, save and publish, and backup database option; select location and name to restore data if needed.

Back up your Microsoft Access database from the file tab using save and publish, choose save database as, specify a location and file name, then save to create a backup.

Learn how to protect our database with a password to prevent unauthorized access in Microsoft Office Access 2010 advanced.

From the File tab, encrypt your Access database with a password and open it in exclusive mode to lock others out. Each open requires the password to protect your data.

Decrypt an encrypted Access 2010 database by opening it in exclusive mode, using the File tab to decrypt, entering the password, and saving the unencrypted backup for future opens.

Open the encrypted database in exclusive mode, decrypt it via the File tab info page, and save it without a password so it no longer prompts on reopen.

Learn how to set options, properties, and behaviors in a Microsoft Access database to customize its operations.

Open the access options dialog box to configure the current database, including its title, icon, opening form, restricting users to forms and reports, and compact on close.

Open the options in Access to customize startup behavior, set the startup display form, hide the navigation pane, adjust object designer error checks, and apply changes by reopening the database.

Discover how to manage database properties in Access 2010, using the general and custom tabs to store author, title, owner, project, and purpose.

Edit database properties to track subject matter, author, manager, and company on the Summary tab, then create and populate custom properties on the Custom tab, including a launch date.

Explore the tools in Microsoft Access to optimize and maintain databases, protect them with password protection, and configure options, properties, and behaviors for each database.

Advance your Access development with SQL queries, cross-tab and union queries, and macros; learn data import, export, linking, and essential database maintenance and protection.