Excel, Power BI, and Access

Training: Access

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Develop your talent with more than 10,000 online courses from LinkedIn Learning

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 our 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 and 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 Browse, 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 ahead 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.

LinkedIn Learning

LinkedIn Learning is an online learning platform that combines industry-leading content from Lynda.com with LinkedIn’s professional network of more than 500 million member profiles to provide highly personalized course recommendations and a more intuitive learning experience. Learn more.


  • Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Provide learning for your team or entire organization, with an easy to use experience for managing users, curating content and measuring engagement

For businesses with 150+ licenses Request Office 365 onboarding assistance from FastTrack

You can request remote and personalized assistance with onboarding. Our FastTrack engineers will help you plan your Office 365 project, assess your technical environment, provide remediation guidance, and provide user adoption assistance. For businesses with at least 500 licenses, Microsoft also provides personalized assistance to migrate data to Office 365.

See the FastTrack Center Video: http://aka.ms/meetfasttrack

Get started today: http://fasttrack.microsoft.com

Tip: Businesses with 1-149 licenses still have access to FastTrack guidance via links in the Admin Center and also available at https://aka.ms/setupguidance.

Transitioning your company to Office 365? This course provides Level 1 IT administrators with an overview of the most common end-user support requests related to Office 365. Get a quick recap of Office basics, including the features of Word, Excel, PowerPoint, Outlook, Skype, OneDrive, SharePoint, OneNote, Access, Delve, Yammer, Publisher, Sway, and Power BI. Find out how to troubleshoot common account and permissions issues and repair broken apps. Plus, learn how to make sure files are synced correctly between a user's desktop and the cloud. Microsoft solutions expert Sharon Bennett will make sure you have the answers to your users' most frequent questions—before they even ask.

Topics include:

  • 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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.