Receive a quick overview and demonstration of tasks that the IT support professional should be familiar with.
- [Instructor] Access is a relational database that is perfect for collecting and organizing your company's information. If you're finding your Excel spreadsheets are becoming a little bit too unwieldy to manage all your data, it's time to move to Access. I'll be launching a local copy of Access, since there isn't an online version. I happen to have Access pinned to my Start screen, but your copy of Access could be somewhere different. We could go ahead and open up a recently accessed database, we can create a brand-new blank database, or we can use it with one of the templates.
For first step, let's go ahead and open up the Contacts, Template. We have a little bit of information. I'm going to go ahead and click Create, and right off the top we have a welcome screen that provides some information about this template, and it even includes a little video to help us get started. Let's go ahead and close this. This template contains all of the tables, all ready to go, the queries, all of the forms, so we can simply add in our data as required. Our reports are created for us, and so are the macros in modules.
This makes it really easy to get started in Access, and I would recommend that you start off with a preconfigured template when you're learning Access, but for our demo today, we're going to use a blank database so you can see a little bit under the covers of what is involved in creating your database. Click File, and then New, and then Blank Database. I'm going to provide a database name, and click Create. Now this is letting me now that I already have a database called Vendor List, and now I'm being asked do I want to replace it.
For our purposes today, I'm going to go ahead and say yes. And there it is. There's your first table. Looks a little intimidating, doesn't it? Well, let's go ahead and start by adding some fields and some data. Now, I'm not going to lie. I already have the data copied to my clipboard. I'm going to go ahead and just paste that in, and then we'll do some manual updates. I'm being asked do I really want to paste this data. Yes, I do. And now I'd like to go ahead and add one more field for state and/or province. So this will be a short text.
Perfect. And now I can add in this data manually. Now typically when you're starting out creating your Access databases, you're probably pulling information in from other locations, whether it be other users, OneDrive, maybe from SharePoint, and that's exactly what we're going to do here. We actually have a vendor list already created. We're going to go ahead and append that to our existing table. To do so, click on External Data, on New Data Source, this is coming from an Excel file, and we're going to go ahead and append it to Table1.
I guess I should find the file first. Click Files, and it's called Vendor List, click OK. Now it's letting me know that my table's currently open and it must be closed in order to append. Am I okay with this being closed? Yes, I am. It's asking me do I want to save the changes? Yes, I do. And my table name, I'm going to leave as Table1. My Excel spreadsheet has two sheets within it. Therefore you have to select the correct sheet, in my case, it is Vendor List. Access has already determined that my first row contains column headings.
If you have to check that box, please ensure that you do so. I'm going to go ahead and click Next, and finally we can import to our Table1. Go ahead, click Finish. Now we're prompted do we want to save these import stacks? For our demonstration purposes today, no, we will not. Go hit close. And go ahead and open your table, and you'll notice now that we have a list. And just looking at this list, you're going to see that we do have some duplicates. Let's go ahead and run the performance analyzer. You'll find this tool under Database, Tools, and then Analyze Tables.
Basically, what this will do is clean up our data, or normalizes it. If you're bringing in similar data from different locations, there's a good chance you're going to have duplicates, just as we do in our demonstration here. And we'll quickly run through this wizard. It's going to analyze Table1. Yes, we'd like the wizard to decide. And next we need to confirm that the wizard is grouping our information correctly, and it looks like it is. Let's just make these a little bigger, and then we'll need to go ahead and relabel these tables.
So instead of Table2, we're going to call this Company. And Table3 will be Location. Go ahead and click Next. And next, we're being asked if the bold fields uniquely identify each record. For our purposes today, we're going to say yes, but in a production environment, I'd probably change this out. Our next option is do we want to create a query? Yes, we'll let Access go ahead and create that, and then we'll click Finish. Next we have an error.
The wizard couldn't rename our table because it's open. Therefore, our query's going to be saved as Table1_new. That's fine. I'm going to go ahead and close the Access Help. And now you'll notice that we have our Company table, and our Location table. This was a very high-level demonstration of what you can do with Access. For answers to the most popular Access questions, and additional tutorials and how-to's, please see the Access Training Center.
- Training users
- Troubleshooting account and startup issues
- Repairing Office apps
- Working with Word, PowerPoint, Excel, Outlook, Visio, and OneNote
- Communicating with Skype and Yammer
- Using OneDrive and SharePoint
- Fixing file synchronization issues