In this video, explore how Excel and Access can be used together to build better end-user solutions.
- [Instructor] If you understand how to use Excel and Access together properly, it can do a lot to enhance your own personal productivity and automate your reporting. Excel and Access are tools and this set of tools will help you create a solution for any department or organization. Over the years, I've met many Excel and Access users. There's the everyday user of Excel who works with just what's been set up for them. There's Excel super users who have figured out everything they ever wanted to do and made it all happen in Excel and they were probably pretty successful.
I've met plenty of anti-Access users who typically dislike Access, but what I found is they actually just didn't like what was set up for them in Access. It's important to understand that Access can be used to develop solutions. And when you conquer the learning curve, it can be extremely powerful. To get started, I think you need to think through the data that you're responsible for and then learn the strength of each program so that you can get the most out of each one. So why do people use Excel other than it's the most widely available data tool? Typically it's because they're getting data from multiple sources.
The systems that they work with daily actually export to Excel. And Excel is definitely better than working with Notepad for .csv or .txt files. When should you consider database capability? Well, when you build your reports in Excel, you have few other reporting options. If you do a lot of repetitive work just to create next month or next week's report, you might consider database capability. If you need more real-time information, databases can help serve the information to make the decision versus after the decision has been made.
How do you know if you should use Access and Excel? Well again, get a proper understanding of both tools and how to use them. You're probably already working with Excel a lot for your reports. And if you find you're doing the same repetitive task each time you work on a report, you definitely might consider Excel and Access together. It's important to know what type of data Access will work with. You can import or link Excel files like .xls or .xlsx. You can also work with .txt files and .csv.
You can link in SharePoint list which is a powerful way to share information in real time. And you can access data through ODBC connections so that you can hit your larger database systems like SQL or Oracle. Speaking of large datasets, one of the key limitations is that two gigabyte constraint, but learning how to link data which we'll cover in this course, you can expand that limit. People also don't realize how capable Access is for any solution. You might likely find that you don't need up to 255 concurrent users and that you won't open up 2,048 tables at any given time and your queries likely won't have 32 tables in a single query.
For a full list of the limits, just take a look at the Access specifications. Using Excel as the backbone for your tables, you gain the ability to manage data, build queries, create forms, and run reports. This can be a big boost to your personal productivity.
- How Excel and Access can work together
- Using the Problem Steps Recorder
- Leveraging screen capture tools
- Building tables in Excel and Access
- Creating basic queries using tables
- Maintaining linked tables
- Calculating in Excel versus Access
- Building forms
- Building macros to run on-demand queries
- Generating datasets using parameters, queries, and macros