Updated: Sep 25, 2018
Just about everyone would agree that Excel is the dominant spreadsheet program on the market today. Many people do not consider any other software to store, format, or calculate data. They also use Excel for its powerful graphing tools, pivot tables, and sophisticated macro capabilities.
The most typical way to store data in Excel is in a flat file; in other words, a single table with no predetermined indexing structure. The biggest difference between Excel and Access is the way in which Access allows users to create relationships between multiple tables, making it easy to move beyond flat files.
There are other differences between Access and Excel, of course. In relational database software like Access, each column table column has a fixed datatype, such as text or a particular kind of number. And in Access and other relational database software, the user identifies columns by an attribute name such as LastName rather than by sequential letters (A, B, C, …).
In spreadsheets, there is no difference in how rows and columns function, making it no more difficult to add a column to a spreadsheet instead of a row. In Access and other relational database software, however, rows and columns have different purposes. Rows correspond to information about particular things, people, or events, while columns correspond to properties of those things, people, or events. Once a database has been set up, the user can add or delete rows (records) quickly and easily, but does not typically add or delete an entire column (field) as that would be considered a major structural change to the database. Depending upon the type of relational database software, one might be able to add or delete columns of an already created table, but if it is a large table with a significant amount of data, this action could be time-consuming and demand extensive computing resources.
In Excel, if users want to populate a table based on the values in another table, they can use the VLOOKUP function. While VLOOKUP works, it can be cumbersome and not as self-evident and easy to use as creating links between tables in Access.
The specification of VLOOKUP is as follows:
=VLOOKUP(lookup value, range, column number containing the return value, optional approximate match flag)
Suppose that we have two tables within the same Excel spreadsheet: the first table, on the left, details how much each customer spent each day, and the second table, on the right, records each customer’s name and town of residence.
The last four columns in the first table, currently blank, are meant to contain the name, town, and state of the customer making the transaction. This information is redundant with information in the second table and can also be redundant between rows – for example, the first and third rows should contain information on the same customer, the one with CustomerID C091. So, instead of entering redundant information in these cells, we would prefer to use formulas that populate them dynamically from the contents of the second table.
The VLOOKUP function can perform this task but using it can be tedious, especially if you have a lot of fields. For example, to set up a VLOOKUP formula for the FirstName cell for the first row (for Customer C091 on March 31st), you would enter the following formula into cell C2:
The dollar signs in “I$2:M$5” set up the formula to use the same range when copied to the rest of the column C portion of the table. However, one still needs to modify the “2” in the formula to use it in columns D, E, and F: you should specify column 2 for the values for FirstName , column 3 for the values for LastName, column 4 for Town, and column 5 for State. Here is how the full results look:
As you can see, while VLOOKUP works, it is somewhat tricky to set up and there are many opportunities to make mistakes. If the second table were a lot longer, the table-linking calculations would be noticeably slow, in part because in every row the first table performs four nearly identical lookups into the second table.
With Access, this kind of table-linking process becomes a lot easier and more modular. The data would reside in two separate indexed tables: SALE and CUSTOMER. The only field that would occur in both tables would be CustomerID, the field used to link information between them.
Using Access to “join” the two tables, thus creating a merged data display like cells A2:G8 above, is very straightforward in Access. And it would be much more efficient than in Excel if you had a lot of data: each SALE row would perform only one lookup in the CUSTOMER table, instead of four, and that lookup would work much faster than Excel’s VLOOKUP due to the indexing capabilities that Access automatically provides.
If you are interested in further exploring how to design and use relational databases, please refer to our book, Introductory Relational Database Design for Business, with Microsoft Access. currently available on Amazon and other book websites. If you are an instructor or professor who is teaching a database concepts or Management Information Systems (MIS) course and are interested in using our textbook, please feel free to go to the Wiley website to order an evaluation copy.