Using the Three Normal Forms in Microsoft Access

One of the more abstruse points of procedure in Access involves the three so-called normal forms. What, exactly, are they? If you think “guidelines”, or “protocols”, you’d be in the ballpark.

When building a database, especially a relational database, there are some “streamlinings” which allow it to function more efficiently. Knowing the how is important, but the why can be too. “Why” is what I’d like to go over here.

The first of the normal forms is basically this: In any field, in any record of the database, there should be one and only one piece of information. The simplest examples are things like phone numbers.

First Normal Forms Wrong

Since today computers can be hooked up to, and can dial, phones, if two numbers are in one field, the computer might not be able to tell which one to dial, or it might think they were one long number. And that would mess the process up.

First Normal Forms Right

It also allows easier querying, filtering, and other kinds of analysis of data. Another common example is entering addresses: If the street address, city, state, and Zip code are all in one field, sorting or filtering by any of those would be much more difficult. But give each one a separate field, and it’s quite straightforward. This is part of what’s known as normalization. If the data are divided into the smallest pieces which still have meaning, you have raw ingredients in the kitchen—you can do more with those than, say, premixed pancake batter, which is pretty much only good for pancakes.

The second of the normal forms is about key fields—the ones that act as unique IDs for each record, in tables which need them. (The usual example here is a Social Security number, or employee ID number, or SKU number for merchandise.)

Second Normal Forms

It is possible for a table to have two, or more, key fields per record, and occasionally this is necessary (as in a table of merchandise orders). But the uniqueness should then depend on the entire set of key fields (a composite key), not just one, because otherwise it will be possible to have duplicate data in the table, and this is a major no-no. (This is basically the second normal form.) Duplicate info wastes space, and can confuse query results. If any of the fields’ data don’t depend on the whole key (multiple fields), they should be in another table.

Third NF Redundant

The third of the normal forms is a little more complicated to explain, but the point is simple: Redundant information should be in a separate table. One situation in the real world has to do with employee databases including health plan info. There’ll be a number or letter indicating which plan someone is on, and then in the next field, the name of the plan. But if these are the same each time, why not use just the letter in the employee table, and put the detailed information in another table? It reduces the amount of typing someone has to do, and simplifies the table in question.

Third NF Right

It also offers a hidden benefit. If one puts the letters for the plans in another table as the primary key, and has plan names or other data in other fields in that same table, changing data in the plan table will affect everyone who uses the plan letters at once. This means that the subset of data regarding plan details is centralized, and can easily be changed for everyone with a minimum of work. So a sort of corollary to the third normal form is, one kind of data per table (if redundant data is a second kind per table, this falls into place pretty neatly).

One piece of info per field; one key field per table where possible, and use all the key fields for ID if not; split off redundant data to another table, which also offers easier changes to some kinds of data. Those are the essentials of the normal forms. There’re other data models, and other operating ideas, but since Access is so commonly used, understanding how to make it run better is good.

How to Import and Export Data in Access

The good news is, the import and export processes in Access—and in general—are two sides of the same coin. And there really isn’t any bad news, other than making sure the data are set up in a way that Access can understand—namely, something row-and-column-ish when importing. There’s even an External Data tab in the ribbon, so it’s just a matter of knowing what kind of data you’re bringing in, or what format the recipient needs if you’re exporting.

Import Start

For an import, other than making sure you know where the data are, simply go to the External Data tab, and look in the Import and Link group. We’ll use a text document here.

Import Box

We can choose whether this will start a new table, be appended to an existing table, or link to the data source (which allows updates, but has some limitations).

We also have to specify where the document is. We can do this in any order, but we have to do both.

If (as in this case) we want to append the data to an existing table, we need to tell the dialog box which one. And, though the box doesn’t say, we have to make sure the incoming info is set up to fit in the same “slots” (fields) as the existing info. (Opening the file and checking in advance is a good idea here.)

Once this is done, we go through the File Import wizard, which lets us see how the incoming data will work.

Import Step 1

We choose “Delimited” or “Fixed Length” according to how the pieces of info are marked off (a delimiter is a marker telling us where things start and stop).

Import Step 2

We then indicate what kind of delimiter is used (commas, tabs, and semicolons are among the more common ones); and let the program do the rest.

Import Step 3

Exporting is even simpler. We select (but don’t have to open) a table, query, or other object, go to the External Data tab, and in the Export group, click the type of receiving file we need to create, and specify location and file name (the program will default to the object name).

Export Box

So, “Where are you getting the data?” and “Where are you putting the data?” are the only two questions we have to be specific about. Access pretty much handles it from there.

Creating Calculated Fields in Microsoft Access

When setting up reports in Access, it is sometimes necessary to learn something which wasn’t in the original data, whether calculated, derived, or otherwise figured out from the existing information. It’s fairly easy to do, though there are a few steps involved.

After determining the requirement—a sales tax or a shipping fee, and what calculation is needed to find it—we create the field for the calculation.

Text box

With the report open in Design view, clicking the Design tab under Report Design Tools gives us the Controls group. There, we click the Text Box tool and click or drag where we want the text box (which shows field data). This normally creates a label too, which one can use or delete as desired.

Calculated field

Once the text box is in place, we leave it selected, open the Property Sheet, and click the Data tab. One of the Data properties is Control Source, where we can tell the text box how to get its data. On the right of this property, we bring up the Expression Builder with the Build button […].

Expression builder

In the Expression Builder itself, we can either double-click the fields we need for data or type in what we want manually. We also add the other items (operators, additional figures, and so on) to perform the calculations—essentially, we build a formula much like those in Excel. (Don’t need the equals sign at the front, though.) This makes it a calculated field.

Field and Label

Once we OK out of the box, we can fine-tune the position and size of the text box, and either move the label or create one of our own so the users of the report will know what it’s showing. (This is more important than some people think—in a business report it’s very important not to make assumptions about who’s going to see it, and no guesswork should be involved in understanding it.)

The term “calculated field” seems kind of unofficial—one usually sees it called an “unbound field”, as it isn’t directly connected to any of the actual database data. But since we often create such fields to derive info from other info, and it’s often mathematical, saying calculated is at least logical.

Data Types, Properties, and Relationships in Access

A fundamental aspect of Access databases is the setup of relationships between tables. It’s usually the nature of business databases to have them, so the actual creation of the relationship isn’t hard to do. But there are a couple of not-so-obvious things the user needs to know.

Relationships Window

Once the database is open, and the tables have been created, the first thing is to open the Relationships window from the Database Tools tab–>Relationships group. If there are no relationships already in place, the Show Table dialog box will open automatically. The user can then double-click the tables needed, or select them and click Add, which places them in the main window.

Table Hookup

Then, dragging from a field in one table to its counterpart in the other will establish the relationship. (We usually check Enforce Referential Integrity, as we normally want something in one table to have a counterpart in the other.) Click Create, and the basic hookup is done.

The background behind this is a little more subtle, but not that hard to understand. Some newer users think the field names have to match for the relationship to work, but this is not true. The program doesn’t require it, but it’s highly recommended that field names be consistent across tables, so anyone who needs to look at or use the database won’t get confused.

The main thing, or rather things, that need to match up, are two of the properties of the fields—specifically, the Data Type and Field Length. These can be easily checked, or modified, by going into Design View once a table is open and selecting the field in question.

Data Type

The reason is, the program can’t assume what will be stored in a given type of field—say, some numbers—will work with something in another field type. If the types match, they should. And if the lengths are the same, fifty characters for example, then what’s in one field can fit in the other.

Field Length

One analogy which I’ve heard, which seems to help explain this, is the idea of two railroad cars. In different countries, one sometimes finds the rails are different distances apart—in the US, it’s four feet, eight and one-half inches. If you want to couple two cars together, they have to ride on the same gauge (distance apart) of rails, and they have to have the same couplers. If these two things are correct, all is well. One might also think about a car—if it’s supposed to run on diesel, you shouldn’t put gasoline in the tank, and vice versa.

So, field names should match, but data type and length have to. Simple enough, once you know it.

How to Normalize Data in Microsoft Access

The term “normalization” gets thrown about quite a bit in database circles, especially to try to explain to newer users a couple of principles of data organization, but seems rather vague to someone not acquainted with database-ese.

The idea of making data “normal” is not too far from the meaning used by database designers, when one understands what “normal” is. When we create, say, a batch of file cards with names, addresses, etc., we tend to lay out the information similarly—consistently—so as to make it easier to follow on each. If each piece of information is in its own place, a “field” or space designated for each, we’re already close to the idea of normalization. One wouldn’t want to put the first and last name in the same field, since we might want to sort by last name first. We couldn’t do it easily if the two were in the same place. Nor would we want to put street address, city, state, and zip code in the same field, for the same reason.

Bad Addresses

So one rule we try to follow regardless of what kind of database software we use, MS Access for example, is that we should put one piece of information in each field. A rough analogy would be, if one wanted to fill a glass jar’s volume completely, using something like golf balls or hard candies would not work too well, as they’re too big to really follow the shape of the jar. But using gravel, or sand, or better still, flour, would be better. The individual pieces are smaller, and can fit the space much better. The smaller the pieces, the more “flexible” they are in filling the space.

Jars

Secondly, it’s a good idea to divide the tables (or collections of data) into categories—an employee database might contain a table of personal data, a table of office data, one of health plan data, one of travel/transport data, and so on. Turns out it’s easier for most databases—and users—to work with a larger number of smaller tables than the other way round.

Finally, there is at least one exception. Some kinds of data, such as addresses, work better with a little de-normalization. (Access makes this fairly easy to see.) We don’t want to put a zip code in the same field with city and state, but putting city, state, and zip fields in an address table—even though slightly redundant—make the addresses much easier to understand and use.

Address Table

Align Fields in Access Forms

Working with Microsoft Access can be kind of intimidating when you start, especially because there seem to be so many details to absorb and keep track of. And even fairly experienced database people have to watch out for the little things.
One which really drives some new Access users up the wall has to do with forms. Doing form layout is an art unto itself, and getting a good layout can take time. Even after you’ve got it mostly cleaned up, the program has one particular nitpick some people don’t even notice. At first.

Form Design View
When you work on a form in Design view, the most time-consuming thing is sizing and moving fields (officially known as text boxes, where data show up) and labels (to tell you what the fields contain). And making sure the Tab key gets you from field to field in a logical order is important. Luckily, we can use the Tab Order dialog box to do this.
Tab Order Box

At this point, the tab order doesn’t match the order of the fields because we rearranged them. We want to tab left to right, top to bottom. So we arrange our fields, bring up the box, and click Auto Order.

Auto Order Button
BUT!…if any of the fields are not aligned just right, by their top edges, with their buddies in the same row, whoever’s highest gets dibs in the tab order. (“D’oh!”)
So we select the fields and labels in question, go to the Arrange tab->Sizing and Ordering group, and click the Align button. In the dropdown, we click Top to get everything in a given row to line up.

Align Top Command

Problem solved.
Then, go back to the Tab Order box, click Auto Order once more, and test. That should do it.
Yes, it’s kinda nitpicky. But it’s worth it, as getting this sort of thing right can improve data entry speed in Access, and other database programs quite a bit…and win you the approval and accolades of the people who do it.

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…)