Access vs Excel—Which should we use for what?

Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

On the other hand, if you need to track something more complicated, such as information on the employees of a small (or large) company, you may run into trouble with Excel. The problem is, you may need to have three or four kinds of information that all relate to the employees, but are not directly related to each other: Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time). Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number—make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from—the different categories of information nevertheless relate to each other, like a baseball team where each player has a separate job, but all cooperate to play and win.

If this idea makes sense, that we separate the types of information but allow them to relate through one item, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. As a bicycle is good for getting around by yourself, but a pickup truck can haul some serious cargo by comparison, so Excel and Access. (Sort of.  🙂  )

As a postscript, the reason I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel—is that it’s a different tool again from Access, though it is useful. And not everyone knows about it, or how it works. We do teach it at SkillForge, but again, it’s a somewhat specialized tool, whereas Access is a more general database application and has more multi-user features, making it more suitable for business database work.

Access 2013: Creating a Calculated Field

Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table.

Microsoft Access 2013 makes this very easy.

To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need.

create-query-design

add-tables

Once the table / tables are in place you can insert the fields necessary for your query.

insert-fields

The calculated field will be entered in the next available column. It is often easier to see what you are typing if you open the Zoom window. That can be accomplished by Right-clicking the open field and selecting Zoom from the Context Menu.

zoom-tool

With the Zoom window open we can enter our calculation. The information to the left of the colon represents the caption we want at the top of the column. The information to the right of the colon is our calculation. In this case it is the curUnitPrice field. No table name is necessary because we are only using one table. The field name is wrapped in a pair of square brackets and is being multiplied by 0.1.

enter-code

Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.

save-the-query

calculated-field

Access 2013: Saving a Filter as a Query

You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database.

filter-number-filyers-greater-than

enter-a-value

filter-results

Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple.

Go to the File tab and choose Save As > Save Object As > Save As.

file-save-as-save-object-as

Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.

as-query

It really is that simple. The filter you just ran is now a query and can be used whenever necessary.

query-results

 

Show Hidden Tables in Microsoft Access

If you’ve ever inherited a database someone else created in Microsoft Access you may have run across the dreaded “hidden-table” scenario. This is when the original developer of the database has hidden (probably in an effort to keep anyone from disturbing them) the source tables that contain your data from the Navigation Pane in Microsoft Access. You’ll realize this has happened, typically, because you’ll go to create a query and you’ll see a tables or tables as being available to include in your query, but strangely, you don’t see those same tables listed in the Navigation Pane in Access. To fix this problem in Microsoft Access 2010 you can:

  1. Right-click on the title of the Navigation Pane (It probably displays the words “All Access Objects” but may say “Tables” or “Queries,” etc. if you have changed your Navigation Pane View.
  2. Click on “Navigation Options” from the shorcut menu.
    access navigation pane
  3. In the Navigation Options dialog box, check the option to “Show Hidden Objects”
    show-hidden-objects
  4. The hidden Access tables should now appear, but are “grayed out.”  You can still open them, but to unhide them permanently, right-click on the table you want to unhide and select “Unhide in this Group”
    unhide-in-this-group

Want to learn more about Microsoft Access? See our Microsoft Access Training Courses or view our Microsoft Access Tutorials on YouTube.

How to Use VBA in Microsoft Access Tutorial


In this Microsoft Access Tutorial, you’ll see how to begin using VBA in Microsoft Access 2007. The basic concepts of objects, properties methods and events are explained and then you will see how to attach VBA code to the load event of a Microsoft Access Form and to the click event of a Command Button on a form. This tutorial is from our live, instructor-led online course: Microsoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.

Using ADO to Retrieve Data : Microsoft Access Tutorial

access-ado-vba-data
In this Microsoft Access Tutorial you’ll see how to connect to a database using ADO Connection, Command and Recordset objects in VBA code, how to populate text boxes from fields in the data retrieved, and how to create command buttons that use ADO Recordset methods to allow the user to move throughout the data. This content is from our live, instructor-led online MIcrosoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.

Microsoft Access Training

SkillForge is proud to announce the most comprehensive Microsoft Access Training schedules available anywhere! Offering Introduction to Advanced courses in Visual Basic for Applications (VBA) – SkillForge offers training for students wanting to learn more about the Microsoft Access application no matter what level of experience they have.

Students can choose from:

And topics range from the basics of creating tables and queries to writing VBA code. And as a special bonus – all students enrolling in a Microsoft Access Training course will receive free either a self-paced, interactive e-learning course to review after class, or a Cert-Blaster skills assessment exam to assess their knowledge both pre and post-class! See our complete list of Access training courses and find your training in Access today!

MS Access Resources

ms-accessMicrosoft has recently published a very thorough set of MS Access 2010 resources to the TechNet site. Access has always been the part of the Office Suite that straddles the end-user/IT Professional fence. The resources on TechNet are definitely geared toward Access Developers rather than folks that might be using MS Access more casually. (more…)