Microsoft Access: Did You Know? (Part 1)

I'll bet most of you using Microsoft Access (I'll refer to as "MSA" and "Access" going forward), use it the textbook way. By textbook way, I mean you make tables, you make a few queries, you make a few reports, and then manually update the tables and data over time.

That's not wrong, and it's not bad either. Don't let anyone tell you otherwise. Manually working with tables and data is a great way to learn how to start keeping data in a database (via a spreadsheet).

If you work for a company that doesn't have the teams or budgets to build out something more typical like a MySQL/MSSQL/Oracle database with a web front-end, the desktop version of Microsoft Access is the next best option since it's available with most Microsoft Office Professional licenses.

The punchline here is: 'Did you know?' Almost anything you can do on a Windows computer with your keyboard and mouse, Access can do it too? Even better, it can also do stuff you physically can't.

Access is very powerful, robust, and capable, which means you can get Access to do more for you semi and fully-automatically.

Using Access to simply store data is like buying a powerhouse laptop just to play solitaire.

On one end of the extreme, you can load all your data into it, query the data, and have a place to keep data.

On the other end, you can tap into Macros & VBA ("Visual Basic for Applications") and

• create your own automated routines
• create your own functions (i.e. custom formulas that can calculate data using specialized nesting/looping/iterative logic)
• import and export to web databases
• import data from a multidue of sources
• download files from the internet
• create interactive and data validating data entry forms
• export data in a multitude of formats, and be a source for other desktop products like Excel, PowerPoint, Word, Outlook, Sharepoint and more.

It can control other Windows desktop programs and web programs. You can go so far as to build it out and create your own commercial software that others can use with the free Access Runtime.

Benefits of Using Microsoft Access

While many write off Microsoft Access as a rudimentary, outdated, lackluster, hard-to-maintain, antiquated, non-scalable, desktop only spreadhseet on steroids, they haven't invested the time to learn that Access can do these 5 things extremely well:

1. Save Money

Access can save companies thousands of dollars on license costs to 3rd Parties by doing the same thing, plus responsibilty can transfer from senior to junior staff.

2. Save Time

Stop manipulating excel each week. You can open Access right now, import data, and query it where formulas can transform your data.

3. Improve Data Accuracy

Tables force you to keep data type consistent (vs. Excel which does not), and queries with formulas ensure you process data the same way everytime.

4. Reduce Lead Times

Automation allows you to turn manual processes that take hours, days, or weeks to prepare, and turn it into seconds or minutes so you can work with your data on-demand more.

5. Data Manipulations

Access can do some things so easily where Excel and Could databases take more effort. With local data, Access can do anything you want to your data. (I haven't seen a scenario for manipulating data yet where Access didn't prevail)

Once you know that MSA can do way more than simply store big spreadsheets, you can start learning about how to do more with Access. Sometimes, all you need is a good mentor with experience to start you off in the right direction.

Conclusion

The readily available Access Database means you no longer need to wait for IT budget to be approved and development teams to meet. A basic tool can be built in (2-40 hours, based on complexity of needs) whereas a super fancy program can be built (with experience) in a month (or 200-250 hours) or moderately more.

At an average of $60/hr, that means a typical one time cost of $120 - $15,000 can get you an in-house tool that you own, can learn to update, and control. $15,000+ may sound like a lot money for for an Access database, but with Access, you can easily boost a service that costs $100,000/year to one that is identical in functionality with a service that costs $250,000/year. Believe me, the finance department would love you for that.

In the upcoming oists on "Micosoft Access: Did you know?", I will expand on my version of data optimization and table design, leveraging cross tab queries (what is very simliar to Excel's Pivot tables), linking data between office products, and eventually leading into Forms, Interactive Reports, and VBA.

Feel free to recommend a subject matter important to you by sending me a message!

About Me:

I am both objective and biased. I've used Microsoft Access daily for about 19+ years (since 1997) and I've learned a thing or two. I've built close to 200 Microsoft Access database tools, none of which were simple tables and queries. I built each of them to solve a problem of time, accuracy, cost, or time-sensitivity.

I am a consultant for many financial companies and educational institutions that have massive budgets. With that said, Suzie in HR or Paul in accounting don't get a development team to build a reporting or tracking tool they need. That's where I come in, I leverage everything Windows can do and use VBA inside Access to bring everything together to design tools that are not only pretty to look at, but usually take a mundane, monotaneous, error prone, or complicated process and automate it so that it can be run with as few clicks or steps as practical.

If you want to see what Access can do, dream of a scenario you dread doing, and Access can solve it. Ask me, and I'll show you.