Deciding to Move to Database Software

When you use a word processing document or a spreadsheet to solve a problem, you define both the data and the calculations or functions you need at the same time. For simple problems with a limited set of data, this is an ideal solution. But when you start collecting lots of data, it becomes difficult to manage in many separate document or spreadsheet files. Adding one more transaction (another contact or a new investment in your portfolio) might push you over the limit of manageability.

If you need to change a formula or the way certain data is formatted, you might find that you have to make the same change in many places. When you want to define new calculations on existing data, you might have to copy and modify an existing document or create complex links to the files that contain the data. If you make a copy, how do you keep the data in the two copies synchronized?

Before you can use a database program such as Access to solve problems that require a lot of data or that have complex and changing requirements, you must change the way you think about solving problems with word processing or spreadsheet applications. In Access, you store a single copy of the data in the tables you design. Perhaps one of the hardest concepts to grasp is that you store only your basic data in database tables.

You can use the query facility to examine and extract the data in many ways. This allows you to keep only one copy of the basic data, yet use it over and over to solve different problems. In a sales database, you might create one form to display vendors and the products they supply. You can create another form to enter orders for these products. You can use a report defined on the same data to graph the sales of products by vendor during specified time periods. You don’t need a separate copy of the data to do this, and you can change either the forms or the report independently, without destroying the structure of your database. You can also add new product or sales information easily without having to worry about the impact on any of your forms or reports. You can do this because the data (tables) and the routines you define to operate on the data (queries, forms, reports, macros, or modules) are completely independent of each other. Any change you make to the data via one form is immediately reflected by Access in any other form or query that uses the same data.

Reasons to Switch to a Database

Reason 1: You have too many separate files or too much data in individual files. This makes it difficult to manage the data. Also, the data might exceed the limits of the software or the capacity of the system memory.

Reason 2: You have multiple uses for the data—detailing transactions (invoices, for example), and analyzing summaries (such as quarterly sales summaries) and “what if” scenarios. Therefore, you need to be able to look at the data in many different ways, but you find it difficult to create multiple “views” of the data.

Reason 3: You need to share data. For example, numerous people are entering and updating data and analyzing it. Only one person at a time can update a word processing document, and although an Excel 2003 and later spreadsheet can be shared among several people, there is no mechanism to prevent two users from updating the same row simultaneously on their local copies of the spreadsheet, requiring the changes to be reconciled later. In contrast, Access locks the row of a table being edited by one person so that no conflicting changes can be made by another user, while still permitting many other users to access or update the remaining rows of the database table. In this way, each person is working from the same data and always sees the latest saved updates made by any other user.

Reason 4: You must control the data because different users access the data, because the data is used to run your business, and because the data is related (such as data for customers and orders). This means you must control data values, and you must ensure data consistency.

If you’re wondering how you’ll make the transition from word processing documents and spreadsheets to Access, you’ll be pleased to find features in Access to help you out. You can use the import facilities to copy the data from your existing text or spreadsheet files. You’ll find that Access supports most of the same functions you have used in your spreadsheets, so defining calculations in a form or a report will seem very familiar. Within the Help facility, you can find “how do I” topics that walk you through key tasks you need to learn to begin working with a database, and “tell me about” and reference topics that enhance your knowledge. In addition, Access provides powerful wizard facilities to give you a jump start on moving your spreadsheet data to an Access database, such as the Import Spreadsheet Wizard and the Table Analyzer Wizard to help you design database tables to store your old spreadsheet data.

Inside Out: Design Considerations When Converting from a Spreadsheet to a Database

You can obtain free assistance from us and many other Microsoft Most Valuable Professionals (MVPs) in the Access newsgroups. Some of the most difficult problems arise in databases that have been created by copying spreadsheet data directly into an Access table. The typical advice in this situation is to design the database tables first, then import and split up the spreadsheet data.

You can access the newsgroups using Windows Mail, or you can go to http://support.microsoft.com/communities/newsgroups/default.aspx, and in the Community Newsgroups column on the left, expand the Office category and then the Access category to see the available newsgroups. Click one of the links to go to that newsgroup within your web browser, where you can post questions and read answers to questions posted by others.