Microsoft Access Based Accounting

Gene MarksMicrosoft Access Based Accounting
Presented by Gene Marks, CPA
Owner, Demand Systems
Contact Gene at gene@ledgersystems.com

May 29, 2001

Visit the AccountingWEB Workshop Calendar for upcoming sessions.


Summary

Write a letter today? If you did then the chances are great that you used Microsoft Word to get it done. Do a little financial analysis? Used Microsoft Excel we'd bet? Sent an e-mail too? Yep, Microsoft Outlook this time. If you see a pattern then it's only because you are a part of the millions of businesses that use Microsoft as the de facto platform for their business applications. Why then should an important application like your accounting software be any different?

In a live AccountingWEB workshop, CPA, Microsoft Certified Professional, and Platinum UA Business Software Partner, Gene Marks of Demand Systems, Philadelphia expanded on the power and efficency of accounting systems built with Microsoft Access that look like, feel like and act like the Microsoft Office systems you already use.

The complete transcript from the workshop appears below.

The Power of Microsoft Access for financial management. Now that it's in there, how to I get it out? The power of the query. Reporting? You want reporting? We've got reporting. A seamless flow of data to other Office applications.

Workshop topics included:

  • Comparison of accounting products that use Microsoft Access
  • How these programs integrate with Microsoft Office
  • Why 'source code' is important
  • How reporting is done
  • Moving and working with your accounting database
  • Integrating Microsoft Access databases with other applications
  • Publishing your accounting reports to the web


May 29, 2001 Session Sponsored by: Red Wing Accounting Software

Red Wing Accounting Software


Workshop Transcript

Session Moderator: Welcome everyone, and thank you for joining us today! I'm pleased to introduce Gene Marks, who will be presenting a workshop on Microsoft Access Based Accounting.

I'd also like to thank our sponsor, Red Wing Accounting Software. You can click on the banner above the workshop to learn more about our sponsor.

Gene Marks is a CPA and he spent 8 years with KPMG in Philadelphia, most recently as Senior Manager. For two years Gene was Corporate Controller of a publicly held bio-pharmaceutical company. While at KPMG Gene was an award winning National Instructor of the firm's professional development courses and he currently conducts seminars for CPAs in the Philadelphia area for Penn State University. Gene is also a Microsoft Certified Professional.

Welcome Gene!

Gene Marks: Hi everyone.

Thanks for joining me at this workshop. As an introduction, I own Demand Systems Inc. and we're located in Philadelphia, PA. There are 10 of us in our company, CPAs, visual basic programmers, project managers and technicians. Our clients are all across the country. We sell GoldMine Software (a very popular contact management/CRM application) and UA Business Software, which is an MSAccess/SQL based financial application. Regarding UA, I'll be doing my best to keep things objective during this session, but I can't promise anything!!

This is a first for me, doing this kind of online workshop so thanks for your patience. I think we'll have a lot of fun. If I go too fast (or too slow) please let me know. If you have any questions, please jump in and I'll do my best to respond. If things get off track I'll let you know and we can take things offline at another time. I've got a six-page presentation prepared so I'm guessing this will take between 30-60 minutes, including any questions. So here goes!

Millions of people around the world own or use Microsoft Office to write letters, prepare spreadsheets or send email. Included in the Microsoft Office Professional Suite is one of the most popular databases in the world - Microsoft Access

As it is on most computers, this program offers advantages that CPAs can use to enhance their services. The universe of Microsoft Access accounting applications is growing fast. My firm is a reseller of UA Business Software (www.advancedsoftware.com) which is a Microsoft Access based accounting application with modules in order entry, job costing, e-commerce as well as standard accounting functionality.

Other popular Microsoft Access based accounting products include Red Wing Software, Traverse, MTX Accounting, and Yes! I Can Run My Business. A great website to review Microsoft Accounting applications is www.granite.ab.ca/accsacct.htm.

Most of these products have higher end solutions that run on Microsoft SQL database. Most also come with 'source code' which will be discussed later.

One of the best things about Microsoft Access is that it completely integrates with Word, Excel and other Office applications. As such, Microsoft Access is part of the Office Suite, therefore, any table, report or query from your accounting application can be instantly converted into a Microsoft Excel spreadsheet or a Microsoft Word document.

How does this work?

A specific customer has invoices overdue and you want to send a polite reminder. You generate a Microsoft Access based accounts receivable report for that customer only and then select the option to "Publish with Word." The report converts into a Word document where you type your specific reminder message on the bottom. From Word, you send the document to your fax software! Speaking of fax software. You might want to look at winfax or faxrush.

Another example is when you generate a Microsoft Access income statement report and you'd like to take some of the columns of this report and copy them into your forecast in Excel. You select the option to "Analyze with Excel" and the report is immediately converted into an excel spreadsheet. You copy the specific cells and paste it into your forecast spreadsheet.

I have a client near Philadelphia where we prepare a monthly forecast report. I take the income statement that's generated by UA and then, following the steps above, convert it into an excel spreadsheet. I then copy the columns I need into the forecast template. Next I update the forecasted columns for more actual data and save it. Once I have the income statement completed in UA, the process of moving it to the forecast excel template takes less than 5 minutes. This ease of integration between Microsoft Access and its other Office applications allows users with very basic skills to open their accounting database, select certain records from a table and with one click transfer it onto a spreadsheet for analysis or reporting.

For example, another client using UA may need customer information from their database. All we do is open up the database in Access, open up the customer table, highlight the rows we want and 'analyze with excel' the rows are immediately outputted to the spreadsheet. It's very, very cool.

One thing we have to be careful about in this situation is making sure the client knows that a user, when doing this is dealing with live data. Certain precautions would be taken, for example, making a backup of the database and messing around with the backup copy only. In either case, using the Microsoft Access database is a powerful and easy way to get data out very, very quickly. Because Microsoft PowerPoint, FrontPage and Outlook are all part of the office suite, integration with your accounting application is now that much easier.

For example, showing an analysis in a presentation is made easier, because once you produce an Excel spreadsheet from your accounting database, Microsoft PowerPoint allows you to insert the spreadsheet (Insert|Insert Object) directly into a PowerPoint slide. When designing your website with Microsoft Front Page, you have the ability to insert tables to store data entered by the user and from these tables, a developer can add script to write this data directly to your Access accounting database. And like PowerPoint, you also have the ability to insert spreadsheets, pivot tables and charts directly into your web pages from data sent out of your Access accounting system. Most of the Access based accounting programs allow email directly from the program using Microsoft Outlook.

For example in UA, when viewing a customer record that contains the email address, by clicking on the email address, Outlook is automatically launched and the customer's contact info is filled in for ready email.

We sell the contact management program GoldMine Software (www.goldmine.com). Because GoldMine also has a powerful email feature, when our clients want to send an email from the Access application, the application launches GoldMine instead of Outlook. Because GoldMine is ODBC ('open database') then the email can be recorded in history there, as well as in the accounting application. It's not too hard to do. As you can see, having an application that uses MSAccess as its database allows a significant and deep integration with all of the other office products.

Before I move on, are there any questions or comments?

Chris Lewis: What is the benefit of using Access vs. other "complete" accounting systems?

Gene Marks: Can you tell me what you mean by "complete?"

Chris Lewis: A package that has project accounting, manufacturing etc.

Gene Marks: Got it, stand by....

Microsoft Access applications, in a nutshell, give these advantages over other systems....
1. Integration with Office products
2. Better and easier reporting capabilities
3. Support not tied to the manufacturer (you can choose from an MS Partner)
4. Most of the applications offer source code for modifications
5. The Access database can be integrated with just about any other non-proprietary database.

Our clients who buy UA buy it to customize it. Period. They've looked and looked and can't seem to find an application that does everything then specifically need.
Another great advantage with Microsoft Access applications is reporting capabilities.

Session Moderator: Gene, it seems to me that more accountants (myself included) take the time to learn Word and Excel, but have not devoted the time to get up to speed with Access. Have you found this to be true?

Gene Marks: Absolutely, however...Access comes with MSOffice and Microsoft offers hundred of training programs, online and in person around the country on Access, more so than any other database application and there are dozens and dozens of titles on Access that can be readily bought at Borders, etc.

Session Moderator: What do you recommend, in the way of training or books, to the person who wants to get over the learning hump with Access?

Gene Marks: Access for Dummies...this book will teach you all you need to know to be familiar with your Access database, but be warned...Access for Dummies will teach you queries, reporting, basic stuff. But if you want to customize, you'll want to dig into the source code and as I learned, being a good tax professional is a full time job, being a good visual basic programmer is also a full time job…I've learned to use the experts when I get in over my head.

Does that help?

Session Moderator: Yes - thanks very much!

Gene Marks: Any other questions before I continue - I'm going to go deeper into the benefits I previously described....

Kevin Becar: Can you recommend any online sites that could be referenced for either a) help or b) tutorials?

Gene Marks: www.granite.ab.ca/accsacct.htm. This site has everything in the world about Access applications to start and...Microsoft...there's nothing better - they have tons of tutorials, books, training registration etc. and...Microsoft newsgroups...there are thousands of Access professionals out there that love to give free help.

Does that help Kevin?

Kevin Becar: Excellent, thanks!

Gene Marks: Another great advantage with Microsoft Access applications is reporting capabilities.

All of the data in a UA Business Software application, for example is stored in one .mdb (Microsoft Access Database) file. From this file, a user can design his own reports, queries and forms and create necessary shortcuts for users throughout the company. Be careful though. For security purposes, we like to setup another Access database and "link" the tables the user needs so they're not in the entire backend company database. As long as the user has security access to the database, employees can access important information without even entering the accounting system. Also with regards to reporting, the user has many resources that are not available with other products.

Because Microsoft Access is so widely available, a user who wants to learn more about setting up reports and queries can choose from hundreds of books (including the Dummies series, for example), websites and training materials (both classroom and self-study). If the user prefers to use another reporting application, such as Crystal Reports, FRx Reporting, Microsoft Access' ODBC (open database connectivity) allows him to do so.

A great example of Access reporting is like this...We have a client that has various employees that need information from the database, but they're not in the accounting dept. For example, they need pending orders for shipment, overdue orders, etc., etc. For these employees, we setup an icon on their computer which points to an Access report. The Access report only pulls back the data they need (we even build forms for selection choices, etc). This way, they're not in the accounting system at all, just getting information out of it.

Outside accountants, auditors and tax preparers can easily take the one database file and, with proper security settings, design their own reports from the database to retrieve information they need. In fact, we have many clients that copy the database file to their own workstations or even to zip or compact disks so that they can backup the data and/or move it to another location for analysis, etc.

We've found that the biggest learning curve is just understanding where the data is in the database. UA Business Software has over 300 tables of tables in its database, but because they're easily identified by module (i.e. tblarinvoices stores accounts receivable invoice information) it doesn't take too long for the user to figure out where to go for the information needed.

We've found that the biggest learning curve is learning where all this data is and determining what data is needed, rather than the actual report design. In fact, Access gives built in report wizards to help the user design their reports easily.

A great example is labels for mailing. A client wants to send Christmas cards to their customer database. They need to print out labels from their Access database. All they need to do is open up the database in Access and...Choose the 'label wizard'...The wizard asks where the data is coming from (in UA it would be tblcustomers). The user selects the data (i.e. company, contact, address) and formats it on the screen. The user chooses what type of label (Avery 5160, Avery 5162, there's tons of templates). And that's it......Access prints the labels. This kind of thing always knocks people out!!! So you can see there are lots of great reporting capabilities by using an Access database.

Before I move on, are there any questions??

Debra Banach: Does the accounting software make using relational databases easier? In what way?

Gene Marks: Great question. Access (and SQL) is relational, but it's up to the developers of the software to truly take advantage of the relational nature of these databases...In UA, for example (and by the way, it's the same with RedWing, Traverse, etc.)...There are hundreds of tables, making full use of relationships. These products make great use of ID's in the tables to relate data to other tables. This keeps performance up and helps report. The only disadvantage is that not all important data comes from one table. If you need to see an invoice history in UA, you'd need to look at both the invoice table, the invoice detail table and the customer table to get the full picture.

Does that help?

Debra Banach: Yes, I've always had trouble with relational databases.

Gene Marks: It's not the easiest thing in the world, but it's best left up to the software developers, not us accountants!!

I'm going to move on.

Microsoft Access opens the door to integration with other systems. Many of our clients buy UA Business Software so that they can integrate it with other systems that they have or plan to buy. Because most Microsoft Access accounting systems provide their source code (more on this later) and have ODBC capabilities, an experienced visual basic programmer or Access professional can move data between systems quite easily.

For example, as I mentioned, we also sell GoldMine Software, a contact management and customer relationship management application. When a client enters contact information, they sometimes wish to synchronize their data with their accounting database too, so that duplicate data entry can be avoided. This can be done with a small batch program written by a visual basic programmer. (By the way, in Philadelphia, these guys charge an hour of $125 per hour, so keep them under control!). In addition, a GoldMine client may want to generate an order while in GoldMine. A programmer can write an add-on that allows the user, with a single click, to launch the accounting application, fill in the necessary order information from the contact record in GoldMine, complete the order data entry and then write it back to the contact history in GoldMine so that sales and customer services has this information to better serve their customer. This type of integration can be accomplished with other popular applications like Act! and Outlook.

Like I said earlier, all of our clients buy UA to customize it. Here are some examples...

A furniture manufacturer with a very detailed menu type of bill of materials to assemble furniture, complete with labor, overhead, etc. No application could do exactly what he wanted. He bought UA for $8,000 (that's all the modules and then the source code). He then spent $25,000 customizing it!!

We have another client...The are a distributor of machine parts with a complex inventory management system. They bought the SQL version of UA ($30K) and spent an equal amount customizing it to automatically fulfill, bill, report and communicate with their customers.

Other clients have existing Access databases (like inventory or purchase orders) and the want to build an accounting back end. These types of clients MUST have a certain mindset...The mindset has to be that they are buying an application to develop it into a solution. It will take patience, resources and a large bottle of Jack Daniels before it gets there. Valium is also recommended.

Many of our clients have future plans to implement other systems in their business. A good number of them plan to put in bar coding, employee management, time card and production systems at some undetermined time. Because their accounting system uses Microsoft Access they have no concern about integrating their accounting data with another system (as long as it's ODBC, which most major systems are). And because Microsoft Access is so widely recognized they have little fear of finding qualified people to help them customize and integrate the systems together.

Most of our clients also have some type of Internet presence or at the very least plan to have web capabilities. In the not too distant future, it will be commonplace for even smaller companies to allow their customers to place orders, check on existing orders, verify inventory and even issue payment electronically. In addition, most of our clients will be allowing their own employees, through intranets and virtual private networks, to access and update accounting data from outside the office in order to better serve their customers and keep information up to date. Microsoft is extremely web-conscious and has made significant changes to its Office applications to make them more Internet friendly. More changes are planned.

Because Microsoft Access data can easily be stored and updated from a website, a client does not have to worry that a web programmer will face difficulties reading and writing to their accounting system due to inaccessible or proprietary data.

Microsoft Access allows the user to very easily import and export data. This is important when moving from other accounting systems. We always tell our clients that if they can get the data out of their current accounting system, we can get it into their Microsoft Access database. And conversely, if a client ever needs to move from their Microsoft Access system to another accounting application, moving data out of their system is a not so difficult task for any experienced IT professional.

A lot of stuff here. Before I move on, are there any questions??

OK

And then there's source code. Most users don't realize that they usually get the underlying program commands that are the building blocks to the program with most of the popular Microsoft Access based applications. The source code is written in Microsoft's extremely popular Visual Basic programming language. This is important for many reasons.

First, if the company who manufactured the software were to disappear (and software companies, no matter how financially strong, have a risk of doing this) you've got the keys to the kingdom. You have ultimate control because you've got all of the programs needed to make any changes necessary and continue the life of your application indefinitely.

Second, because you have all of the source code, you don't have to rely on the manufacturer for support. This means that anyone with a good knowledge of Visual Basic (there are hundreds Microsoft Solution Providers in this area and an equal number of books and courses available) can fix problems and make changes to your program whenever you need. This solves the problem of one-source support for the most important asset in your business – your financial system.

Finally, owning the source code means you can make any change you want – and all of my clients do. Don't like the look of the order entry screen? Change it! Need more fields of information when you add an inventory item? Create them! When you save an invoice, would you like certain data to also be written to your customer relations system – no problem.

Owning the source code means you can integrate your program with just about any other open database, such as bar-coding, time and billing and contact management systems, as long as they're compliant with Microsoft Access – which most systems are.

Beware of some of the perils of owning source code. Many of the major non-Access accounting vendors discourage source code availability because it interferes with the upgrading process. For example, once modifications are made to the order entry module in an Access application, you have to be very careful when upgrading your product so as not to stomp on the modifications you've made. Most vendors offer 'tools' instead of actual source code to help the upgrade process but these tools offer significantly less flexibility to the programmer. So be very careful when modifying your software - always involve an experienced visual basic programmer and make sure that changes are thoroughly documented!

So remember...Microsoft Access applications, in a nutshell, give these advantages over other systems...
1. Integration with Office products
2. Better and easier reporting capabilities
3. Support not tied to the manufacturer (you can choose from an MS Partner)
4. Most of the applications offer source code for modifications
5. The Access database can be integrated with just about any other non-proprietary database.

I'M DONE!! Any questions please???

Patricia J. Foley: So as the client's outside CPA, what role if any should the CPA take in the software solutions?

Please don't take this the wrong way but...your expertise is probably accounting, tax and business advice so...as a good business advisor, recommend good experts, hear what they have to say, and help your client evaluate. There are a lot of firms out there like ours that do this full time and you should take a look at as many as you can, plus the applications they offer.

Is that OK?

Patricia J. Foley: Thank you.

Kevin Becar: I have gotten bad results from joining too many tables together. Is there a rule of thumb to go by?

Gene Marks: The more joins the slower the database performance...although there's no set rule, I've never seen more than 2 joins on any one table. So Kevin, you should evaluate whether a 'join' is really necessary for your application.

Chris Lewis: Can you use Access for project management?

Gene Marks: You can use Access for project management if you want to create something from scratch, however…there's a whole industry of project management applications out there, like Primavera and Microsoft Project and it would make more sense to INTEGRATE your Access accounting application with one of these applications, rather than build something from scratch. Does that help?

Chris Lewis: Yes, thank you.

Gene Marks: Any more questions please??

Session Moderator: What a wealth of information! We really appreciate your being here today - thank you Gene!

Gene Marks: Thanks everyone!!!

Patricia J. Foley: Thank you.

Michael Platt: Thanks

Session Moderator: We also want to thank our workshop sponsor, Red Wing Accounting Software!


Biography

Gene Marks CPA spent 8 years with KPMG in Philadelphia, most recently as Senior Manager. For two years Gene was Corporate Controller of a publicly held bio-pharmaceutical company. While at KPMG Gene was an award winning National Instructor of the firm's professional development courses and currently conducts seminars for CPAs in the Philadelphia area for Penn State University. Gene is also a Microsoft Certified Professional.

About Demand Systems

Demand Systems Inc. provides controller and computer services to small and medium sized businesses for clients in the U.S. and United Kingdom. A leading reseller of UA Business Software since 1998 Demand Systems is also a Platinum reseller of GoldMine Software. Our CPA's and Controllers help our client do things quicker and better using software tools and accounting knowledge that a controller provides without having to hire a full time person. Demand Systems also provides visual basic development and SQL administrative services to its clients as needed.

E-mail: gene@ledgersystems.com
Web site: www.ledgersystems.com

You may like these other stories...

By Deanna C. WhiteThis year, minority accounting students who aspire to become CPAs will have three opportunities to explore the possibilities the career can offer when the American Institute of CPAs (AICPA) holds its...
On May 2, 2012, the Center for Audit Quality (CAQ) has released a report summarizing its recent workshop on the evolving role of the public company auditor. More than thirty workshop participants gathered in New York on...
Session SummaryThe leadership of the National Conference of CPA Practitioners (NCCPAP) announced their concerns regarding the fallout from the events surrounding Enron. The leadership of the organization believes that this...

Upcoming CPE Webinars

Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.