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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.