Join Robin Hunt for an in-depth discussion in this video Understanding Excel and Access can work together, part of Building Solutions Using Excel 2013 and Access 2013 Together.
- I want people to understand that Excel and Access compliment each other and if fused together properly, can do a lot to automate your own personal solutions or departmental solutions. I've met several different types of Excel and Access users through the years. There are the everyday Excel users, who use only what is set up for them. I also find the super users, who have figured out everything they ever wanted to do, learned how to make it happen in Excel, and were successful. I've met some Access users that say they don't like Access, not realizing that what they don't like is the system that was developed for them, not Access itself.
I think it's important to figure out how you want to deal with the data that you have the responsibility of managing and reporting on. Figure out the strength of each program and use it accordingly. So why do people use Excel? You may be getting data from multiple sources. The system or systems you work with might export directly to Excel. Excel is definitely better than the native notepad for .csv or .txt files.
You should probably consider database capability when the reports you receive are only in Excel with very few other report options. The reports are served after the decision versus before. To use Access and, or Excel, you should gain a proper understanding of both tools. You're probably already working in Excel and you're probably already doing the same repetitive tasks in Excel. The types of data that you can use are not limited to .csv and .txt if you can link it to Excel any file that can be imported into Access, like .xls, or .xlsx, .csv, or .txt, and then also ODBC connections that gain you access to your SQL or Oracle databases in both Access and Excel.
One of the key limitations to Access is the file storage at the time of this video is limited to 2GB for storing data in Access. In your job, you may find that you're ready to make a transition to including more data tools. Excel and Access are typically an easy entry point for anyone wanting to do more than the basics with data. The ability to manage the data, build queries, and forms, and reports using Excel as the backbone for the tables, can add a big boost to your personal productivity and the interoffice data and reporting management of the data you work with.
These techniques are the key to better personal productivity data systems. Start watching now.
- Using the Problem Steps Recorder
- Importing and linking Excel data as Access tables
- Creating queries with tables
- Calculating data in Excel vs. Access
- Building forms
- Creating reports for your solution
- Building macros to run imports and queries