Join Adam Wilbert for an in-depth discussion in this video Query basics, part of Access 2007: Queries in Depth.
The data in our database can become overwhelming with tables growing to thousands of records easily. Luckily, Access provides us with a set of objects called queries that we can use to quickly filter out and get to the information that we're interested in. But before we can get into making queries, we need to understand the data that we have available to work with. Now if you're inheriting a database like we are today, you want to go through all of the tables to see what's in them. If you have been working with your database from scratch, you are likely to be intimately familiar with the data that's in there. But for now we are going to go ahead and go through our tables and see what we have to work with. Right now I am going to open up our tbl_DirectCustomers table and we will see that this table contains some information about all of the customers that have done business with the Two Trees Olive Oil Company.
We have got our FirstName, our LastName, phone number, some contact information, their email address. On the far right side, we have a Newsletter column that simply tells us whether they've subscribed to our online newsletter or not. I can go ahead and close this table and then open up the Employees table, and we will see that we have some similar information about the employees that work for the Two Trees Olive Oil Company. We have got FirstName, MiddleName, LastName, contact information and email address, and scrolling right, we can also see some information about when they're hired, the rate of pay that they currently receive, and the department that they work in.
I am going to close this and go to our Orders table. The Orders table has an OrderID, and this is a unique ID for each order that's placed. We have got the date that the order was placed, an ID number that links to customer that placed the order, an ID number that links to the product that was ordered, and an ID number that links to the employee that assisted them with the order. I can close this and open up our Products table and we will see those ProductIDs that we just saw in the Orders table. We can also see what the name of that product is, the code for the specific oil that's in the product, the size of the product in ounces, the wholesale cost, the retail cost for the customer, and then we have some information about how much that's marked up.
So we have got the markup in dollars and the markup in percent. I am going to go ahead and close the Products table. There are mainly four tables in our database give us some lookup information about some of our other products. So if I open up the GradeID, we will see that we have a numerical code for the grade and then we have the text that code represents. Similarly for states, if I open that up, we see we have a two-digit abbreviation for the states, and then the text that represents. I am going to go ahead and close that. Now, we can see the relationships between all of the tables in our database by going up to Database Tools, and then clicking on Relationships, and this will open up the Relationships grid, where we can see all of our tables laid out and how they relate to one another.
So we can take a look at the Orders table here and moving to the left we can see that the order is linked to a product that was ordered. The products are linked to the OilID, and the Size, and also the Grade. Moving right from the Orders table, we see the customer and the employees that are associated with that order, and we also have a lookup table for states that feeds information into both our customers and our employees. So let me go ahead and close our Relationships window and we are going to take a look at how we can get some more information out of our Employees table now. So I am going to open up our Employees table again and if you're used to working in Excel, you might be familiar with some of the filtering options that are in Excel.
For instance I can click on Illinois here, IL, and in Access if I right-click on that and then say Equals 'IL', it will filter down to all of my employees that live in the state of Illinois, and you can see on the very bottom here we have got four employees. If I now left-click on that icon, you will see that we have got a list of all the states that are represented in our database for the Employees table and you will see that Illinois is currently selected. I can turn on another state, for instance Kansas, KS, and if I go ahead and say OK to that, it will show me all the employees that either live in Illinois or Kansas. left-clicking again, I can scroll through the list and I will turn off Illinois and now I am just down to Kansas, and you can see that I have got total of three employees that live in the state of Kansas.
Going through the Filter menu can be a little bit tedious and you see that if I go ahead and close our table now with the filter in place, it's going to ask me if I want to save the table. And I can go ahead and say Yes. Now, if I go ahead and open up the Employees table again, you will notice that our filter is no longer applied. In the Sort & Filter options on our Ribbon here, we have got a button here called Toggle Filter. You will notice that if I press that to toggle it back on, it returns to the state where Kansas is selected. But it no longer gives us any information about state of Illinois, and you can't go back-and-forth. You can only save one filter at a time. The better way to do this is to go through a query.
So let me go ahead and show you one of our queries that we've got set up right now. Under queries, I have got a qry_ EmployeesAZ and if I double-click on that, you will see that it gives me just the information for the employees live in Arizona. I have also got one for New York. I can go ahead and save as many of these queries as I want. It will give us information about just the state that we are interested in. Now, every time you run a query, Access creates what's called a record set and basically what a record set is, is a sub-selection of your main table. So every time I run this Employees New York query, Access goes back to our Employees table, grabs all of the employees that live in New York, for instance let me remove this filter and switch to New York, and you'll see that we have our three employees that live in New York, which are the same three employees that's being returned by this query.
Every time you run a query, Access goes back to the Employees table and pulls the most current information. Now, in our database, we also have a form called EmployeeLookup and this is a little more advanced way of running a query and we will get to this later in this course. Basically if we were to run this form, we could select from a list of states, and that will provide us all of the available options instead of having a single query listed out for Arizona, New York, and every state possible. So you don't need to have 50 queries to do a single task. So while at their most basic level a query is simply a sub-selection of a table -- we can get some of that information from running a filter on our table -- a query is definitely a much more robust way to go.
We can save our queries out, and we can return to them at anytime. As we will see throughout the course, queries can also do some more complex tasks that you can't simply do with a filter.
- Naming conventions and best practices
- Working with joins and primary keys
- Using comparison operators
- Printing query results
- Creating parameter queries
- Creating calculated fields
- Using the Expression Builder
- Making conditional statements
- Appending queries
- Updating queries