Download a PDF of this page here.

Download the Spanish version here.

Essential Concepts

Importing and Exporting Data

External Data refers to data that is stored outside of your current Access database. This can include data from other databases, Excel spreadsheets, text files, or even online sources.

Linking vs. Importing:

• Linking: When you link external data, you create a connection to the data source without actually bringing the data into your database. The advantage is that you can always have up-to-date data without needing to re-import it. However, linked data may be slower to access and is dependent on the availability of the external source.
• Importing: This involves copying data from an external source into your Access database. Once imported, the data is independent of the original source and can be fully managed within Access. This is useful for situations where you need to work with a static snapshot of the data.

Common External Data Sources include:

• Excel Spreadsheets
• Text Files (CSV, TXT)
• Other Access Databases
• SQL Server
• SharePoint

Data Integrity: Always verify that the imported or linked data is accurate and properly formatted before using it in your database.

Queries

A query in Microsoft Access is a powerful tool used to retrieve specific information from your database. It allows you to ask questions of your data and get precise answers, which is essential for data analysis and reporting. Queries can be simple, retrieving all records from a table, or complex, involving multiple criteria, calculations, and data sources.

Criteria can include:

• Date Ranges: Find records within a specific period.
• Text Patterns: Match records based on specific text entries.
• Multiple Conditions: Use operators like AND, OR, and BETWEEN to combine conditions and refine your results.

Forms and Reports

A form in Microsoft Access is a user-friendly interface that allows you to interact with your database data. Forms simplify data entry, editing, and viewing, making the process more intuitive, especially for users who may not be familiar with database structures.

Forms can be bound (connected to a data source like a table or query) or unbound (used for navigation, collecting inputs, or performing tasks without direct data entry).

To create a form:

1. Select the Data Source: Start by choosing the table or query that you want the form to be based on.
2. Use Form Tools: Access provides various tools, such as the Form Wizard, to help you quickly generate a basic form.
3. Customization: After creating the form, you can adjust its layout and design to suit your needs.

Controls are elements like text boxes, buttons, and combo boxes that allow users to interact with the form. Controls can be bound to data sources, enabling direct interaction with database fields, or unbound, serving other functions like navigation.

Common controls include:

• Text Boxes: For data entry or display.
• Combo Boxes: For drop-down lists.
• Buttons: For executing commands like saving records or running queries.

When working with forms there are a few different views you can use:

• Layout View: Allows you to design and adjust the form’s layout while viewing live data. This view is useful for making quick changes to the form’s appearance.
• Design View: Provides a more detailed environment for modifying the form’s structure, including adding new controls, adjusting properties, and setting behaviors.
• Form View: The final product that users interact with, where data can be entered, viewed, and modified.

Finally, a report in Access is used to format, summarize, and present data in a printable format. Reports are ideal for generating professional-looking documents that can be shared or printed. Reports can be based on tables, queries, or other forms of data, and are often used for creating summaries, invoices, labels, and more

Analyzing Data

Data transformation in Microsoft Access involves changing the format, structure, or values of data to make it more suitable for analysis or reporting. This process can include actions like sorting, filtering, or converting data types.

Common data transformations include:

• Formatting: Adjusting how data is displayed (e.g., converting dates to a specific format).
• Aggregation: Summarizing data by grouping it and performing calculations like totals or averages.
• Cleaning: Removing duplicates or correcting errors in data to ensure accuracy.

Calculations in Access allow you to perform mathematical operations on your data directly within queries, forms, or reports. This can include simple arithmetic like addition and subtraction, or more complex functions like averages and percentages.

Conditional analysis involves applying conditions or criteria to your data to filter results or perform specific actions based on certain conditions. This allows you to focus on subsets of data that meet particular criteria.

A subquery is a query nested within another query, allowing you to perform more complex data retrieval operations. Subqueries can be used to filter results based on the output of another query or to calculate aggregated values within a main query.

Types of subqueries include:

• Single-Value Subqueries: Return a single value to be used in the main query.
• Multiple-Value Subqueries: Return a set of values that the main query can reference.

Domain aggregation involves summarizing or performing calculations on a defined set of records within a database. Functions like DCount, DSum, DAvg, etc., are used to perform these operations over a specified domain or range of data.

Glossary

Aggregate Functions
Functions that perform calculations on a set of values and return a single value. Common aggregate functions include SUM, COUNT, AVG (average), MIN, and MAX.

Bound Form
A form in Access that is directly linked to a specific data source, such as a table or query. When data is entered into a bound form, it is automatically saved to the data source.

Business Intelligence
The process of collecting, analyzing, and presenting business data to help make informed business decisions. In Access, this often involves using queries, reports, and forms to analyze data. There are twelve aggregate functions:

• DSum
• DAvg
• DCount
• DLookup
• DMin and DMax
• DFirst and DLast
• DStDev
• DStDevP
• DVar
• DvarP

All functions have the same structure:

FunctionName( “ [Field Name] “,” [Dataset Name] “, “[Criteria] “ )

Glossary

clean

the process of identifying and correcting errors or inconsistencies in data; this is often done before data analysis to ensure accuracy and reliability.

database designer

a tool or individual responsible for designing the structure of a database, including tables, relationships, and queries.

Design View (for forms)

a view in Access that allows users to create and modify the structure and layout of forms; users can add controls, adjust properties, and set the behavior of the form.

domain aggregation

a technique used to summarize or aggregate data across a defined set of records, often involving specific criteria or filters.

exporting

the process of transferring data from an Access database to an external file or application, such as a csv file, excel spreadsheet, or another database system.

form

a database object in Access used to enter, edit, and view data in a user-friendly format; forms can be bound to data sources or unbound.

importing

the process of bringing data from an external source into an Access database; this could involve importing data from a spreadsheet, text file, or another database.

Layout View (for forms)

a view in Access that allows users to design and modify forms while simultaneously viewing live data; it provides a flexible way to adjust the layout of form controls.

linking

the process of connecting an Access database to an external data source, such as another database or spreadsheet, without importing the data; changes in the external data source are reflected in the Access database.

parameter query

a type of query in Access that prompts the user to enter specific criteria or parameters each time the query is run; this allows for more dynamic and flexible queries.

query

a database object in Access used to retrieve, filter, and manipulate data from one or more tables. queries can also be used to update, delete, or append data.

Query Wizard

a tool in Access that guides users through the process of creating a query; the wizard provides step-by-step instructions and options for building different types of queries.

report

a database object in Access used to format, summarize, and present data in a printable layout; reports are often used to create professional-looking documents for analysis and presentation.

scrubbing

the process of cleaning and preparing data for analysis by removing or correcting inaccurate, incomplete, or irrelevant data.

Structured Query Language (SQL)

a programming language used to manage and manipulate relational databases. sql is used in Access to write queries that retrieve, insert, update, or delete data.

subquery

a query nested within another query. subqueries are used to perform more complex data retrieval and filtering operations within a main query.

transforming data

the process of changing or converting data from one format or structure to another, often as part of data preparation for analysis or reporting.

Unbound Form

a form in Access that is not linked to a specific data source. Unbound Forms are often used for navigation, custom dialog boxes, or to collect user input that is processed in other ways.