Hello. And welcome to the module 1 skills, video in. This video we're, going to learn. How to open up access create, tables. Add records to tables, create. Queries forms, and reports, first. Things first how do you open up access well. To, open up an existing database there's, two separate ways you can do this first. Off the most direct way is to double click on the database you want to open. When. You first open up an Access database you. Will be presented with a security, warning up on top and just telling you that some active content has been disabled, what, this is is, access. Can. Contain, macros. Or Visual. Basic for, applications program. Code and some. Of that it, could be malicious but we. Trust. 99%. Of the databases out there so you want to click enable content, so. You will be able to get to the database and all one's, reports, and so forth to it so. That's, one way of opening up access the. Second way is opening. It up through, access. The application. So, I have an access. Icon on my toolbar and if I click it it will. Open up access. As an application, in what's called the backstage, view and, from. This view you can either create a database or you, can, open up an existing database, file so. To open up an existing database, file the, ones that you have opened, recently will, show here in this, menu if, it, doesn't. You can select open, other files and, then. Select browse to, browse to that particular. Database. And, I, can open up the database i just opened. A couple. Seconds ago and there. You go, so. That's how you open up access, let's. See how we go about creating, a new Access, database, so. Once again. Simply. Open, up the, access application. So. That we can open it up into, the. Backstage, view. So. In backstage, view you're presented with several, options for. Databases, you. Can create a blank desktop. Database or, a custom web app or they, have numerous, templates, that you can create. A database from we're. Going to create a blank, desktop. Database, so, once we select that we. Are presented, with the. Ability to name it and to browse and, actually. Find, a location that we want to place it so, I'm going to click right in here and I'm going to name this database Riverview. And. The, next important, thing is you want to browse and, actually place the database in a location that you can easily find it now, if you don't. Choose a location it's going to wind up in your Documents, folder I, like, to place mine, typically, on the desktop, or in a folder that I've already created. So. I'm going to do that I'm going to put it in the module 1 database that I was working with. And, once I click okay now it. Has a location set it. Still hasn't been created yet because we need press. The create button. And. When. Access opens, up a new database it's, default, view will be in. A new table, and that's because there are no objects, in the data set and as discussed, in concepts, video. Tables. Are the foundational, objects, and, access. To. Enter in a record simply. Click in the field that you want to start with in this case visit, ID would make sense because that's first filled in table so I'm, going to type in my values 107.
2 And, then. To move to a new record or tune in the field you, can use the arrow key or you, can hit enter or you can hit tab so. If I hit enter I'm, moved. Over to the animal ID field, so. To enter or value in here I'm just going 1 2 3. 5 6 and. Then, all hit, enter and, I'm gonna do visit date now. The visit date for this record will be 12 12. 2016. I can. Also use the date picker which, appears, for right next to it but. For this one I'm just entering, it manually and then. For the reason, I'm going to type in vaccinations. And. Then. For the off-site, I'm. Gonna say, that yes this was an off-site so, to do a check mark into. A table, I can either position. My mouse pointer over, it and click or I can, hit the spacebar and that, will add a. Check. Mark into the box, so. Again, we talked about saving objects, and saving records you, notice as I, was entering in data into the fields we have this little pencil icon showing. Over the 4 left this. Indicates, that a record is being. Modified, and, it. Is in database. Terms dirty, it's a dirty record the changes have not been saved to it to. Save the changes, to this record all I need to do is move. To, the next record so. If I hit tap I'll move to the next record and you'll notice the pencil icon is gone. At that point so. That's one way that you can save a record the. Next way that you can save it is by. Actually, clicking. The table so if I go in here and. If. I change this and. Now. I'm starting to work on it you notice the pencil icon comes up if I changed to five, five and then I close my fatal, if. I go back and reopen it. You'll. Notice that since. I closed the table the change was, saved, so. I'm going to go back here I'm, going to change, it back to what it should be and now, move over to the next record and enter, in the subsequent records for this particular, table, so. Now I've entered in a total of nine records a couple, other things about tables, before we move on is. Down. At the bottom we, have what are called record. Selectors, and navigation, buttons and what, they allow you to do is to navigate specific. Records, within the table, as. The table grows very large and has hundreds, of records becomes, very, efficient. Way of moving about, a table, and finding a specific record. The. Buttons here again a single, arrow will mean that it's going to move to the next record if. You have the arrow with, a little what. I used to ko with. The cassettes you, could go, to the very end and then. There is an error with a splotch here little. Asterisk, and that, will put you on a blank record, the. Other thing about a table - you'll notice the, visit IDs the star primary key but. You'll, notice that the numbers are out of sequence, they're not in a sorted. Order so. When, you actually close. A table, and then reopen it tables. Are automatically. Sorted, on their primary key so I've, entered in the records here and, if, I go about closing it it. Will, open. Up, with. Those primary, keys sorted. In order so, you notice before, how. It was kind of mixed up now. The visit IDs are sorted, in what's called ascending order from smallest to largest, the. Other thing you can do with the table is in. Datasheet view where. You cannot see the entire value in a field. If you, position. Your mouse pointer, between, it and the next field until, you get that double arrow and double-click it it. Will resize, the field to. Best fit. Now. If you want to resize all the fields what, you can do is select or, click on the table selector, which is this little button.
Here In the far corner. So. That everything, is highlighted and, to. Select any one of those fields. Position. Your mouse pointer between them to get that double arrow and double click and then, the entire all of the columns. In that table will be resized, for best fit. We. Can also copy, in records. From. Another table in an Access database so. With our visit table right now I've added a couple more records again. It's sorted by visit ID but. There are many more records sitting in another database in a, table that, I want to copy. And paste into this table so. I'm. Going to move over to my Kimberley, database, and open. It up. And. Once again I get the Mabel, content, scary warning it's. All unable to content, and the records that I want are coming from, the appointment table so I'm. Going to open this table up in datasheet. View and. To. Select every, record in the table again. I want to select. Or, click on the table selector, and that. Will, get. Every, single row there now. To copy it now I can click copy, control-c. And control-v or, the way that, I normally copy, and paste items. But all in, the video copy it this way and. I'll. Go back to, my River View database, now. To paste those records, in what, I'm going to do is, position my arrow on, the, very, the. Record below the very last one so, then it highlights, in taro, and. From. Here I'm going to paste and, whenever. You do an action. To a table where you're copying pasting, deleting, records access. Is going to give you a. Confirmation. Message, so. It's time you brought sixty-five, records are you sure I'll. Click yes and, now, those records, are within, the table so. Again we, can enter data manually, we can also copy, and paste data from. Another table into. An existing table in a database, now that the caveat, with that is, the. Data that you're pasting, has got to be obviously. The same number of columns and the, same data type so tables, contain, the data. Queries. Will, ask. Questions, of that data so let's. See how we, can go about creating a new, simple query, so. I'm done with the visit table for now I'm going to close this and. Whenever. We create an object in Access. You're, going to want to click on the create tab, so. Here, under create we. Have. Two, options, under queries, map query design and query wizard so. We're going to create a simple, query using, the query wizard, so. Once we click on query, wizard we, have four options here we're going to select simple, query wizard, so. We click OK now. Our database only has one table, so we're, going to use the visit table and, with. The visit table we can select, a specific. Field, for this one I'm gonna take visit, ID and. Yd, visit. Date reason. And off site now. I just selected all the fields I could, have also just clicked, on the double arrow here now.
For Some reason you don't want, a particular, field, in your, results you can simply select. It and then click the back arrow and that will move it back over into the availability, fields, so, if you want to move everything over use double, click the arrow click the double, arrow so. Again you, can be, selective, on the fields or a query so. In this case again I'm going to take every, single field, pop. It over there and, once. I click Next I, have. The option of seeing it as summary, or as, detail, we're gonna stick with detail, so. It shows every field of every record and, then. Once we click Next we. Can go ahead and mean, query and. We. Haven't gotten into naming conventions, yet so. With. Queries. Queries. Always start, with the Q, R Y. Prefix. So. I'm, going to name this one query visits. And. I'm. Going, to finish and just open the query to view the information. So. Once, I've done that. What. Is it it's simply. The visit table we're taking, every single record from the, visit table here, just. Like tables, and datasheet, view if, I am, having some of these fields. Cut off I'm not seeing all the values I can, resize, every single column using. The, query selector, the datasheet selector. Now. Way highlight, everything and then just position, my mouse between, two the fields and double click and now, I can see everything, within. Those, particular fields, the. Queries again, they will. Ask. Questions of our data and. The. Real power with a query, is in criteria. And, what criteria does, is select, specific, rows, for. Our tree right, now we've only got our query set up where it's selecting. Specific. Fields. So. We. Add criteria, to actually. Show, a specific rows, of our data so for example if, I wanted to see only. Visits, that, the. Reason, had to do with vaccinations. I could, add vaccinations. As criteria. For. My, query so. Whenever you're creating. Criteria. For a query you need to understand, what, values, are being held in a reason, or whatever, field you're going to use so for this one I'm going to use the. Reason, field and add, vaccinations. As criteria so, I'm going to have to change over to design. View for this query to. Do that click. On your home tab and over. On the far left you have your view options so. I'm going to select, the down arrow and I'm going to select design view and. What. You'll see here is the query grid, the query design grid so. For this one I want. To use reason. And right. Here, we have a field, line. A table, line a sort line a show, line which will allow. Us to show or not show the field and then, our criteria, line so. Over here under, reason. I'm. Going to type in vaccinations. Now, I'll type it in wrong though just. To show you something, here. So. Here, of obviously, typed it in long and if I go about running this query. And you run a query right up here at the top you have a view and a run so. If I select run. Nothing. Comes back so. Again that's because I, mistyped. It I'm gonna go back over here fix, this so. That it's correct, and then rerun, it. So. I rerun it again, with the correct typing, and now, instead, of getting back every single record in the table only. Returning, records, where the reason is vaccinations. And again, this is where the. Query is, most powerful when you utilize, criteria. We'll be doing a lot of that in, modules. 3. & 5. So that's, how we create a simple query let's, see how we go about creating a simple form. So. I'm going to close and save my changes to this one and. Now. If, I want to create a form. Let's. Consider a one button form I just, simply need to choose what, records source I want to use now. With, forms. You, can use either queries, or tables, as record, sources I'm, going to use the visit table for this and. Once. Again to create anything any object in Access, we want to click on the create tab and then. If I come over here into, the forms group the. Very first option is a, one, button form so, once. I click it access. Automatically, creates a form for me that's based on the visit table, so. Here I have, a visit, form and it. Has, every single field from the visit table. So. If I want to resize fields, I can, just select over, at the far left, hand or a right hand side and then. I can just move my. Fields. In and now, it's showing. Things a little bit tighter, there so. That's how you create a one button form last thing we can do is create a one button report so. I'm going to close my visit form and I'll save my changes and I'll just leave it set, as visit. Click. OK and, once, again if I want to create a report and, reports.
Again They can be created, based, on a, table, or a query typically. With a report, it's going to be based on a query so. I'm going to select a query for this record source and, once. Again I'm going to come over here to the create button and, over. Here in the reports, group the very first, button that I have is a one-button report. So. I click it and. Access. Automatically. Creates a report for me now. When. You create a report like this you, have to watch the margins, so. Over here on the far right, hand side and. Left and. Top I can actually see how, this report will print out and as. You can see there, is what, I call spillage, I have some, fields. That are spilling over so if I were to actually see, this printed out this. Would be on one page, this would be on a second page so no. Bueno we need to fix, this kind of thing so. The easiest way to do this is right. Now we're in what's called layout, view and, in layout view this allows us to see the data and actually, make modifications to, the report so. The first thing I'd want to do is select. My visit ID and just move, it over a little bit. And. Then. I could do the same with my animal ID move. It over a little bit and now, you can see everything fits, within the report, thing. You have to watch out for though is, access. Will, automatically. Place in dates and times and at the bottom it'll put in a page number and. That. Won't move so. We actually have to highlight the page number. And I just, use my arrow keys and just arrow. It over until it is fitting. Within the page itself. Access. Also creates a total column for you and we'll, get more into reports, later in the semester but just. Know it's what it's doing here is just giving you a record count for the report. With. Reports, you can see them in specific, views so, with reports, you can see it in report view which, just. Gives you not. A print, preview type of look but how the report basically looks again, we're in layout view now so, in this view we can actually make, changes to, the report. While. Still seeing the data print. Previews are important, because in Print Preview you're, able to see how it actually print out so again where we have that spillage, over here and print, preview if, you've come very obvious, because page, 2 you'd see just a little nub over here showing. Off-site checkboxes. So, print preview would look something like this down. Here it has navigation buttons, too so you can go to the next page and you, can see that again there's no spillage, it's just showing the, records on, a single, page so I'm gonna close out of that and I'm.
Going To go, ahead and save, my report, and. Look. Yes, and. I'll. Just call this. Report. Visits, so. Now over, here on the far left hand side this is called the navigation. Pane and in, the navigation, pane you're able to see all the objects, within the database so. Right, now this database has four objects, it, has a visit table it has a query, query visits, has a visit, form and it. Has a report, called, visits, now. In, here we can, make you. Know sort, things different ways we can sort it by. Tables. And related views and what that'll do is just place everything, under, the visit, table category, personally. I like to see it grouped by object, type it's, easier to work with if you need to see the tables together the, queries together the forms together and so forth one. Last thing we talked about the database, file extensions, so. Here. This. Is the Riverview database, it's, sitting on my desktop and, I'm. Gonna close this out for a second go back and reopen this. So. Once I've opened it, I. Will. Have a lock file open, alongside of, it so, again remember the locking file is there to. Support multiple users so, if two people are trying to work on the same, record. At the same time it basically, locks one of the mounts so we. Don't wind what's called concurrency. Problems, where data. Changes, in, improper. Ways. So. That'll do it for this, skills video if, you need to go back you, know feel. Free to rewind to go back over a concept. So. That you can get it again so we, will see you in module 2.
2018-09-10