Access 2007 Intermediate

Explore the three types of relationships in relational databases—one-to-one, one-to-many, and many-to-many—in Microsoft Access, with examples like employee data and 401k info, plus how to set up relationships.

Explore how to enforce referential integrity in Microsoft Access by creating one-to-one and one-to-many relationships, enabling cascading updates and deletes between primary keys like employee ID and order ID.

Learn to modify relationships in Access and enforce referential integrity with one-to-many and many-to-many links, using a third table like orders details, and test unmatched records via queries.

Solidify your Access 2007 table design by configuring field descriptions, which guide data entry, display in the status bar, and carry over to forms for users.

Explore how field properties control options for auto number, text, and number fields in access 2007, and how to switch between upper and lower panes to edit them.

Explore the practical use of text-based field types in Access 2007, highlighting text fields as alphanumeric inputs capped at 255 characters and when to choose memo fields for extensive notes.

Learn how Access 2007 handles numeric field types, including text for telephone numbers and zip codes, date/time validation with calendar, currency with decimals and symbol, auto number, and yes/no booleans.

Discover how to use other field types in Access 2007, including OLE objects and hyperlinks, which convert web addresses to hyperlinks and email addresses to Outlook-ready messages.

Discover how to set field size and apply display formats in Access 2007, changing uppercase, lowercase, and phone-number hyphen formats without altering stored data.

Enforce data entry formats in Access 2007 with input masks, using the wizard to apply phone-style patterns, customize placeholders, and choose whether to store digits with or without formatting.

Discover how captions let you show the user what they see by using one-word field names with no spaces, which simplifies queries and data display in Access 2007.

Set default values in Access 2007 to streamline data entry by prepopulating fields like Massachusetts, and use reserved words like now to auto-fill date and time.

Enforce data integrity in Access 2007 by configuring a validation rule and validation text for expiration dates, ensuring the date is greater than 60 days.

Master the Access 2007 required property to enforce required fields, understand validation text, and handle error messages that reference table and field names, delivering a reliable data entry experience.

Learn how indexes enable fast lookups and data validation, from primary keys to unique constraints. See credit card numbers and last names as examples of when duplicates are allowed.

Learn how unicode compression in Access 2007 intermediate reduces per-character storage from two bytes to one, dramatically shrinking large databases (e.g., 400,000 records) and lowering overall size.

Use queries in Access 2007 to avoid manual edits and flexibly format names. Store first name, last name, salutation, and middle initial in lowest common denominator fields.

Learn to concatenate text in Access 2007 by joining first and last names with a space, using square brackets for spaced field names, and applying the left function.

Learn to calculate totals in an Access 2007 intermediate query by multiplying quantity by unit price, formatting as currency, and viewing grand totals by order or product.

Create a multi-table query with calculations to show total sales by customer, linking customers, orders, and order details to identify the top three customers who spend the most.

Link three tables—customers, orders, order details—using proper joins and referential integrity to build a multi-table query that creates a calculated name field, shows products, quantities, unit prices, and order total.

Explore grouping by customer totals in Access 2007, counting items, calculating averages, and building calculated fields via relational links between customers, orders, and products.

Use a base query to calculate order subtotal plus tax in Access 2007, then reference those results in a second query as the data source to compute order total.

Learn to fix where clause errors in Access 2007 by correcting aggregate usage and calculated fields, adjusting group by expressions, and yielding accurate customer totals, averages, and orders.

Calculate the top five customers by sales in Access 2007 using a query, showing dynamic results as new customers spend and alter the rankings.

Understand action queries in Access 2007, including select, make table, append, update, cross-tab, and delete queries. See how they retrieve, create, or modify data and why some can delete records.

Learn to consolidate similar data into a single table using make table and append queries, ensuring field alignment and accurate quarter data integration.

Use update queries to mass-change data with criteria and wildcards, such as standardizing job titles and two-letter state codes. Learn about referential integrity and joins to identify orphan records.

Create delete queries to remove entire records and fix orphaned order details by matching order IDs. Use outer joins to identify problems, enforce referential integrity, and maintain a one-to-many relationship.

Explore parameter queries in Access 2007 that prompt users for criteria instead of using hard-coded values. Learn to create prompts, view results without design view, and understand common idiosyncrasies.

Learn to create a simple parameter query in Access 2007 by using a state criterion with square brackets, prompting 'Please enter the state to search' and returning matching customers.

Use parameter queries with wildcards in Access 2007 to build like statements, enabling dynamic searches by title or name with star-based text matching.

Learn to find date ranges in Access 2007 by using a between statement on order dates and converting it to a parameter query, with user prompts and basic validation.

Create multi-parameter queries in Access 2007 using between with date and status parameters, prompting users for input and using square bracket syntax for a professional, user-friendly result.

Explore advanced access forms with calculated fields for on-the-fly calculations, hidden controls that lock editing, date calculations from date of birth, and turning emails into hyperlinks.

Explore a hidden table behind Access 2007 forms and how resizing a field affects controls. Release controls, use arrange to change layouts, and remove the table to make controls independent.

In design view, select multiple controls and range to the narrowest width. Automatically adjusts to widest, aligning fields such as mobile phone number and fax number for a clean layout.

Create a bound text box in Access 2007 to automatically calculate age from date of birth using an equals sign, date minus birth, and division by 365.

Learn how to set and format control properties in Access 2007, including naming conventions, control sources for calculated ages, formatting, and enabling or locking controls.

Create a form with an order details subform using referential integrity. Use the control wizard to turn the customer id into a combo box that looks up customers.

Create a calculated control in Access 2007 to show the days between ship date and order date using an unbound text box with the ship date minus order date formula.

Add and format labels in access 2007 forms, fix label associations, adjust borders, create a calculated shift time control with tax, and populate via a combo box using control wizard.

Learn to build a customer report with the Access 2007 report wizard, select fields, wrap by state, sort, preview, and bind a parameter query to reuse a single input.

Create a calculated field to concatenate last name and first name in an Access 2007 report, converting a bound control to an unbound one with a valid name.

Create calculated controls and fields in Access, use now to display current date and time, format dates with text, and employ page and pages to count report pages.

Learn to set a report's control source to a parameter query and create a dynamic title, then use like with wildcards to show all records when criteria are blank.

Access 2007 intermediate uses report bands to generate top-down reports, featuring a report header, page header, state grouping, detail data, and a footer with totals.

Explore how to use the group and sort panel in Access reports, add totals or counts, and configure parameter queries to drive report results.