The first step is to be clear about the difference between Excel and Access, and when Access can offer greater value. Our article on What is Microsoft Access Used For? is a great starting point. In short, if you need to track a large volume of information, need to produce polished reports, gather input from many users efficiently, or perform validations to ensure your data is consistent, Access is a far more reliable tool than Excel. Lastly, it is important to know that there is most certainly a learning curve to get up to speed with Access. But the time you gain overall by reducing manual and repetitive work in Excel far outweighs the time it takes to learn Access as a new skill.
Once you know Access is the right next step for you, it's important to begin by creating your Access database structure. Unlike Excel where the data is stored in a "flat" manner, with all related information in a single row in an Excel worksheet. In contrast, Access stores related information in single table, and then ties it all together by using numeric "keys." For example, there would be a table that might just store customer data [customer name, phone, address], and another table that would store orders [order amount, date, taken by whom, sales tax]. To join the two together so you would know which customer an order is for, you would use a unique customer identifier [stored in the customer table] that would then be placed into the order record. With this in place, you can at any time query both the orders table and customers table to get a full view of a specific order [including all related customer information] without ever repeating any information.
Determining how to structure your Access database takes time and a learning curve. But it is perhaps one of the most important steps you need to take. Access can help you analyze an imported Excel file, you can use a commercial product like Designer for Access to help speed you along, or you can take on the task manually using the Access table Design View. In any of those cases, you will want to:
- Decide what the main things you want to track are. These are at the highest level, like: Customer, Products, Books, Students, Customers, etc.
- For each item in step [1] above, what are the specifics you want to track. So for Customers, what do you need to keep, such as Customer Name, Address, Zip Code, City, State, Market Segment, etc.
- Finally, decide how the items in step [1] relate to each other. This is a complex task in some cases, and may take studying books on database design. If you use Designer for Access the product helps decide that for you by asking two simple questions about each main thing you are tracking.
- Once you know how things relate, you are ready to begin creating your tables. For each item in step [1] above, create a table.
- Add the items from step [2] to the corresponding table as fields. Note that in Excel, a cell can contain anything [text, number, date], whereas Access will ask you to specifically say what you will store in the field. This again is a way to enforce consistency, make certain data entered is correct [you wouldn't want someone entering 'about $2000' in the amount of sale], and that you can query the data to calculate and report on it correctly.
- Finally, step [3] above will tell you if you need to place a reference to a unique key in another table [for example, a "CustomerID" reference in your Orders table], or possibly create a many-to-many join table [for example, if an order may include many individual products, you would need a Products table, an Orders table, and a table to make sure your order could include multiple products].
Once you are satisfied you have
all the necessary elements to store in your database [tables and fields], you can either start entering new data into your Access database, or even migrate your Excel data into your new Access database. OpenGate has published an online tutorial for how to bring in "flat" Excel data into a relational database structure. While the video is specifically for a Designer for
Access-created database, the steps are applicable even if you created the database on your own.
If only it were that easy! You can indeed import Excel into Access and have a table that looks almost exactly like your Excel worksheet. The problem, of course, is that you won't realize the benefits of Access as a relational database. Access does have a built-in table analyzer that may be able to help you split your spreadsheet into multiple related
tables. However it presumes you have a good deal of database expertise to use. We suggest following the steps above to make sure you are ultimately successful with your project, and Access.
With millions of users, there is a vast wealth of resources to help you. From message boards, Access books, tutorials, blogs, and MS Access addins, you will have many resources to help you succeed.