Join Adam Wilbert for an in-depth discussion in this video Introducing the database, part of Access 2013: Queries in Depth.
This course focuses primarily on queries, which are one of Microsoft Access's five main object types. In order to get the most out of this course, you should have a basic working knowledge of the other four types, namely tables, forms, reports, and macros. Now, there's no need to be an expert in their application, but you should feel comfortable knowing what each object is and what they're used for. Throughout this course, we'll be working with a database created for a company called Kinetico, which specializes in alternative energy products. When jumping into a new database, it's always a great idea to take a few minutes to review the data tables and relationships, in order to familiarize yourself with the contents that you'll be working with.
Now when you first load up an Access database, you might see this yellow security warning up here at the top of the screen. We can press the Enable Content button to allow any code or macros that are present in the database to run. But instead of doing that, what I'd like to do here at the beginning of the course, is set up a trusted location for our exercise files so that we don't have to enable content on every single exercise file moving forward. In order to do that, we'll first go up to the File tab up here, and then we'll come down to Options on the bottom. In the Options screen, I'm going to go over to Trust Center here at the bottom. And then way over here on the right, I'll click Trust Center Settings.
On the next window that opens up, I'm going to come over here to Trusted Locations. This will allow me to set a trusted location on my hard drive. To do that, we'll come down here to the Add New Location button, and I'll click Browse, to browse out to the location of my exercise folder. For me that's on the desktop, and if I scroll down, I get to my exercise folder there. I'll go ahead and say OK to select it, and then I'm going to make sure that I check this box here that says, Subfolders of this location are also trusted. That'll allow Access to trust any files that are within the Exercise Files folder.
We can also add a description here, so that later on we can remember why we created this trusted location, and then it's temporary just for this course. I'll type in lynda.com Access 2013, Queries In Depth. Go ahead and say OK, and that location will get added up here to your trusted locations. And you can see the description here and the date that you added it. Let's go ahead and say OK and back out of the Option screen. Now if I close the database and reopen it, you'll notice that we no longer get that yellow security warning across the top of the screen. Now in the navigation pane, you'll notice that we have several data tables that have all ready been established and hold our information about Kinetico.
To see what these are, let's start with the database's relationship view. We can get to that by going up to Database Tools and then clicking on Relationships here. That'll show you all of the tables within the database and how they relate to one another. In this particular database, we're tracking products that Kinetico offers and any orders that any customers are placing for those products. We also have a States table, that's connected to our Customers table. It'ill give us additional information about the state that they're from. For instance, the region and the division. We additionally have a table about our employees, and each order is attached to a single employee.
Let's go ahead and take a look at some of the data tables. I'm going to go ahead and close the Relationship screen here and open up the Customers table. Within the Customers table, we have columns for the customer ID, their first and last name, we have some address information and phone numbers, and we also have an email field over here on the far right. Let's go ahead and close this table, and take a look at Employees. For Employees, we have similar information. We have an employee identification ID number, we have a first and last name, a building and department where they work, their current status and hire date, and whether they belong to any of our dental, medical, or retirement benefits.
If I scroll over here to the right, we can see their current salary, and their current job rating. Let's go ahead and close the Employees table. The Orders table over here, maps customers, employees, and the products that they ordered. So, I have an ID number for the order itself. I have the date that the order was placed. I have the ID number of the customer that placed the order, and the ID number of the salesperson that placed the order. I also have an ID number that attaches to the product, and we have the quantity of products ordered. If I go ahead and open up the Products table, we can see what those product IDs refer to.
We have various product categories here. We have the name of the specific product and then we have some details about that product. For instance, if I scroll over here to the right, we can see the power in watts that, that product generates. It's footprint or it's size, it's manufacturing cost, it's suggested retail cost to the customer. We have the product line that the product belongs to. And then we have whether it's targeting a domestic customer, a corporate client, or a personal customer. Let's go ahead and close out the Products table. And finally, we'll take a look at the States table. The States table is kind of a lookup table.
It maps the two-letter state abbreviation that appears in the Customers table to the full name of the state, the region that, that state belongs to and the division within that region. So, those are the tables that hold all of the data that we'll be working with throughout this course. So, that was just a quick overview of the Access 2013 environment. And the data within the Kinetico database that we'll be using throughout this course. If you need a deeper review of some of the core components of an Access database, check out my Access 2013 Essential Training course, available here in the Lynda.com training library. Now that we're a little more familiar with the data that we'll be working with, we can start turning that data into information, with queries
- Defining criteria
- Understanding comparison operators
- Using joins
- Creating parameter queries
- Using Expression Builder to work with functions
- Working with dates and times
- Creating conditional statements
- Finding duplicate records
- Creating backups
- Making, deleting, and appending records
- Understanding SQL basics and writing SQL queries
- Useful query tricks