Paul Strauss

Information Management Applications

MS AccessMS ExcelWeb SitesMySQL

What Is MS ACCESS?

Nearly every business depends on the accurate collection, analysis, and reporting of information. MS Access is a complete system that provides these three essential functions. Typically, the data is entered manually into screens (or forms). Since the accuracy of the collected data is of great importance, a great deal of effort is devoted to the creation of the screens in order to insure, as much as possible and practical, that the data entering the system is correct. Reporting is the generation of printed conglomerations of the data that has been collected. The data may be selected and sorted in almost any way imaginable. The analysis of the data is usually done as a report as it generally entails some selection and sorting of the data, perhaps in addition to some further calculations on the data during the report generation. Nearly any kind of information or data can be collected and there are no practical limits on how much data can be collected. It is not inaccurate to say that nearly anything you need to do with your business information, for whatever quantity of data you have or will have, can be done with MS Access.
Typical MS Access applications are:
MS Access is a Relational Database Management System (DBMS). MS Access is one of the best desktop database management systems available. Its name actually explains quite well what MS Access is, given that you understand each word in the name.

Relational

The word Relational actually refers to a rather abstract mathematical notion that describes how the data items in each "record" are associated with each other. What is important to know is that, unlike almost every other DBMS, MS Access has a firm, well-understood, precisely-defined, mathematical foundation. This mathematical foundation insures that when data is stored and retrieved, you can be confident that the data you are storing and the data you are retrieving is exactly the data that you want. Other database management systems that do not have a mathematical foundation cannot make that claim. There are other benefits but what is important for you to know is that a Relational DBMS is a reliable DBMS.

Database

A Database refers to the data that is being managed, all of the data and all of the associated information about the data, also called "metadata". Metadata would be things like the names of your Tables, the Columns in a Table, the Indexes on those Columns, etc. How all of your data and metadata is physically stored and managed is largely hidden and unknown to most people. What is important to know about this is that Access takes care of these details and gives us a reliable way to get data into and out of your database.

Management System

Management System means that it is a complete system for storing and retrieving data. This includes a way to create and manage Forms and Reports, a data storage and retrieval language, and the supporting programming code to enhance and extend the functionality of the Forms and Reports. The MS Access Relational DBMS is a complete package for doing just about anything one might need to do with data.

Structure of an MS ACCESS Application

Every MS Access application (or program) is divided into two physically separate files: the Front-End, where the screens, reports, data retrieval queries (SQL statements), and supporting programming code (VBA) resides and 2) the Back-End where all of the data resides. Breaking an application into two physically distinct parts has several advantages:
This is an important point. Many of the criticisms that people have of MS Access are due to the limitations of the native MS Access data storage engine, the Back-End that comes with MS Access. By using a different, much more capable data storage engine, many of these criticisms become moot. The remainder of the criticisms are almost all due to inexperience and unfamiliarity with some of the more powerful aspects of MS Access. For almost any data-centric application, MS Access is a very capable, robust, reliable, truly industrial-strength database management system and is certainly not the desktop "toy" that its detractors claim it is.

Parts of an MS Access Application

There are several distinct parts that comprise an MS Access application. Even though this discussion may get more technical than one might feel comfortable with, I chose to discuss them because if you do decide to have me create an MS Access application for you, these terms will come up in our discussions and you need to know what they are. The parts are:

Forms or Screens

Most of the time, data is entered into an application by typing it in, by filling out the fields on a Form or Screen. It is possible to load large amounts of data directly into the application but this is not a common way to acquire data. Often, there will be one Form for each kind of data, for example, a Customer Form, a Sales Form, a Payments Form, etc. It is also possible to combine several related functions onto one Form. Your needs will determine which Forms collect one type of data or several types of data. It is not necessary to have separate Forms for data entry and data editing (or maintenance) because data entry is simply a special case of data editing and so both data operations can be easily handled by one Form.
A Form does much more than merely accept or allow the editing of data. The data entered into the application must be correct. Now, what is meant by "correct" very much depends on the data itself. For example, it is possible to buy Zipcode data that lists, for each Zipcode, the city and state. So, in this case, given a Zipcode, the application can make sure that the entered city and state are correct. Short of that, it is still possible to make sure that a Zipcode at least passes a "sanity test" — that is, the entered Zipcode contains only digits (and possibly a dash) and either exactly 5 or exactly 9 digits. While in this case, we can't say that the entered Zipcode actually exists or is correct for the entered city and state, we can at least make sure that it at least looks like a valid Zipcode. It is also possible to correct the capitalization of names and addresses and to supply the USPS preferred address abbreviations.
The Forms must make sure that no data is duplicated. It should not be possible to, for example, to attempt to enter a new part and assign it a part number that already exists; you never want to have two different parts with the same part number. Similarly, it should not be possible to enter the same person or customer more than once.
The Forms must make sure that no data is lost or orphaned. That means if you enter a phone number, it must belong to, for example, one and only one person or company. Until you have established (selected) a current person or company, you can't enter a phone number. Similarly, if you delete a person or a company, all of the data associated with that person or company is automatically deleted from the data. You don't want to end up with a phone number that doesn't belong to some person or company in the data.
These data checks are essential to an application that is robust and reliable. It also takes a long time to determine how much validation is possible, to write the code to do the validations, to reject bad data with a sensible message to the user, and recover from the failed attempt. Because of all of the work that a Form must do to insure correct data, they become very labor-intensive; typically about 80% of the labor that is required to build an MS Access application goes into building the Forms. However, since this is where data enters your application and bad data is worthless, and can even be detrimental if it leads to lost sales or bad decisions, this level of care and effort is essential.

Reports

Most often, data retrieved from a DBMS is presented in the form of a printed Report, sometimes referred to as a "print-out". MS Access has one of the most capable and flexible report-generating facilities to be found in any product. It is generally so easy to create a good Report in MS Access that for almost any Report, it will take between two and four hours to create. For most situations, the native reporting functionality of is sufficient. If more complexity is required, a little bit of coding can greatly extend the already formible, out-of-the-box abilities. Still, if MS Access doesn't readily provide for your needs, it is rather easy to hand-off the data to MS Word and let MS Word print the data. There are actually two ways to do this and the most direct way is to arrange matters so that MS Word is actually doing a mail merge using the data directly from within your database; it can literally reach into your database and grab the data, no copying necessary. A good example of this scenario would be to generate form letters or invoices. If you need to send the printed report to someone, rather than sending the Report output to your printer, you can have MS Access create a PDF which can be emailed and which can be displayed on almost every computer.

VBA

Much of the power, capabilities, and flexibility that MS Access Forms and Reports has is due to the fact that you can extend its out-of-the-box features and create new features that satisfy a specific need or situation. This power comes from the ability to add some programming code, written in VBA (Visual Basic for Applications) to any Form or Report. VBA is used extensively in Forms in order to validate and edit your data. For example, a little bit of VBA can insure that phone numbers have exactly 10 digits and are formatted to your specifications with the proper punctuation, such as 800-555-1212 or (800) 555-1212. VBA can also reach out of MS Access and interact with Windows itself. For example, MS Access can query Windows to find out the Username of the person currently logged on, which is useful if you need to keep track of who has entered or changed some data or to control capabilities on a per-user basis. There is much more that can be done with VBA, but what you need to know is that VBA provides MS Access with the ability to do just about anything you might need or think of doing.

Queries and SQL

Queries are the primary and most common way that data is entered and retrieved from a DBMS. Queries are written in a computer language called SQL (Structured Query Language). Statements written in SQL are simply called Queries, whether they retrieve data or when they update data. You will probably never have an occasion to ever see an SQL statement or Query because they are embedded into Forms and Reports but these terms are often used when discussing your application and it is to your advantage to have some sense of what they mean so I briefly mention them here.

Tables and Rows

Unlike SQL, you will have occasion to see the Tables and Columns in your database. An essential part of the design of your DBMS is layout out the Tables and Columns. This layout will be presented to you in the design documentation that will be part of the design and estimation phase of your project. The Tables and Columns determine what data items will be stored and their proper design is absolutely essential to a properly done application. The importance of properly designed Tables and Columns cannot be over-emphasized. You need to see the Tables and Columns so that you can verify that the data that will be collected is all of the data that needs to be collected. You need not be concerned with your future data needs because a properly-design set of Tables and Columns will have the flexibility to be extended and modified as required in the future.
A Table stores information about one kind of object and only one kind of object. An Object here doesn't have to be something physical like a Part, but it could also be payroll records or Employees. So, in the Table that stores Employee information, things about the Employee, you would find their Name, Address, Maritial Status, etc. The Payroll Table is where you would find each pay period's payroll records. Payroll records aren't information about the Employee but is information that belongs to the Employee. The distinction is perhaps subtle but it is essential. You don't need to understand this well enough to design a Table yourself but you should be acquainted with the concept in order to understand the design documentation. In fact, it can take several years of experience to create a proper design for the Tables in a database.
Columns are part of each Table. A Column holds one kind of information about the object described by the Table. So, in the Employee Table, the Name, Address, City, State, Zip, and Maritial Status would all be Columns because they all describe an Employee. A Table can have several hundred Columns. All of the Columns of a Table constitute a Row. There is exactly one Row for each occurance of the type of object that a Table stores information about. So, there will be exactly one Row for each Employee in the Employee Table. In the Row for a particular Employee, the Columns will hold the information about that Employee (Name, Address, etc.).
A Table may have no Rows, it can be empty, but most important, there is no practical limit to the number of Rows that a Table may have. The Table dynamically and automatically expands to accomodate any new Rows inserted into the Table. We don't have to plan ahead for the amount of data that will eventually be contained in the Tables. Similarly, there can be any number of Tables in a database and a Relational database typically has more than a dozen tables.
You may have noticed that I have not mentioned files and records and fields. The reason is that a Relational DBMS does not store its data in files, records, or fields. In fact, exactly how data is stored is not defined by the mathematical foundations of a Relational DBMS; it is left up to the implementors of the DBMS to determine exactly how the data should be stored. The only thing we care about is that there are things called Tables, Columns, and Rows, that they have certain properties, and act a certain way. Those properties and modes of action are defined by the abstract mathematical foundations of a Relational DBMS. However the implementors chose to implement these things is left up to them. We don't much care how they manage it as long as the resulting implementation has the correct properties and modes of action. Because a Table is not a file, a Row is not a record, and a Column is not a field, the creators of Relational DBMS theory chose not to use the terms file, record and field, and instead use the terms Table, Row, and Column to avoid confusion and to emphasize the fact that we not talking about physical files, records, and fields. Still, as an approximation, you can think of them as equivalent until you become comfortable with the more precise terminology.

Costs

You do NOT need to purchase even one license for MS Access. Microsoft offers a version of MS Access, the Run-Time version, for no cost, that can be installed on as many computers as you wish. You do not need to purchase even one copy or license for MS Access to install as many copies of the Run-Time as you wish. The only difference between the Run-Time version and the full version that is that the Run-Time cannot be used to create or change the application. If you ever want to modify your application, you only need to buy one copy of the full version of MS Access and that retails for around $130. So, you need not spend any money for MS Access itself unless you choose to do so.
As mentioned above, you may need to use a better Back-End program. There are several options here. For every application that I have written that required a better Back-End, I have used MySQL. This is an Open Source, free program, that can be installed on your computer. Do not be put off by the term "Open Source" or by any negative impressions you may have of "Open Source". MySQL is owned and supported by Oracle and Oracle is continuing to support the development of MySQL. Furthermore, you have probably used MySQL, although you may not be aware of it, because many web sites and shopping carts are written using MySQL. It is a data storage engine that is, for almost any purpose, as good as any commercial offering from Microsoft or Oracle itself.
What you are probably most interested in is knowing is: what will my project cost? Unfortunately, the best answer I can give you is, "It depends." At this point, it is like walking into an architect's office and simply asking him, "How much will it cost to build a house for me?" Without knowing anything about how big the house is, the number of rooms, the amenities, etc., about all he could say is that the minimum price for even the smallest, plainest, house is $150,000. Similarly, about all I can say is that the smallest, plainest application is going to cost at least $2,000. This cost is almost certainly all labor — you are paying only for the time it takes me to create your application. There are no hardware costs and if there are additional costs, they are usually trivial. After we have discussed your needs, I will be able to offer a rough estimate. If you want a firmer price, then I will have to do some design and analysis so that I can get a better sense of how much time will be necessary to complete your project. This will be a detailed design and analysis resulting in a written document; expect it to take three to five days to produce this document.