The first step is to be clear about the
difference between Excel and Access, and when Access can offer
greater value. Our article on
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
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
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:
The process of database design is usually iterative, you'll need to work through it several times to make sure you
get all the tables and fields you want.
- 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.
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
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
and MS Access addins, you will have
many resources to help you succeed.