Microsoft Excel and Access are the two most common databases used to modernize today’s office environment. This modernization brings with it efficiency, reliability, security and, more importantly, frees up your staff to deal with your actual ‘profit’ orientated business needs.
While both programs are based on a database structure, they are distinct differences, that will define how, and when, they are used. Excel is very flexible and easy to use, and has a short learning curve. It’s plasticity allows many, intricate, formula based functions and, as such, is great for creating forms, reports and analyzing data. A draw back to Excel though, is its limit to a finite number of records, albeit a extremely large finite number. And at very large file sizes (containing many records) automatic calculations can be slow.
Access on the other hand can handle limitless records and has the ability of using relationship databases, this means many smaller manageable tables can be created that can be interlinked in database searches and reports. The downside to relationship databases is it’s relative inflexibility (only restrictive uses of formula driven searches and reports) and a very long learning curve.
Knowing, and understanding these differences, can make or break the success of your office automation.
Here are a few samples to review:-
This is a sample invoice created for a small to medium retail business using Excel (in this sample it is a bakery) and takes away the need for a cash register. For security all cells are locked except for two, the quantity and the amount tendered.
The invoice can be personalized by adding your logo and address. The date field is updated using a date picker, so no changing of date formats to confuse the auditor. The invoice number is automatically increased by “1” every time it is saved/printed
The sales items are selected from a preset drop down list, thus no chance of different names for the same item, The price is automatically entered based on the sales item selected. The discount is automatically entered based on the quantity that you input. The sub-total is automatically calculated to include the discount and VAT.
The delivery location is selected from a preset drop down list and amount is automatically entered (inclusive of VAT) based on the selection. The total is automatically calculated (including all VAT). Once the amount paid is entered the invoice automatically calculates the customer’s change to the nearest 5 cents.
The ‘Create New Invoice’ button automatically prints the existing invoice to a local printer, saves the invoice to a folder on your computer and saves the sales details to a sales registry, also on your computer.
This is sample of a table. It allows the users to calculate their property tax based on government determine tax tables.
The user selects their property type from a drop down list, then enters their estimated value and the table automatically calculates their property tax.
This table, like the invoice above is locked so the only cell that the user can input data is the cell where they list the property’s estimated value.
This is another sample of a table. This calculates the customer’s total mortgage fees (Government/Bank/Lawyer) for a given mortgage amount.
The customer selects the property type (as defined by our legal system), then the purchase price, the mortgage amount, mortgage interest rate, years of mortgage and the bank’s fees (as either an interest rate or flat fee).
The table then calculates the stamp duties, Deed taxes, lawyer fees, bank fees and gives you their breakdown and total. It also calculates your monthly mortgage payment.
Like the above two samples all cells are locked except for those requiring input from the user.