Join Adam Wilbert for an in-depth discussion in this video Understanding queries, part of Access 2010: Queries in Depth.
At the heart of every database is of course data, lots of data. Data tables can easily grow to hundreds, thousands or even millions of records. But trying to manage and makes sense of such large amounts of data can quickly become overwhelming. At some point, you are going to want to know, what does all of this data tell us? Luckily, Access provides a set of objects called queries to help us out. Basically, running a query is simply asking a question. Who are my top customers? How many products did we sell last month? You ask a question and a query will return the answer. At its most basic level, learning queries is less about getting the right answer and more about learning how to ask the right questions.
So, let's suppose that we are preparing an e-mail newsletter. The first thing we want to ask ourselves is, who are we sending them to. I am going to go ahead and open up the Chapter 1 group in my custom navigation pane. Let's take a look at our Direct Customers table by double clicking on it. Here, we can see that we have a field called Email address and that's exactly what we will need, but we actually don't need all of this other information about their physical address. Let's go ahead and run a query that's based off of this table. I am going to double-click on qry_CustomerEmail. This query takes the FirstName, LastName, and Email fields from our Direct Customers table.
Now, query results look like tables but they are actually called record sets. This is an important concept in Access, that queries do not store data; they merely store the instructions on how to assemble the data from you tables. This way, queries are always up-to-date without you having to maintain the same information in two or more places. Let's go ahead and go into our DirectCustomers table and make a quick change. For Salvador Garrison, I am going to change his email address here. And I will just highlight the first part and type in Garrison and we will move off of that record.
Let's go back into qry_CustomerEmail and we will see that change is updated. Now, let's take our example to the next step. Instead of just emailing everybody, we only want to target people in the southern region. If we go in to our DirectCustomers table, we will see that we have information about state but there is no information about what region those states belong to. If we open up our States table, we will see that we can relate states to regions here. So, let's look at one example. Our Salvador Garrison lives in the state of Oklahoma. If we look at our States table and find Oklahoma in the list, here it is, we could see that Oklahoma is in the southern region.
Now, this will be a little bit tedious if we had to go through and do all thousand customers this way. We can use a query to streamline that process. Let's go ahead and open up CustomerEmailandRegion. This query takes the same information as the previous, FirstName, LastName, Email, but it also adds two fields from the table states. RegionName and DivisionName. Now, we can go ahead and sort this. Let's sort it A-Z and then scroll down til we find our southern states, and these are the customers that we are going to be targeting in our email campaign.
But again, this query is returning too much information. We don't need the Northeast or Midwest customers. So, let's take this a step further and run one more query. CustomerEmailandSouthernRegion. This query is filtered to just give us the southern states. We could see that we have 297 customers that will be getting our email campaign. So, while using filters on your tables would be a quick and dirty way to get insight into some aspect of your data, queries will often work out better for you in the long run. The ability to save and rerun a query is just one of the many advantages that we will be exploring throughout this title.
As we'll see, queries will be able to provide answers to some pretty complex questions and of course we only need to learn how to ask the right questions first.
- Naming conventions and best practices
- Working with joins
- Using comparison operators
- Defining criteria for select queries
- Creating parameter queries
- Creating calculated fields
- Working with dates and times
- Using the Expression Builder
- Creating conditional statements
- Making, deleting and appending records
- Building reports