The idea of the split form in Access is rather like its cousin in Project. We set up a view with the “big picture” or large-scale view in one portion of the window (usually the top), and details in the bottom. Sometimes vice versa, but the thinking is to have both available for convenience.
Creating a split form is very easy, as it’s listed within the regular forms. We select the table we want to work with, go to the Create tab->Forms->More Forms, and click Split Form.
The result is to show the data from the table in two ways. One, at top, is the single-record-at-a-time view, which is what forms are for. If the records contain lots of fields, we can edit with ease. The other is the data-table-style view, where we can, for example, compare the record we’re working on with its buddies. If we see something inconsistent, or note some other problem with the table as a whole compared to the one we’re in, it’s easier to fix.
The datasheet form is related, but has a different purpose. It kicks in if you’ve created a one-to-many relationship between two tables. The “one” side of the hookup shows as a single record. The “many” shows the items on the other side of the relationship in the other part of the window.
Again, creating the datasheet form is straightforward, and even less work than the split form. We select the table on the “one” side of the relationship, click Create tab->Forms->Form, and the two parts will come up automatically.
The top is going to show one record, say the supplier of products, and the bottom–the “datasheet form” part–the products supplied.
The key point to be aware of with these is, the upper part (the “regular” form) may need layout adjustments. And this usually requires going into Design view, clicking Form Design Tools->Arrange->Table group to select Remove Layout. Otherwise the arrangement of labels and text boxes will remain locked.
The parameter query, while easy to set up, actually depends on a sort of quirk in the program, as we’ll see. But regardless, it’s a very powerful feature; it allows many variations on one question in a database. Ranges of dates, differences in spelling, and many others can fit a parameter query.
A parameter query on a range of dates uses the “Between—And” keywords, and has the user enter a starting and ending date as indicated. (Any criteria of this sort go in the Criteria row in Query Design view.) The quirk is, when we want the program to ask the user for info, the prompt goes between square brackets, just as if it was a field name.
This forces the parameter query to pause and say, “Huh? That’s not a field I know. Let me ask the user.” At which point it displays the prompt to the user, to inquire what the heck it is. The user answers with the appropriate data (the parameter), and voilà.
But the real power of this feature comes from combining it with the wildcard. This is the “anything-you-like” character. Though this is not universally necessary, it makes the parameter query much more flexible. If a user can only remember part of a name, a couple of letters, or a not-very-specific parameter, it’s okay. We can then use the “like” keyword to help. This says “Find something like this.” Using the wildcard character (*) lets the user enter only what they have and ask anyway. Not as much help with dates, but many of the queries we run involve text. We can put the asterisk on either side, concatenated with the prompt (using “&”). Then there’s no problem having something start with, end with, or contain the letter(s).
So if the user only knows a couple of letters in a company name, they can be entered for searching. If the result includes more than the one name, it isn’t a loss. Even if there are many results, better than not enough, or none, in most parameter query situations.
The action query in Access is a different thing from the normal query we create, called a “select” query. The latter simply selects data and shows the result. But an action query edits data, or even deletes it. And the key thing one needs to know about this is one cannot use Undo to reverse the effect. So though we can easily create action queries, we need to treat them carefully. Luckily, there is a way to test them first.
We can use a food database for our example. Let’s say we need to increase prices on soft cheese (Category 2) due to more touchy shipping conditions.
We create a regular (select) query to find the items in that category, and run it. This is how we test, prior to making it an action query. We see if it pulls up the right data first.
The next step is to return to Design view and go to the ribbon, specifically to the Query Tools Design tab and the Query Type group. Initially, a query defaults to being a Select query. We click the button for what we want now, in this case an (Update) action query.
Then we go into the query grid, click the proper cell in the Update To row, and write the formula to take the action. In this case, [UnitPrice] (the field name) *1.05 (up by five percent), in the UnitPrice column. It’s even possible to make this a variable, or parameter query, but the basic technique is the same.
But the most important item by far is the warning we get next. We’re told the action query can’t be reversed with Undo. And it does NOT say the query will then do what we told it. Nor do we get a “mission accomplished” message. The query simply runs. And changes data.
This means we need to understand that an action query assumes we know it will do this. Therefore if someone doesn’t know, and runs it again, the query will apply the same change again. And again. As long as we are aware, it’s not a problem. But it is a caution we must keep in mind.
Just as there can be subforms and subreports within an Access form or report, there can be a subquery within a query. As the term implies, a “query within a query” allows the main query to be more specific, or complex. It sometimes requires a little bit of Structured Query Language, or SQL. But this is not a problem. Access is a “shell” over SQL as Windows was a shell over DOS, and the two get along fine.
A typical example would be setting up a subquery based on text information, to allow more flexibility with the use of number fields in a database. After opening the DB, we bring up the query window—better to do this in Design view—and add our basic query info.
We can then either switch to SQL view, which some SQL users prefer, or right-click in the appropriate column and row. We then use the Zoom command to write the subquery.
Here we want to ask which products are equal to or less in price than Colby cheese. We want to base the question, though, on the name of the product, NOT the price itself. This is the key.
So in the UnitPrice column of the query (through the Zoom box), we tell the subquery to select (and show) the products from the table (of Products) where unit prices are less than or equal to that of (the product name) Colby cheese. (Note the SQL keywords.) What usually causes a little confusion is that the “less than or equal to” operator comes before anything else. But the nature of comparisons in Access, as well as in SQL, often requires this. And since the comparison is numerical for all that we’re writing a “verbal” formula here, it comes with the territory. Once we know about it, it’s pretty straightforward.
One point not everyone knows, though. If something is edited in the query results, we’re editing the actual data, not a copy. So a query (along with a subquery) can be used to find and correct potentially erroneous or out-of-date info. (One techno-term I’ve heard is “reflexive correction”.)
The use of table relationships in Access allows tables to cooperate in the use of data, but table joins, while they look similar, serve a different purpose. The relationships, which allow coordination while organizing data, nevertheless don’t directly affect, say, the results of a query. Joins do.
A table join in a query allows for a filter effect. The thinking is that a query is a question one asks of the database. But redundant answers are no use, and a waste of space. So when we pull data from more than one table, which is frequently the case, we need to make sure we don’t get repeat answers, or every possible permutation of the answers/data.
When creating the query, we choose the tables (assuming we need more than one, which is normal) and insert them.
If we run the query without a join, we get every possible combination of the field data for an answer…which often leads to repeat results.
So we decide which field(s) to use in the join (this is often dictated by the nature of the database), and specify the nature of the join—usually an “inner” join, which shows only results both tables share. We create the join the same way as we create a relationship, by dragging from a field in one table to its counterpart in the other—the main difference to the program is, the join only gets “activated” when the query is run, saving time and memory usage. There’s also the fact that some joins are only useful at certain points in the database’s operation, so creating a permanent relationship outside the query is unnecessary.
When we run the query with the join, to filter out repeats, the number of answers becomes more reasonable. In many cases, we can use a key field as one of the query result fields, which certainly cuts down on repeats, as a key field has the same effect in a query as it has in a database in general—unique identification of a record. But the important point is that table joins will “insist” on reducing if not eliminating repeats, and will do so even more with a key field involved.
It’s been my experience that very few business database-related queries will have no repeat results at all—the data are such that this seems almost impossible. But if we can eliminate 95+% of repeat data in a query, only the most massive sets of results will have a significant problem with them.
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.
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.
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.)
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.
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.
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.
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.
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.
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.
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).
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.
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).
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.
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.
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.
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 […].
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Once the table / tables are in place you can insert the fields necessary for your query.
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.
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.
Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.
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.
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.
Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.
It really is that simple. The filter you just ran is now a query and can be used whenever necessary.
In this Microsoft Access tutorial, you’ll learn how to import data from a Microsoft Excel worksheet and create a table from it in an Microsoft Access database. To learn more about Microsoft Access, check out our Microsoft Access training courses!
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:
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.
Click on “Navigation Options” from the shorcut menu.
In the Navigation Options dialog box, check the option to “Show Hidden Objects”
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”
In this free Microsoft Access tutorial, you’ll see how to link tables in a Microsoft Access 2010 Query. You’ll also learn the difference between inner joins, left outer joins and right outer joins. Learn more about Microsoft Access in our Microsoft Access Training courses.
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.
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.
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.
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!
Microsoft 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…)