Introduction to Tables, Queries, Forms & Reports in Microsoft Access

Kirt Kershaw
A free video tutorial from Kirt Kershaw
Microsoft Certified Trainer - Training Microsoft Since 2000!
4.5 instructor rating • 15 courses • 16,833 students

Lecture description

Access 2016 training video on the basics of the four major objects in Access including: Table, Query, Form and Report.

Learn more from the full course

Master Microsoft Access - Access from Beginner to Advanced

Develop dynamic Access databases fast in this easy to learn Microsoft Access Course (2010, 2013, 2016, 2019 & 365)

17:00:20 of on-demand video • Updated April 2021

  • Build a solid understanding on the Basics of Microsoft Access
  • How to create an Access Database from scratch
  • Master Microsoft Access from Beginner to Advanced
  • Create Tables to store and organize all raw data
  • Use a Query to search and retrieve data fast
  • Enter and view records in a more organized layout using Forms
  • Generate very detailed, professional looking Reports
  • Taught by Certified Microsoft Office Access 2000, 2002, 2003, 2007, 2010, 2013 & 2016 Specialist with over 18 years of Access training
English [Auto] Before we learn how to create a database from scratch which is what we're going to be doing the next training video. I want to briefly introduce you to the four objects we've been talking so much about. And here they are again as we've seen in previous training videos. You've got your tables queries forms reports. But now I want to open them up and basically when you open up any one of these objects you're going to have two views. Some you'll have more and others you'll just have to. And basically the views I want to show you is the front end view like for data entry for forms queries and tables. And then the design view. So you can set up and design your tables queries forms reports. So for example like the books table double click it didn't create itself or design itself meaning that well you create the table and you save the name of it and call it books but what about the fields you have to set those up. And one way you can do that is just by coming over here where it says click to add and type in the fields name. The other way is you can go ahead and go to the design view flip the view here from the front end to the back end by coming up here on the Home tab to the views group and you see the pencil ruler in the triangle. Well those are design instruments so click on it and we're in the design view because it's got its related contextual Design tab. You can see down below We've got our field names. You can type in here and then set the data type or type of data that you want to be entered into that field or displayed. Like when you type in a number it'll display in currency. So that's an oversimplification of the design. And I don't want to dwell on it too much because we'll go over it more in greater detail in the later training video. So we don't get overwhelmed here. Let's go back to the front end view the data sheet view by coming up here on the Design tab to the views group and clicking on the data sheet button there. And we're back to the front end you know on the front end view once you have your field set up you can start entering it in records or the data for that field and then for the title and then for the book price which when combined all together equals one record and you can see down below in the navigation record bar that if you want to go from the first record to the second record go and click on next and it goes to the next record that says you're on record 2 out of how many. Ten. And you can do that you go to the next one you can go to the previous one or you can go to the first record or go to the last record or if you want to go to record number six. Just go ahead and click in that box there. Delete what's there. Type in six hit enter and boom takes me right to it. And then if I want to enter a new record I need to go into the last blank row here and just click there and then I can just start typing in the book number title and book price and then I'll save. But what if you're like in record. What's that one. Number six out of a thousand. I don't want to be scrolling and scrolling and scrolling to get to the last blank line here to enter a new record. Oh that'll be annoying. Instead just come down here and click on that last button new blank record. Click on it takes you right to it then just start typing. And then if you want to search for record you'd come down blue on the search box. Click in it and then type in let's say car to oh there we go. Found at first one cartoon's out of. Well that's only one cool SLAs the tables. More specifically the book table next queries and queries are based upon the data found in the books table. And why would you want to query. Well the basic things that it can do is that you can say instead of seeing all the fields in the books table let's say you had maybe a dozen fields here or 20 but you just want to focus on maybe the book title and book price. You don't need to pull up everything else that would come up with that table and then sort through it and scroll over here to scroll back again if you had a dozen fields or columns here. Each column represented a field here that you want to scroll through. Well go ahead and create a query. And that's one way to use a query but more specifically it's to be able to set in criteria to filter through all this information here from the table. Like maybe if you just want to see only those book numbers that begin with CA and that book prices greater than $12 Well you can set that up in queries too. So let's go ahead and take a look at it. We've got the book's query and that should be an indicator that it's based upon the book's table. But how do you find out. Well let's double click to open it up and let's see if we can do some deductive reasoning. Book number and title that comes from the book number and title in the books table. Let's go back to the query here and like all access objects it's got a design view which you can come a pure on the home tab again and click on the design button and it takes you right to it and hey there's the table right there with all the fields from it. And then what we want to see in the query we just go ahead and pull it down into the grid below and that's what's going to pull in not everything we get a pick and choose Oh yeah and then down below you've got the criteria row that you can go ahead and type in. Well I just want to see the book number that begins with A and maybe all the titles that begin with the letter a new case will go over that in greater detail and later training video. So let's keep it simple and go back to the data sheet view come up here from the Design tab in the results group and click on the data sheet button. So now that you're familiar with flipping between views by clicking on the design view data sheet view design view. You can also right click on the tabs and also go to design or data sheet. Well there are sequel but we're not going to be covering that. I want to keep it to the basics here. So is for the query. And what you're doing here as far as updating any information or adding new records because it's based upon the books table it will update that as well. So if I add a record it allowed it to the books table if I edit the record and I change the number here from 4 1 2 to 4 1 3 it will go into the books table and update the 4 1 2 to 4 1 3 as well. So you want to be careful what you do here is not just for being able to swear between data but it also helps that once you break it down into those things that you just want to see from the books table that you can quickly make edits there and not have to well go back to the books table and scroll through everything to make an edit over here or edit over here if you have a dozen fields or more or even six fields is quite a bit for me. In any case there we go. And then we have forms. Now we've got a form that that is based upon books. So let's double click to open it up and it's front end view and a form is a way to view your data from the table because it's based upon again the table the data in the books table and the form allows you to control how you want to view it as opposed to a query or just the books because those are in grids or spreadsheets. This way you can go ahead and say I don't want it from left to right. I want the book number here and you can actually say well I want the title over here. Maybe the book price down here. In any case you can go ahead and control how the fields are being viewed and what's going to be viewed first and right here it's going to be the book number fall by the title and then the book price and then below that we have the companies that ordered this book. And right now I just have one company the customer number here which is siestas dash 2 I could fix that and also include the customer name if I wanted to. But in order for me to have this subform here that ties it to the books we all have to create a relationship as we talked about because a relationship between somebody who bought the book is going to be well there's the books and you got the customers. And we'll talk about how we can connect those and relate them. So when somebody buys a book like if they called up right now and said hey I want to go ahead and buy your book here drawing cartoons. Well I can come down here into the new record and type in the customers number or their name if I have their name. And I wasn't going by their number but you can go ahead and design that however you want and it will make more sense probably as we continue on because remember access is a process when it comes to learning. I wouldn't watch just the first couple of training videos or skip them because they're all layered one on top of another. And you learn a lot and hopefully you don't skip. But in any case you go ahead and type in the customers name number and all the information there save it. Now we have two customers that have purchased this book cool. And again how do you know based upon the book's table. Well deductive reasoning would show you that but if you really need solid evidence then go ahead and plus come up here and right click on the tab for form and go to the design view. And that's what the design looks like. It's a grid here that you can go ahead and use these little dots here to align them just so in your form and when you go to the design view. I mean you can select and move these fields around. But I digress. We'll cover that later train video. But let me come up here because we're in the design view on the contest show related design tab and go to the tools group and open up the property sheet. Now on the property sheet I've got the properties selected for what I have selected here but in the Properties for the entire form. So I'll come up here and click in that square right there. So it says OK what are the properties for the entire form. Where are the properties that I want to find out is where is the record source. Where is the data coming from. And you can see over here it's the books and we've got the prefix TBO. Now why would you want to go ahead and put prefixes in front of your tables queries forms reports. Well when it comes to the back end view here this is one reason is because when you click on the dropdown arrow and you want to basis form upon a table like the books table and not the books query you see how if I didn't have the prefix Q R Y or TBO I'd have two books all any got to work because then I have to go Hmm I wonder which is which. So when it comes to naming your tables queries forms reports. It's a good idea to have your naming convention here with the three letter prefix. I don't do it because I don't name in the same way when I create a query. I'll type in something that I'll remember as a query but it's up to you but by default it's what they recommend So you don't get confused as you can see over here if they have the same names which ones the table which ones the books that you want to base this form upon. So let's go ahead and close out of here and go back to the form view which you can right click go to form view and there you go. And you can go ahead and view the current record that you're on which down below. There you go you got your record navigation bars so there's one two three. And of course you can do everything that I just went over in the tables. And also you can do the same thing in the queries mess with the navigation bar down below takes this right directorates 6 you can go ahead and click on new blank record for the books here and go ahead and type in a new book number book title and book price. So there's the forms. Last but not least we got two reports So let's go ahead and double click and open that up opens up in the What view is this. If you're not sure view you're in you can do well a couple things you can come up there on the Home tab to the views group of course and then go ahead and click on the drop down arrow and whatever's highlighted. That's the view that you're in. Well let me hover off of it. You can see the report view icon is highlighted. So we're in the report view but of her want to print it off forget the print preview so I can see what it looks like before I click on the print button before it comes out of the printer. Then go and click on that now or in print preview that you can go and click on it to zoom out to get a basic overview of the layout and click on it to zoom in any case you can do that you can right click here and change views or just right click anywhere in the middle of the print preview and we can go to report view which is the default view. And then of course how was it designed. All right click anywhere. Go to the design view and there you go just like the form's design view. You've got your little dots here. You can go ahead and line your fields just so it will go over that greater detail and later training video. And then if you want to verify that the source of the data is coming from the books table the record source again you want to bring up the property sheet for your design view here as we did for the foreign design for books. And you can come up here on the design tab to the tools group click on properties sheet and it brings it up and just make sure that you've got that little box selected here or while you click in a blank area. So it selects the entire report and not just an item or a field of text boxes report because right here we've got labels 6N. Well that's not going to tell us where the data is being pulled from. So you want to be able to select the entire report. You can click off to a blank area and you can see when I do that that little box there gets filled in with the black with a smaller black box. And so you can see over here is the report Taurean click on a drop down arrow and find report and it's still selected. Or you can click down below and the off the grid here in a blank area and it still report any case. Come over here and you've got a bunch of different tabs one for formatting your design for the data events. Others we're not sure where to look and click on. All because that has all. And you can see the record source is the TVL books. You can click on that drop down arrow and change it if you'd like. And that's a good idea for this database that we do have the three letter prefix for our tables and our queries. So I can identify which one. In any case this one the click off is based upon the book's table. And then when we're done let's go ahead and close out close out or right click and close all or just close one that we're on but let's close all.