Excel to Access: Intro to Microsoft Access for Excel Users

Compare Excel and Access to see how Access complements Excel rather than replaces it. Explore the navigation pane, tables, queries, forms, and reports that organize data in Access.

Discover why Access is worth it for Excel users: move from static visuals to a flexible database where you can filter orders by date and instantly recreate monthly reports.

Explore how Access handles data starting with tables, then use queries and reports to unlock power, import Excel data, and build forms with basic programming for practical database workflows.

Compare Excel tables and Access tables by exploring fields and records, and switch between tabbed and overlapping window views to understand navigation, record addition, and basic table concepts.

Learn how editing, adding, and deleting records in Access work like Excel but differ in cursor behavior, no formula bar, and automatic pencil marks during edits.

Sort data in Excel and Access tables, using single-column sorts (address or last name) and multi-level sorts by state then city with advanced filter sort, and remove sort.

Compare basic filters in Excel and Access, using select all, state and city filters, and number and date filters such as greater than 1000 and after January 1, 2013.

Explore advanced filters in Microsoft Access, including toggle filters, filter by form and the advanced filter sort grid, using wildcards and or logic to refine criteria.

Learn find and replace in excel and access using find and select and ctrl f or ctrl h, compare searching by rows, columns, and field, and consider update queries.

Create a new table in Access using table design, set a primary key with auto number, name fields without spaces, and define short text fields like first and last name.

Create a new Access table named my customer, add fields like short text, description, zip, phone, sales rep, website, credit limit, date added, wholesale, and notes; avoid reserved words.

Discover why numeric field sizes matter little today and apply using long integer for whole numbers and double for decimals to streamline table design.

Explore table properties by applying format and input mask to text fields like city and state, use the input mask wizard for phone numbers, and adjust currency and date formats.

Learn how table properties enforce defaults, required settings, and validation rules in Access, including default values, zero-length text handling, and a must be greater than 1000 credit limit rule.

Create a state lookup by building a state table and using a combo box with limit to list, then explore sales rep entry with a value list.

Modify table design with ease by adding, moving (via the record selector) and deleting fields like email and employees, and copy fields between tables using design view.

Learn to create and design reports in Access, understanding the report structure with page header, page footer, detail, and report header/footer, and how to compute totals inside the report.

Create a report with the report wizard in access by selecting fields, sorting by date and order number, and adjusting headers, footers, and margins for print preview.

Learn about access controls for forms and reports, focusing on label controls and text boxes, with a tour of other controls like buttons, combo boxes, list boxes, and print preview.

Explore report view and layout view in Access, and learn to resize and align header and detail controls by selecting the label and text box together.

Modify the report design by resizing the detail area and creating a customer name field from first and last names. Use selection and alignment techniques and preview with print preview.

Learn to enter formulas in Access text boxes to concatenate first and last names using square brackets, equal signs, ampersands, and a quoted space, then format and preview totals.

Explore sorting and subtotals in Access reports by adding groups for order date and order number, counting records, and displaying per-order and overall totals in headers and footers.

Apply conditional formatting in access reports to highlight values based on data, such as totals of 1000 or more or counts of ten or more turning bold and green.

Open the property sheet to reveal context-sensitive settings for the selected control, and adjust height, back color, visible, input mask, and event triggers for forms, reports, and queries.

Master the properties box to format controls, apply borders and lines, use F4 to show or hide it, and set conditional formatting based on the order subtotal.

Learn how select queries sit between tables and reports to pull only the data you want, transforming vast data into usable insights in Access.

Learn to use select queries with the select queries database, exploring customer, product, and order tables to generate reports for 2012–2014.

Learn to build select queries in Access using query design, base them on the customer table, and use the asterisk to pull all fields.

Sort data in Access queries using ascending or descending orders, and sort by state first by duplicating the state field as the leftmost sort field while keeping the display clean.

Learn how to use criteria in Access queries to filter records with and versus or logic, including example state and city criteria, quotes, and viewing results.

Learn how to use wildcards in Access queries, with the asterisk for any characters and the question mark for a single position, including practical examples with city and product codes.

Learn to use formulas in query criteria in Access, applying greater than, greater than or equal to, and between to filter product data (code, price, quantity) in design view.

Learn to perform calculations in Access select queries, rename column titles such as product code, create a total column from unit price and quantity, and apply criteria on the total.

Learn how to design a relational database in Access by linking customer, orders, and products tables with primary keys and order numbers, enabling clean queries.

Explore how parameter queries in Access use prompts inside square brackets to collect start and end dates, turning prompts into criteria in a date field like order date.

Use a parameter query to capture a start date and end date, and create a dynamic report title that shows the entered dates.

Review the report source table structure and build a four-table query using select queries, between criteria, and parameters to replace the August 2014 orders as the report's data source.

Replace the report's record source with the orders by date query, verify in print preview, and save the updated report.

Set up date parameters in the orders by date query using between criteria, start and end date prompts, and test with view to run a linked date-range report.

Finish the report by updating the title to show dates and adding a date text box with a start date and end date formula, and review queries for Excel import.

Explore three query types: unique value, top, and total, in Access, using unique values to return distinct records and sorting by state and city.

Explore on-the-fly lookups for the city field, using a distinct list in a combo box powered by SQL, with not null criteria and unique value queries to speed data entry.

Learn to use top queries in Access, including unique values vs unique records, and sort by date or subtotal to extract key records.

Create and analyze totals queries in Access to count customers by state, compute total and average orders, and apply 2014 criteria for targeted results.

Explore total queries in Access by grouping by customer ID and month, renaming columns with names followed by colon, and using month and year functions to count orders in 2014.

Execute an action query to create a permanent table from a query, using a make table query to generate a state list and enforce lookup constraints in design view.

Learn how to use an append query in Microsoft Access to add New Jersey customers to the customer table, reconciling field differences and concatenating address fields.

Learn to remove records in Microsoft Access with a delete action query, using a where clause to delete customer IDs greater than 13869, while backing up the table first.

Learn how to use an update query in Access to raise unit price by 5% on the products table, with a backup copy and quick verification.

Create a subtotal for each order by summing line totals (unit price times quantity), then update the orders table with the subtotal and the grand total including freight.

Create a macro to automate updating order totals by deleting the subtotal table, creating the subtotal with queries, and updating the totals in Access after importing from Excel.

Learn to handle common import problems when migrating from Excel to Access, including removing top headings and correctly using the first row as field names, with apostrophes for leading zeros.

Import excel data into access with the first row as column headings, diagnose import errors via the excel data import errors log, and correct bad data before re-import.

Learn how to import Excel data into Access, clean and reshape it, identify the need for a primary key to link customers and orders, and plan table structure in migration.

Learn to consolidate monthly Excel reports into a single dataset before importing into Access, ensuring consistent column headers and efficient table structure for a smooth migration.

Identify one to many relationships and structure your data into customers, orders, order products, products, and a lookup state table to design a scalable Access database.

Apply the rule that only duplication should be the hook, linking tables with key fields like customer ID and product code to preserve data integrity and enable accurate historical pricing.

Keep data as raw as possible, avoid storing calculated fields, and maintain a simple lookup table design to plan your database using update and total queries.

Use the database documenter to print table structures from Excel data, showing field names, data types, and sizes, with an optional compact half-page report for notes.

Visualize and connect tables with the relationships screen in Access to see your database structure. Create, edit, and remove links; optionally enforce referential integrity and print a relationship report.

Learn why I skip the relationships box in Access, emphasizing primary keys, enforced referential integrity, and using forms and VBA over background relationships.

Why you should avoid the analyze table wizard when converting Excel data to Access, due to misaligned fields, one-to-many relationship issues, and invalid generated codes.