From the course: Access 2019: Building Dashboards for Excel

Learning how queries, forms, and reports work with tables

From the course: Access 2019: Building Dashboards for Excel

Start my 1-month free trial

Learning how queries, forms, and reports work with tables

“

- [Instructor] I think it's important to understand the benefits of using Access but I think you also have to learn how the data is setup in your Access database. This is just going to help you work better and work faster. So let's learn how queries and reports work with our tables. Okay, let's go look at our database. Okay, we can go ahead and close my users and watches. And then from our navigation pane, let's go up and up the Login Data report. We look at this report and it has some of the information that we need for our upcoming charts. So we want to see where everything is at. We're going to do this by looking backwards from the report. I'm going to right click the login data tab and go to the design view. Let's take a look at the properties sheet. My properties sheet opens and shows me all the different properties for this report. And on the data tab, it tells me that my records source is the login data. So I know that if this report shows me information I need, then if I look at the record source, I can look and see where all this is contained in the tables. So I'll close my properties sheet and I'll close my login data. In queries, I see the login data. Let's right click and open that up. So I'll right click and choose design view. Here I can see the tables that are combined together, or tied together through a join of users and login. Can bring my grid up if necessary and see the fields that are available. So this tells me a lot about where my data is stored. I know I can go look at the DBO users table and also the login log table to find out more information. I'll go ahead and close that query. And I don't need to save any changes, I can say No. When you want to find how things are connected, you can reverse engineer them like that or you can do something like object dependencies. So for example, let me go ahead and click on login log, I'll go up to my Database Tools, and I'll choose Object Dependencies. It'll prompt me to go ahead and update the dependency information. I'll go ahead and click okay for that. And then on the right-hand side, I see the Object Dependencies pane. This tells me that I'm looking at the login log and I'm looking at the objects that depend on the DBO login log, which is, look, a query called Login Data. If I click objects that I depend on, it shows me what it depends on. And because it's a table, there are none. Okay, let's go ahead and take a look at users, and then I'll choose refresh on the dependencies. And then I'll choose objects that depend on me. And this shows me a slew of queries and forms and maybe even reports that use the Users table. This tells me it's a critical table and if I want to launch like login data, I can click that and it'll open up my query. You have to learn where the data is stored and it's always helpful when you can follow a tool to locate that data versus manually opening up everything and trying to figure it out from the front. Definitely look at your properties and use things like object dependencies. They're big time savers.

Contents