MS Access Guide

In This Chapter Identifying your records uniquely with a primary key Understanding relationships Building relationships between your tables Indexing for faster queries Life in today’s world is all about doing things faster and more efficiently to increase productivity. Isn’t that what your life is about? Oh, you have a life outside the office, too? (What a concept.) Maybe you have a relationship? This chapter is about making your databases faster and about building good relationships (the database kind, not the human kind!). As with any good relationship, the end result is often harmony and happiness. Making your Access tables work well together will make things so much easier in so many ways as you move on to build your queries, forms, and reports. The good news is that building relationships in Access takes a lot less time than building human relationships. How can you make Access work faster and more efficiently? With key fields and indexes, that’s how! Each table shoul

In this part . . . Continue building a well-structured database through the use of multiple tables. Customize your table fields to keep data consistent. Learn to efficiently edit your data. Create primary keys to keep your table data unique. Set up relationships between your database tables. Understand relationships for any kind of database and create and maintain indexes.

Nobody’s expecting perfection at this stage of the game. Certainly not in your first foray into database creation, and not even on your second or third attempt. Even seasoned experts forget things now and then, realizing after they’ve built a table that they didn’t need it, or after they’ve started setting up reports and queries that they’ve forgotten a table that they needed. It can happen to anyone. What to do? Use Access’s simple interface to add the tables you want and delete the tables you don’t. One more, please If, after you start building your database, you decide that your database warrants more than one table — in other words, if you realize you need a relational database — then you need to add another table. If you already knew that your database was going to need multiple tables, then — after building the first one — the only thing to do is build the rest, one by one. To add new tables to an existing database, repeat the following steps for each new table: 1. Click

So you’ve read a few posts here at the beginning of the blog, maybe you’ve leafed ahead where I’ve referred to other chapters, and now you feel ready. You want to dive in and start building a database. Keeping in mind my previous advice to take it slowly, you can take a whack at it here. In the following procedure, you set up a new database and then use the Table Wizard to build the first table in the database. Ready? Here we go . . . 1. If Access is not already running, take a moment to start it. Chapter 1 shows you how to do this. In the Access workspace, a series of large template icons appears, below a Search for Online Templates box, accompanied by links to likely searches for templates that store Assets, Business, Contacts, Employee, and so on. 2. Click the Blank Desktop Database icon. A Blank Desktop Database dialog box appears, as shown in Figure 3-1. Figure 3-1: New blank databases need names. Give yours one here. 3. Type a name to replace the generic Databa

Unlike ice cream, databases come in just two flavors: flat-file and relational. Also unlike ice cream, it’s not really a matter of preference as to which one you choose. Some databases require a relational approach; others would be overwhelmed by it. Read on to figure out how to tell the difference. Isolationist tables In a flat system (also known as a flat-file system), all the data is lumped into a single table. A phone directory is a good example of a flat-file database: Names, addresses, and phone numbers (the data) are crammed into a single place (the database). Some duplication occurs — if one person has three phone lines at home, his or her name and address are listed three times in the directory — but that’s not a big problem. Overall, the database works just fine. Tables that mix and mingle The relational system (or relational database) uses as little storage space as possible by cutting down on the duplicated (also known as redundant) data in the database. To a

A field, you remember, is where your data lives. Each field holds one piece of data, such as Last Name or Batting Average. Because there are so many different kinds of information in the world, Access offers a variety of field types for storing it. In fact, Access puts the following field types at your disposal: Short Text Long Text Number Currency Date & Time Yes/No Lookup & Relationship Rich Text Attachment Hyperlink OLE Object Calculated There’s also an Autonumber field type, which is applied automatically to the first field in a new, blank database. The types just listed are those available for fields you create in addition to that first field — the ones that will contain your data. For now, suffice it to say that the Autonumber field is a field that contains an automatically-generated number so that each record is unique in that it has a unique autonumber, or ID. You get the word about the need for (and ways to create) unique fields later on, in Chapter

Now, if the section heading (“Database Lingo”) is making you panic because you think I want you to memorize a bunch of database jargon, don’t worry. Just relax. Breathe normally. The next section, and many throughout this chapter, simply uses some terms you need to know so you can figure out what Access is referring to in its various dialog boxes as well on the various tabs it uses to give you access to commands in the Access workspace. Knowing these terms will, therefore, help you get around and get things done in Access. Unfortunately, you simply must know technical terms — there are no two ways about it. I’m talking about only a handful of words, though — some of which you probably already know and maybe even use in reference to information in general — words like record and database. See? Nothing high tech, just some basic words and concepts you really need to absorb so you can move on and use Access effectively. The terms in this section appear in size order, starting with t

In This Chapter Getting to know some basic terms and concepts Outstanding in your field(s) Deciding to go flat-file or relational with your database Getting a table started This may be the single most important chapter in this guide. That probably sounds like a strong statement, but it’s really true. When you want to figure out how to actually do something, it’s essential to understand not just how it works, but why it works that way, and what’s going on behind the scenes. So after you’ve read about why Access is the right tool for you (in Chapter 1) and how to get around in the Access interface (Chapter 2) , it’s time to really nail down how Access works and how to start building your database.

If you like to use the keyboard as much as possible when you’re working with software, Access makes it somewhat easy to do that. I say somewhat because you need to use a special key in order to make the rest of the keyboard work as a commander. When you want to switch tabs and issue commands with the keyboard (rather than with the mouse), press the Alt key. As shown in Figure 2-20, pressing Alt causes numbers and letters to appear in small squares on the Quick Access toolbar and the Ribbon’s tabs. When the numbers and letters are visible, you can press one of those characters on your keyboard to issue a command (such as pressing 1 to Save) or to switch to a tab (such as pressing C to get to the Create tab). Figure 2-20: Rather press a letter or number than click a tab or button with your mouse? The Alt key shows you how. When you’re on a tab (but only if you press its letter key to activate it), the individual buttons on that tab have their own keyboard shortcuts displayed. Ins

Access, like all Windows applications, is meant to be used with the mouse. The mouse is assumed to be your main way of communicating with the software — clicking Ribbon tabs, buttons, and drop-down lists, and making choices in dialog boxes to use things like the Report Wizard and the Access Options dialog box discussed in the previous sections of this chapter. You can left-click to make standard choices from onscreen tools and rightclick to access pop-up menus — also known as context-sensitive menus because the menu choices vary depending on what was right-clicked. If you right-click a Ribbon tab or button, you get choices for customizing toolbars and buttons. If you right-click a database component tab (say, the Table tab while that table is open), you get choices related to the table. Not a big fan of the mouse? Check out the Cheat Sheet for this blog. It’s full of powerful keyboard shortcuts. Keystroke Function F1 Opens the Help window. Alt+f Opens the File tab.

ScreenTips are the little names and brief descriptions of onscreen tools that appear when you put your mouse pointer over buttons, commands, menus, and many of the other pieces of the Access workspace. make something happen — as when a dialog box opens, Access performs some task for you, or something is created — these things typically have associated ScreenTips that you can choose to view or not view. If you choose to view them, you can choose to see very brief or more elaborate tips. To tinker with Access’s ScreenTips settings, follow these steps: 1. Click the File tab. The File menu (the red panel on the far left) and the Info view appear on the workspace, as shown in Figure 2-17. Figure 2-17: The File tab’s panel of commands gives you an Options command, which you can use to adjust how Access looks and works. 2. Click the Options command, near the bottom of the menu. The Access Options dialog box appears onscreen. 3. From the list on the left side of the Access O

Want to remove a command from the Quick Access toolbar? It’s easy: Point to the unwanted button on the Quick Access toolbar and right-click. Choose Remove from Quick Access Toolbar from the pop-up menu (see Figure 2-15). Voilà! It’s gone. Because the button remains on the tab where it originally lived, it’s not lost — it’s just not taking up space at the top of the Access workspace. Be careful not to remove the default buttons — Save, Undo, and Redo. Why? Because they’re used so often that it’s silly to remove them from such a great location. If you do remove them, you’ll have to use the Quick Access menu button and select them from that menu when you want to use them. That’s two steps (opening the menu and making a selection) instead of one, and who wants to increase steps by 100 percent? Not me! Minimizing the Ribbon Need more elbow room? If you need to spread out and want more workspace, you can make the Ribbon smaller, reducing it to just a strip of the tab titles (wh

Speaking of the Quick Access toolbar and all the ways you can access commands for customizing it, try this to add commands: 1. With any database open (so that the Ribbon tabs are displayed), rightclick any of the buttons on any of the tabs. You can also right-click the Quick Access toolbar or any Ribbon tab. 2. Choose Customize Quick Access Toolbar. The Access Options dialog box opens (shown in Figure 2-13), with its Customization options displayed. Figure 2-13: Pick a command category and a command to add to the Quick Access toolbar. 3. Click the Choose Commands From drop-down list and choose a command category. A list of Popular Commands appears by default. 4. From any (or each) category, choose the commands you want to see at all times in the Quick Access toolbar by clicking them one at a time and then clicking the Add button. As you click the Add button, the command you chose is added to the list on the right. Note that you have up- and down-pointing triangles o

Any good application provides some capability for the user to customize the workspace — from adding and rearranging buttons on the toolbar to dragging toolbars and panes around to optimize the layout. Access is certainly a good software application, so it does what any good application does: It allows you to customize the workspace. You can move the Quick Access toolbar, you can add buttons from the main tabs to the Quick Access toolbar, you can resize the Ribbon, you can tweak the status bar, and you can decide how (or if) your ScreenTips are displayed as you mouse over the tools. There’s no need to do any customization, really — the default settings for toolbar locations, button combinations, and onscreen help are designed with the average or most common user in mind, and they’re pretty good. On the other hand, you may just want to tweak things to feel at home. (Think of the times you’ve fluffed the pillows on the couch before lying down — they may not have needed it, but you wan

When you open a database — be it an existing one or one you’re just starting from a blank database or a template — the workspace changes, offering the Ribbon and its tabs shown in Figure 2-5 (Home, Create, External Data, and Database Tools). These tabs are not to be confused with the database components’ tabs, which appear in the center of the workspace for whichever tables, reports, queries, or forms you’ve chosen to open from the list on the left. Figure 2-5: The main Ribbon tabs — Home, Create, External Data, Database tools — appear when you open a database. When the Ribbon tabs first appear, many of their buttons are dimmed — because they don’t become available until you’re doing something that warrants their use. For example, if you haven’t opened any tables, forms, reports, or queries in your open database, the tools for editing or formatting your database will appear on the tabs, but they’ll be dimmed, which indicates that they’re unavailable. Tools for creating new compo

So you’re ready to dive in. Well done, you! It’s easy to start Access. You can start the application in multiple ways, accommodating nearly any situation you’re in. ( Chapter 1 discusses most of them.) Whether you’re starting Access to view and edit an existing Access database (which gives you what you see in Figure 2-2) or are about to create your own (which opens the application and displays the list of Recent databases and template icons, shown in Figure 2-3), you can get to the tools you need right away. Figure 2-2 shows an existing database open to one of its tables; its other components are listed on the left side of the workspace. When you first open the application (as you also discover in Chapter 1), you’re presented with a workspace that offers three basic ways to make that swan dive into the pool that is Access. You can open an existing database by double-clicking it by name in the Explorer window or from an icon on your Desktop, you can start Access from the Start menu i

In This Chapter Getting started Checking out the tabs, buttons, and menus Using your mouse to get from here to there Letting your fingers do the walking If you skipped Access 2007 and 2010 and are coming to Access 2013 from the 2003 version, you’re probably surprised by the new interface, which was introduced with Microsoft Office 2007. If you did upgrade to Access (or Office) 2010 when it first came out, then the 2013 interface looks very familiar — and you’ll find much of it to be the same as what you’re accustomed to. For those to whom the Office 2013 is a big change, take note of the following changes to the interface, which is strikingly different from what you may have used in previous versions: Menus have given way to tabs and buttons arranged in a strip across the top of the screen — known as the Ribbon. Toolbars are no longer made up of distinct 3-D buttons. Instead, there are buttons and graphic examples of formatting, pictures of what the buttons create, and d