Access 101: What is a Query in Access?
Queries are how we get useful information from data stored in tables. They also let us make bulk changes to our data. ... Be careful with queries!
This article is part of my Quick Start Guide to Microsoft Access series, Access 101.
"Query" is another word for "question."
In a relational database like Access, we use queries to ask questions of our data, such as:
- What are my unpaid invoices?
- Who are our biggest customers?
- What was our net profit in the last quarter?
In addition to reading data with Select queries, we can also change data with action queries, such as the Append, Update, and Delete query types. These four types of queries comprise the most common database operations and are often referred to by the acronym CRUD:
- Create
- Read
- Update
- Delete
Experimenting with Queries: The QBE Window
The best way to learn the basics of queries in Access is to use the "Query by Example" (QBE) window:
Mike Wolfe
The QBE window lets you click and drag tables and other queries onto its canvas. You can then click and drag matching fields from one table to another to join them together.
For example, a properly designed Invoice table won't include the customer's name. Instead, it will contain a "foreign key" named CustomerID that matches the "primary key" field of the Customer table, also named CustomerID. The QBE window allows you to drag either field on top of the other to create a "JOIN."
This is a terrific learning tool as it lets you experiment with the power of queries without having to learn SQL syntax right away.
HOWEVER, the QBE window is intentionally limited. While this keeps the feature simpler to understand, there are certain types of queries that you can only create by writing raw SQL. Once you understand the basics of how queries work via the QBE window, you should make it a point to learn SQL as that is the only way to fully unlock the power of a relational database.
Set-Based Operations
Action queries are optimized to perform "set-based" operations.
Executing a single query can create, change, or delete millions of records. This ability is part of what makes relational database systems so powerful. This power is a double-edged sword, though.
With great power comes great responsibility.
Being able to change or delete millions of records also means that you can accidentally change or delete millions of records.
Development Data
The potential for causing widespread havoc is why it's important to do all of your development work on a development copy of the data.
This may seem like a rather advanced topic for an introductory article, but the fact is it's even more important as a beginner to make sure you are not experimenting on so-called "live" or "production" data. While large software development companies have automated workflows to manage separate environments for development, testing, quality assurance, and production, you don't need anything that complex.
If the production data is saved in an .mdb file, grab a copy of the data file, append the word "Dev" to the end of the filename, and relink all the tables in the front-end to the "Dev" copy of the database.
If the production data is stored in SQL Server, grab a backup of the database, restore it on a local development instance of SQL Server, append the word "Dev" to the restored database name, and relink all the tables in the front-end to the "Dev" copy of the database.
Mike Wolfe
If you are not sure how to do either of the above tasks, then by definition you do not know enough to be messing with anyone's production data. Grab a copy of the Northwind 2.0 Access template and play around in there instead.
And when you eventually feel frisky enough to make data changes directly to a production database, make sure you wear a big, blue foam cowboy hat to do it:
Mike Wolfe
