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. It might even exceed the memory limits of your system or the data storage limits of your software program. Because most spreadsheet programs must be able to load an entire spreadsheet file into memory, running out of memory will probably be the first thing that forces you to consider switching to a database.
If you need to change a formula or the way certain data is formatted, you might find 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. For example, in a database, you would store the quantity of items ordered and the price of the items, but you would not usually store the extended cost (a calculated value). You use a query, a form, or a report to define the quantity-times-price calculation.
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.
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 MVPs (Most Valuable Professionals) in the Access newsgroups. Some of the most difficult problems arise in databases that have been created by directly copying spreadsheet data 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 Microsoft Outlook Express or Windows Mail; or you can go to http://support.microsoft.com/newsgroups/defaultaspx, 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.
Take a long look at the kind of work you’re doing today. The sidebar, “Reasons to Switch to a Database,” summarizes some of the key reasons why you might need to move to Access. Is the number of files starting to overwhelm you? Do you find yourself creating copies of old files when you need to answer new questions? Do others need to share the data and update it? Do you find yourself exceeding the limits of your current software or the memory on your system? If the answer to any of these is yes, you should be solving your problems with a relational database management system like Access.
In the next chapter, “Exploring the New Look of Access 2007,” you’ll learn about all the new user interface changes in Access 2007 including the new Ribbon interface element. You’ll also open some of the built-in Access template databases and explore the new Getting Started screen for Access 2007. Finally, you’ll learn about the new Navigation Pane to interact with all your various database objects.