Author Archive
Microsoft Access versus Microsoft Excel for Data Analysis and Reporting
Choosing Between Spreadsheets and Databases
We are often asked by Microsoft Office power users whether, why, and when they should use Microsoft Access versus Microsoft Excel. Especially when they are very comfortable using MS Excel and don’t understand the reasons why anyone would use MS Access or databases. Here’s our perspective.
Empowering Information Workers
We do not believe it’s an either/or situation. Our experience with Access and Excel is that they both have strengths and naturally complement each other. Individuals who understand the differences, learn the products, and apply the appropriate tool for the appropriate situation give themselves and their organizations a competitive advantage.
Microsoft Office products empower individuals (Microsoft calls you Information Workers) to get things done on your own. This lets you leverage your knowledge of the work that actually needs to be done to your solution. This is far more efficient than forcing you to escalate problems to “IT professionals” who need help understanding your technical requirements with limited understanding of your business requirements. It becomes nearly impossible to do when one doesn’t know the solution yet and is trying and modifying many things.
Advantages of Microsoft Excel Spreadsheets
The learning curve for Excel is very short, so it’s easy to use Excel and be productive right away. Rare are the situations where IT staff create spreadsheets Information Workers can do for themselves.
Excel makes it easy to store data, perform numerical calculations, format cells, and adjust layouts to generate the output and reports to share with others. Advanced features such as pivot tables and pivot charts, analysis toolkit, and many templates make it easy to accomplish a wide range of tasks. Tweaking the results is also very easy to get the exact layout, fonts, colors, etc. that you want.
Disadvantages of Microsoft Excel Spreadsheets
Unfortunately, there’s a price for the flexibility of spreadsheets. While it’s easy to create formulas, reference cells, copy and paste data, and link worksheets and spreadsheets together, as the work gets more complex, spreadsheets become more difficult to change and manage. While spreadsheets are ideal for creating one time analysis, they become problematic as the data grows and evolves over time. As new rows and columns get added, summary ranges and formulas need to be modified or new ones created, data and formulas aren’t consistently updated, and these mistakes lead to bad results and decisions.
The challenges of spreadsheets are due to the difficulty maintaining them accurately over time and scaling the volume.
Advantages of Microsoft Access and Databases
For spreadsheet experts, it’s often difficult to understand what databases offer that spreadsheets don’t already have. There are two several advantages of databases:
- Data structure and normalization through multiple tables
- Scalability: adding more records is free
- Data and Referential Integrity
- Queries and Reports
- Automation through Macros and VBA Modules
Table Structures and Validation
Databases make it easy to store information in one place and reference it in multiple places. For instance, customer information may be kept in a customer table where the name, address, phone number, email, etc. are kept. It is then referenced in other places such as queries, forms, and reports. If the customer’s information changes, the new information is automatically updated in all the referenced places. The table designs also impose structure that lends itself to data types, validation and consistency for higher quality than what normally exists in spreadsheets. The basics of storing numeric, date, and text fields are just the beginning.
Records are Free in Databases
The biggest difference with spreadsheets is that in a database, records are free. If it’s well designed, over time, new records are continually added without needing to add new fields. All the queries, forms, and reports continue to work without any changes. Different filters may be applied but the results are always consistent. There’s no need to retest the formulas (cells) when new data is added. This allows accurate reports to be generated every year, quarter, month, week, day, etc.
Data and Referential integrity
With the fear of garbage in – garbage out, Access databases provide many tools to maintain data quality. Lookup lists and validation rules for individual fields and records can be easily implement in Access at the table level. Forms can add additional rules during data entry to respond to user selection and events. Access also offers referential integrity between tables to ensure data is consistently defined across tables.
Queries and Reports
Microsoft Access queries and reports let you slice and dice your data and present it in detail or summary form regardless of how the data is stored or sorted in the underlying tables. It offers a great deal of power and flexibility to analyze and present results. Using groupings, it’s easy to show aggregations as the data changes and groups get added or removed.
Automation through Macros and VBA Modules
From a macro or VBA module, you can use the DoCmd.TransferSpreadsheet command to export data from an Access table or query to an Excel spreadsheet (use the acExport option). Your spreadsheet can then reference that data or import it into itself.
The TransferSpreadsheet command with the acImport option can also be used to import a spreadsheet into an Access table.
With Office/Excel automation, you can actually open an existing Excel spreadsheet from Microsoft Access, and place data in specific cells, thereby automating the updates of the data there.
Disadvantages of Microsoft Access
The biggest disadvantage of Microsoft Access is that being a database, it takes more skill and training to use it well. Figuring out how to define normalized tables, link them together, and structure the information so it’s easy to edit, query, view, and report can be quite challenging. Many of those issues are not unique to Access since they apply to all database designs.
The learning curve of building a database, creating queries, and designing report layouts may seem quite daunting. It’s certainly more complicated than just putting data in cells of a spreadsheet. It can also be quite frustrating to be unable to easily copy and paste blocks of data, or implement exceptions to the structure of an Access report (for instance, wanting to highlight a specific value or row with special fonts and comments). While MS Access includes VBA code that allows for customization, it requires much more effort than the simple WYSIWYG design of Excel.
Conclusion
Excel makes it easy to generate custom output with very flexible formatting and annotations that you can add anywhere. The payoff with Microsoft Access is how databases simplify things over time. It may be overkill for one time analysis, but if the data and reports need to be maintained over time, spreadsheets often hit a wall. Most organizations have many “similar” spreadsheets that are tweaked slightly differently and rapidly become unmaintainable. A well designed Access database avoids that limitation. That said, both Access and Excel have their strengths and weaknesses.
A hybrid solution where data from an Access database is exported or copied to Excel often provides the best of both worlds. The data integrity of a database with its well defined and approved output in conjunction with Excel for ad hoc analysis lets you leverage the advantages of both. Using automated processes, the sharing of data can be very smooth.
In our experience, these solutions evolve over time in very non-predictable ways regardless of technology. Having tools that allow for the flexibility to rapidly respond to such changes empowers you and your organization to meet its mission efficiently.
Apr 15
Testimonials
Jeff L.
Maria,
Thank you so much for the new program that you created for my company. I have been in business for 23 years and I can’t believe I didn’t do this sooner. You have made us so much more efficient than we were in the past. I can do three times the amount of work that I could do before you created the new Access program with the email integration. I also want to thank you for always being available whenever I called and for being so easy to work with. I will highly recommend your services to anyone in need of an Access Program.
Sincerely,
Jeff
Charles R. Butler – MEDACCESS RX, LLC
Marie,
I wanted to thank you for your excellent work in creating the custom programs for the two companies I manage. Your availability, responsiveness, and additional creative input were invaluable in making these applications not only functional but user-friendly as well.
Even though the source data was complex, the programs perform exactly as requested, and your support for the final refinements enabled us to have these important programs delivered on time and on budget, as promised.
As I mentioned earlier, we will use your firm exclusively as our Access and Database programming provider.
Thank you again for your outstanding work,
Charles R. Butler FACHM
Executive Director
MEDACCESS RX, LLC
HEALTH PLAN PARTNERS, LLC
Eric Wagner – GARNEY CONSTRUCTION
We have different employees across the country using the new estimating software that you helped us build, and we’re having good success with it. The complex formulas, data tables and look ups all seem to be working good and it’s being received well by all who are using it. We’re pretty happy with how it’s going.
Terry Uhran — Surgery One
"I took over a company this past year and one of the priorities was to find someone with Access programming expertise and skills. I went on a significant search and found Marie and better yet she was local. Our company had a very large and complex Access application which required significant modifications and through our search for a programmer we were told that our Access program probably couldn’t be modified. In working with Marie it became evident that she would be able to make the changes that our Access software platform required. Over this past year we have made many changes to our software and we are very pleased with Marie and how the process works. Marie puts everything in to exceeding the customers’ expectations and is readily available in the event we need her. She is even willing to work weekends if there are immediate needs like preparing for an audit and her rates are very reasonable and competitive. Marie goes above and beyond the call of duty therefore I highly recommend her services."
Jim Ripoll — Florida Standardbred Breeders and Owners Associaiton, Pompano Beach, Florida
"Kudos To AccessProgrammer.Org"
As a long time user of Access 2003, our Association had an immediate need to upgrade/modify our Access database program to Access 2010. Researched available companies for assistance and contacted AccessProgrammer.org. Shortly after a detailed discussion regarding our immediate needs, contracted with AccessProgrammer.org to update and modify our comprehensive database.
After a relatively short period of time the upgrade and all requested modifications were satisfactorily completed. Their knowledge of Access 2010 was exceptional. We were able to work on-line which was a great help. They were professional, competent, timely, aailable and reasonable. We would wholeheartedly recommend this company and do intend to continue to use their expertise on an as-need basis in the future.
Glenn Oliver – Southeastern Sales RF
I found Marie Finch on the web and it is a pleasure to work with her. She does a good job listening to my Access issues, she documents them and then outlines a process to fix the issues. Marie also made suggestions on how to provide a (interface or GUI) solution to avoid future issues. She stands behind her work. I do appreciate her support.
Regards,
Glenn Oliver
Southeastern Sales RF
Northern Star
Working with Marie was excellent! She worked in a timely manner, always responded to our emails, and had multiple presentations for us in the time we were working together. We love the program, and she is always open to help us with any questions we have.
We are very satisfied with her customer service, and the program. We are looking forward to a long-term working relationship.
Julio Fernandez
Administrative Support Manager
7648 Southland Blvd Ste 107
Orlando, FL. 32809
Chad Cheek — CEO Citrus Legal Funding
When I decided to start my business I ran into an issue not knowing how to create a database. This database is very essential for my business to operate on a daily basis. I searched the internet before I got in contact with Marie Finch. I explained to her my needs and she really made them happen. She worked with me on a daily basis to ensure the program was running correctly. I highly recommend Marie Finch to help you with you Access needs.
Chad Cheek
CEO Citrus Legal Funding
4700 Millenia Blvd Ste# 175
Orlando, Fl 32839
Andrea Suggs – Administration Manager – Antelco Corporation
I have had the pleasure of working with Marie Finch to set up an Access database of our sales history to allow for customized reporting and detailed information of each product sold and who/where it was sold to. Marie was very attentive at listening to what I was trying to achieve and offered suggestions to make it possible. Marie supplied the finished database within the timeline she had proposed and continued to “fine tune” the finished result until she knew my goals were met. I would highly recommend Marie for your Access database needs.
Andrea Suggs
Administration Manager
Antelco Corporation
886 Waterway Place
Longwood, FL 32750
USA
Ph. 407-331-0699
Fax 407-331-0169
Email asuggs@antelco.com
Web www.antelco.com
Arthur Nascimento – Quality Control Manager – Pro Skin Solutions, Inc.
We needed a software for very specifics tasks in our company and since there are not software available in the market that would serve us the way we needed, we decided to work with Marie Finch. Marie, made an outstanding job giving us exactly what we needed. I have lost count how many times we decided to implement new things in our Access Program and every time Marie is always there when we need. I highly recommend Marie’s services, for the quality of the services provided and for the pleasure of working with such a knowledgeable and hard-working programmer.
Arthur Nascimento
Quality Control Manager
Pro Skin Solutions, Inc.
d/b/a Active Cosmetics Manufacturing
7075 Kingspointe Pkwy.
Orlando, FL, USA – 32819
Ph: (407) 996-9797 Ext. 204
Fx: (407) 996-9798
www.active-cm.com
Doug Wittmer — ECO Business Systems, Inc.
I'd like to take this opportunity to thank you for the great job you did on updating my Access program. This is quite intricate program which tracks my counter readings,rates and overages. It has simplified how I track this very important part of my business.
Thank you again for the great job you did and I would recommend to anyone who needs any kind of Access programming.
Doug Wittmer
ECO Business Systems, Inc.
Pat – Manager of annas@annashousekeeping.com
Want to strongly recommend Marie Finch for programming. She's dedicated, a hard worker and aims to please and accomplish her clients need. It has been a pleasure meeting and working with such a delightful and smart programmer.
Pat Parker
Manager
Allan J Pisarz — Chief Marketing Officer
Marie, you are a pleasure to deal with and you know your stuff…
Allan J Pisarz
Auto Glass Fitters
Chief Marketing Officer
Leroy — Las Vegas, Nevada
Marie you are the Lady! I'm really impressed with you and your company as my software program needed a lot of work and I was unable to find a company that had the knowledge and programming competence to complete it the way I needed until I found Access Programmer through a web search. I have to admit my project was not easy to complete and I don't always explain things in a manner that a programmer can understand but you were very patient and most of all Honest and up front with the cost involved and the time it would take to complete my project.
I just want to say truly from my heart thank you for a job well done, I finally found a company that knows what they are doing and stand behind the work they do by communicating with me as a client until I was completely satisfied with the final product delivered. I cannot wait to have you work on my next project.
Thank you very much,
Leroy from Las Vegas, Nevada
Allan J Pisarz — Auto Glass Fitters
I have had the the pleasure of working with Marie Finch recently. I must say I was pleasantly surprised. Marie was very helpful in understanding my problem and went to work on it immediately. She explained everything along the way. I highly recommend her.
Allan J Pisarz
Auto Glass Fitters
Chief Marketing Officer
Paul Purdue — Attorney Computer Systems
Marie is THE person to call if you need anything programmed in either Excel or Access. We use her services to create custom reports for our clients on a regular basis, and she communicates well, provides results on time, and is extremely competent.
She is willing to step outside her comfort zone to do special projects for us. For example, she wrote some PHP scripts to interface our CRM system with Mailchimp using their API’s. Neither of these things were part of her core competency, but she handled the project with ease.
Jason K. – IT Manager for Gym Source
I am an IT Manager for Gym Source and have worked on many complex and demanding projects. Since I have had the opportunity of working with Marie Finch on several projects and am familiar with her capabilities, I would highly recommend her.
I have worked with her on numerous Access programming applications and was very pleased with her ability to create new VBA applications as well as her ability to walk through and decipher existing VBA code to debug, enhance and automate them. In addition to her professional capabilities within the IT discipline, she is reliable, honest and trustworthy and can be counted on to deliver the product on a timely basis.
She has written numerous VBA and PHP applications for Gym Source that we use regularly to analyze data and make informed business decisions. Among the categories of applications that Marie has written for us are: Inventory, Accounting, Income Statement, Balance Sheet, Marketing, Financial, etc. Her ability to write technical and end-user documentation is exceptional.
Her programs have undoubtedly enhanced our business capabilities and made my job much easier. I recommend Marie without reservation and could not imagine doing my job as effectively without the programs that she has written.
Ron Gachette AT&T
I highly recommend Marie Finch. She is an excellent programmer analyst who is pleasant, reliable, hard-working, and committed to getting the job done to meet the business need at hand. I have experienced these qualities in Marie first hand at AT&T managing several projects involving MS Access data conversions for our community of 50+ users. These qualities coupled with her technical expertise helped ensure a smooth, successful delivery of the projects.
PC SUPPORT, INC.
“Marie Finch is a remarkable programmer analyst and I recommend her for any database application project. She has worked on Foxpro and Microsoft Access projects with me for use at the Orlando Magic. I’ve known Marie for over 15 years and continue to contract her services for Access programming as the need arises.”
Val Vatterott
PC SUPPORT, INC.* since 1987
President or CEO
pcsorlando.com
Mike O’Leary CEO Perfect Power Golfperfectpowergolf.com
I highly recommend IT Development Services. Having been frustrated by previous web service companies with lack of communication, unreliable meeting deadlines, poor organization and confusing double talk. IT Development has proven to be most professional in communication, very reliable with agreed upon work, organized and easy to communicate with from start to finish. My web sites are complex and detailed in not only marketing but in using email auto responders to generate sales with video presentations along with funnel tracking to upsell customers more advanced product.
IT Developmental Services has not only met but exceeded expectations! I would recommend them to all levels of web based businesses. From beginners to complex web sites if you need more from your webmasters and developers from design to software, from set up to marketing, I have now complete confidence that any and all issues will be solved with open communication, reliability and honesty.
If you have any questions I completely recommend IT Development Services and their President Marie Finch. Honest, Professional, Reliable and willing to work with you to solve any problem is not easy to find in today's web site development industry. I am so very pleased to have IT Development Services as part of my team.
