Ken Cook

Cheat Sheet / Updated 03-10-2022

Access 2016 makes managing data easy, enabling you to set up a database quickly, enter records into that database, and then use the data however you like. With Access 2016, you can organize data into tables, design forms for editing and reports for presenting, and create queries for finding information!

Cheat Sheet / Updated 02-16-2022

Access makes managing data easy, enabling you to set up a database quickly, enter records into that database, and then use the data however you like. With Access, you can organize data into tables, design forms for editing and reports for presenting, and create queries for finding information!

Article / Updated 02-25-2019

Access 2019, the latest version of the Microsoft Office database application, has always been a powerful program, and this version is no different. All that power makes Access an application that’s not so easy to learn on your own. You don’t have to use every feature and tool and push the edges of the Access envelope. In fact, you can use very little of everything Access has to offer and still create quite a significant solution to your needs for storing and accessing data — all because Access can really “do it all” — enabling you to set up a database quickly, build records into that database, and then use that data in several useful ways. Later on, who knows? You may become an Access guru. What is Access good for? That’s a good question. Well, the list of what you can do with it is a lot longer than the list of what you can’t do with it — of course, especially if you leave things like “wash your car” and “put away the dishes” off the “can’t do” list. When it comes to data organization, storage, and retrieval, Access is at the head of the class. Building big databases with Alexa Okay, what do I mean by big database? Any database with a lot of records — and by a lot, I mean hundreds. At least. And certainly if you have thousands of records, you need a tool like Access to manage them. Although you can use Microsoft Excel to store lists of records, it limits how many you can store (no more than the number of rows in a single worksheet). In addition, you can’t use Excel to set up anything beyond a simple list that can be sorted and filtered. So anything with a lot of records and complex data is best done in Access. Some reasons why Access handles big databases well are: Typically, a big database has big data-entry needs. Access offers not only forms but also features that can create a quick form through which someone can enter all those records. This can make data entry easier and faster and can reduce the margin of error significantly. When you have lots and lots of records, you also have lots of opportunities for errors to creep in. This includes duplicate records, records with misspellings, and records with missing information — and that’s just for openers. So you need an application such as Access to ferret out those errors and fix them. Big databases mean big needs for accurate, insightful reporting. Access has powerful reporting tools you can use to create printed and onscreen reports — and those can include as few or as many pieces of your data as you need, drawn from more than one table if need be. You can tailor your reports to your audience, from what’s shown on the reports’ pages to the colors and fonts used. Big databases are hard to wade through when you want to find something. Access provides several tools for sorting, searching, and creating your own specialized tools (known as queries) for finding the elusive single record or group of records you need. Access saves time by making it easy to import and recycle data. You may have used certain tools to import data from other sources — such as Excel worksheets (if you started in Excel and maxed out its usefulness as a data-storage device) and Word tables. Access saves you from reentering all your data and allows you to keep multiple data sources consistent. Creating databases with multiple tables Whether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database — and that’s Access. How do you know whether your data needs to be in separate tables? Think about your data — is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database. The Big Organization database Imagine you work for a very large company, and the company has data pertaining to their customers and their orders, the products the company sells, its suppliers, and its employees. For a complex database like this one, you need multiple tables, as follows: One table houses the customer data — names, addresses, phone numbers, and email addresses. A second table contains the customers’ orders, including the name of the customer who placed the order, the salesperson who handled the sale, shipping information, and the date of the order. A third table contains information on the products the company sells, including product numbers, supplier names, prices, and the number of items in stock. A fourth table contains supplier data — about the companies from which the main organization obtains its inventory of products to resell to customers. The table contains the company names, their contact person, and the address, email, and phone number information to reach them. A fifth table contains employees’ data — from the date they were hired to their contact information to their job title — and also contains notes about them, sort of a summary of their resumes for reference. Other tables exist, too — to keep a list of shipping companies and their contact information (for shipping customer orders), an expense table (for the expenses incurred in running the business), and other tables that are used with the main four tables. Because you don’t have to fill in every field for each record — in any table in the database — if you don’t have a phone number or don’t know an email address, for example, it’s okay to leave those fields blank until you’ve obtained that information. Fail to plan? Plan to fail If you think carefully about your database, how you use your data, and what you need to know about your employees, customers, volunteers, donors, products, or projects — whatever you’re storing information about — you can plan: How many tables you’ll need Which data will go into which table How you’ll use the tables together to get the reports you need Of course, everyone forgets something, and plans change after a system has already been implemented. But don’t worry — Access isn’t so rigid that chaos will ensue if you begin building your tables and forget something (a field or two, an entire table). You can always add a field that you forgot (or that some bright spark just told you is needed) or add a new table after the fact. But planning ahead as thoroughly as possible is still essential. As part of thorough planning, sketch your planned database on paper, drawing a kind of flowchart with boxes for each table and lists of fields that you’ll have in each one. Draw arrows to show how they might be related — it’s sort of like drawing a simple family tree — and you’re well on your way to a well-planned, useful database. Here’s a handy procedure to follow if you’re new to the process of planning a database: 1. On paper or in a word-processing document, whichever is more comfortable, type the following: A tentative name for your database A list of the pieces of information you plan on getting from that database on a daily or regular basis 2. Now, based on that information, create a new list of the actual details you could store: List every piece of information you can possibly think of about your customers, products, ideas, cases, books, works of art, students — whatever your database pertains to. Don’t be afraid to go overboard — you can always skip some of the items in the list if they don’t turn out to be things you really need to know (or can possibly find out) about each item in your database. 3. Take the list of fields — that’s what all those pieces of information are — and start breaking them up into logical groups. How? Think about the fields and how they work together: For example, if the database keeps track of a library of books, perhaps the title, publication date, publisher, ISBN (International Standard Book Number, which is unique for each book), price, and page count can be stored in one group, whereas author information, reviews, and lists of other titles by the same author or books on the same topic can be stored in another group. These groups become individual tables, creating your relational database of books. Figure out what’s unique about each record. As stated in the previous point, you need a field that’s unique for each record. Although Access can create a unique value for you if no unique data exists for each record in your database, it’s often best to have such a field already in place, or to create such a field yourself. Customer numbers, student numbers, Social Security numbers, book ISBNs, catalog numbers, serial numbers — anything that isn’t the same for any two records will do. With a big list of fields and some tentative groupings of those fields at the ready, and with an idea of which field is unique for each record, you can begin figuring out how to use the data. 4. Make a list of ways you might use the data, including: Reports you’d like to create, including a list of which fields should be included for each report Other ways you can use the data — labels for mailings, product labels, catalogue data, price lists, contact lists, and so on 5. List all the places your data currently resides. This might be on slips of paper in your pocket, on cards in a box, in another program (such as Excel), or maybe through a company that sells data for marketing purposes. With this planning done, you’re ready to start building your database. Access databases with user forms When you’re planning your database, consider how the data will be entered: If you’ll be doing the data entry yourself, perhaps you’re comfortable working in a spreadsheet-like environment (known in Access as Datasheet view), where the table is a big grid. You fill it in row by row, and each row is a record. The figure shows a table of volunteers in progress in Datasheet view. You decide: Is it easy to use, or can you picture yourself forgetting to move down a row and entering the wrong stuff in the wrong columns as you enter each record? As you can see, there are more fields than show in the window, so you’d be doing a lot of scrolling to the left and right to use this view. You may want to use a form (shown in the following figure) instead. A form is a specialized interface for data entry, editing, and viewing your database one record at a time, if: Someone else will be handling data entry Typing row after row of data into a big grid seems mind-numbing The mind-numbing effect (and inherent increased margin for error) is especially likely when you have lots of fields in a database, and the user, if working in Datasheet view, has to move horizontally through the fields. A form like the one shown puts the fields in a more pleasing format, making it easier to enter data into the fields and to see all the fields simultaneously (or only those you want data entered into). If your database is large enough that you require help doing the data entry, or if it’s going to grow over time, making an ongoing data-entry process likely, Access is the tool for you. The fact that it offers simple forms of data entry/editing is reason enough to make it your database application of choice. Databases that require special reporting Yet another reason to use Access is the ability it gives you to create customized reports quickly and easily. Some database programs, especially those designed for single-table databases (known as flat-file databases), have some canned reports built in, and that’s all you can do — just select a report from the list and run the same report that every other user of that software runs. If you’re an Excel user, your reporting capabilities are far from easy or simple, and they’re not designed for use with large databases — they’re meant for spreadsheets and small, one-table lists. Furthermore, you have to dig much deeper into Excel’s tools to get at these reports. Access, on the other hand, is a database application, so reporting is a major, up-front feature. An example? In Excel, to get a report that groups your data by one or more of the fields in your list, you have to sort the rows in the worksheet first, using the field(s) to sort the data, and then you can create what’s known as a subtotal report. To create it, you use a dialog box that asks you about calculations you want to perform, where to place the results, and whether you’re basing a sort and/or a subtotal on more than one field. The resulting report is not designed for printing, and you have to tinker with your spreadsheet pagination (through a specialized view of the spreadsheet) to control how the report prints out. In Access? Just fire up the Report Wizard, and you can sort your data, choose how to group it, decide which pieces of data to include in the report, and pick a visual layout and color scheme, all in one simple, streamlined process. Without you doing anything, the report is ready for printing. Access is built for reporting — after all, it is a database application — and reports are one of the most (if not the most) important ways you’ll use and share your data. Because reports are such an important part of Access, you can not only create them with minimum fuss but also customize them to create powerful documentation of your most important data: Build a quick, simple report that just spits out whatever is in your table in a tidy, easy-to-read format. Create a customized report that you design step-by-step with the help of the Report Wizard. The report shown in the figure has the volunteers sorted by their last names. These options were easily put to work with just a few clicks. You can really roll up your sleeves and design a new report, or play with an existing one, adding all sorts of bells and whistles. The following figure shows this happening in Design view. Note that the report’s title (Volunteers List by Status) is selected: It has a box around it and tiny handles on the corners and sides of the box, which means you can reformat the title, change the font, size, or color of the text, or even edit the words if a new title is needed. So, you can create any kind of custom report in Access, using any or all of your database tables and any of the fields from those tables, and you can group fields and place them in any order you want: With the Report Wizard, you can choose from several preset layouts for your report, and you can customize all of it row by row, column by column. You can easily add and remove fields after creating the report, should you change your mind about what’s included in the report. If you want to place your personal stamp on every aspect of your report, you can use Design view to do the following: Add titles, instructional or descriptive text boxes, and graphics. Set up customized headers and footers to include any information you want to appear on all the report’s pages. If all of this sounds exciting, or at least interesting, then you’re really on the right track with Access. The need to create custom reports is a major reason to use Access.

Article / Updated 12-05-2018

A field in Access 2019, 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 AutoNumber (this data type is applied, by default, to the starting ID field in any new table) Date/Time Yes/No Lookup & Relationship Rich Text Attachment Hyperlink OLE Object Calculated Lookup Wizard 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 aforementioned 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. For now, don’t worry about figuring out what each field type is or what it does based on its name — I go over each one shortly. As you can see, though, the list covers just about any type of data you can imagine. And remember, each one can be customized extensively, resulting in fields that meet your needs exactly. The upcoming bulleted list introduces the available field types and how they’re used. You’ll also find out a little bit about how you can tweak them to meet your specific needs: Short Text: Stores up to 255 characters of text — letters, numbers, punctuation, and any combination thereof. Long Text: This replaces the Memo field type found in versions 2010 and previous. A Long Text field holds up to 64,000 characters of information — that’s almost 18 pages of text. This is a really big text field. It’s great for general notes, detailed descriptions, and anything else that requires a lot of space. Numbers in a text field aren’t numbers to calculate with; they’re just a bunch of digits hanging out together in a field. Be careful of this fact when you design the tables in your database — you don’t want to enter, say, a value that you intend to use in a Calculated field or to extract some other kind of information from a report and have that value stored as text, rendering it inoperable as a number. If the data is numeric, store it that way. Text fields have one setting you need to know about: size. When you create a text field, Access wants to know how many characters the field holds. That’s the field size. If you create a field called First Name and make its size 6, Joseph fits into the field, but not Jennifer. This restriction can be a problem. A good general rule is to make the field a little larger than you think you need. It’s easy to make the field even larger at some later point if you need to, but it’s potentially dangerous to make it smaller. Number: Holds real, for-sure numbers. You can add, subtract, and calculate your way to fame and fortune with number fields. But if you’re working with dollars and cents (or pounds and pence), use a Currency field instead. Currency: Tracks money, prices, invoice amounts, and so on. In an Access database, the buck stops here. For that matter, so do the lira, the mark, and the yen. If you’re in the mood for some other kind of number, check out the Number field. Date/Time: Stores time, date, or a combination of the two, depending on which format you use. Use a Date/Time field to track the whens of life. Pretty versatile, eh? Yes/No: Holds Yes/No, True/False, and On/Off, depending on the format you choose. When you need a simple yes or no, this is the field to use. Lookup & Relationship: If you want a field within one table to actually display content from a field in another table, choose this as the field type. A simple Lookup Wizard opens as soon as this field type is chosen, through which you select the table and field to look up through this new field in your table. Rich Text: Need the content of a particular field to be formatted just so? Choose this field type, and the formatting applied to the data in the field (using the Text Formatting tools on the Home tab) will be how it appears onscreen and in reports. OLE Object: You can use the OLE Object data type to link or embed an object — such as an Excel worksheet or Word document — to an Access table. Attachment: Use this field type to attach files — Word documents, Excel worksheets, PowerPoint presentations, or any other kind of file, including graphics (a photo of the volunteer, product, or location, perhaps?) — to the record. Hyperlink: Thanks to this field type, Access understands and stores the special link language that makes the Internet such a powerful place. If you use Access on your company’s network or use the Internet extensively, this field type is for you. Calculated: Use this field type when you want to fill the field in question with the result of a formula that uses one or more other fields in the same table. For example, in a table that contains a list of your products, other fields might include Price and Discount. If you want to also have a field that calculates the new price (the Price, less the Discount), you'd make that a Calculated field. When you choose this as the field type, you use a submenu to choose what kind of data will house the result, and then an Expression Builder dialog box appears, through which you set up the formula. To help you start thinking about your database and your data and to begin imagining the fields you could use for some common types of data, this table presents a breakdown of field types and ways you might use them. Common Fields for Everyday Tables Name Type Size Contents Title Short Text 4 Mr., Ms., Mrs., Mme., Sir, and so on. First Name Short Text 15 Person’s first name. Middle Initial Short Text 4 Person’s middle initial; allows for two initials and punctuation. Last Name Short Text 20 Person’s last name. Suffix Short Text 10 Jr., Sr., II, Ph.D., and so on. Job Short Text 25 Job title or position. Company Short Text 25 Company name. Address 1, Address 2 Short Text 30 Include two fields for the address because some corporate locations are pretty complicated these days. City Short Text 20 City name. State, Province Short Text 4 State or province; apply the name appropriately for the data you’re storing. Zip Code, Postal Code Short Text 10 Zip or postal code; note that it’s stored as text characters, not as a number. Country Short Text 15 Not needed if you work within a single country. Office Phone Short Text 12 Voice telephone number; increase the size to 17 for an extension. Fax Number Short Text 12 Fax number. Home Phone Short Text 12 Home telephone number. Cellular Phone Short Text 12 Cell phone (or “mobile phone” for you cosmopolitans). Email Address Short Text 30 Internet email address. If the person whose record you’re building has multiple email addresses, make this one Email1, and number the alternatives — Email2, Email3, and so on. Website Hyperlink Web page address; Access automatically sets the field size. SSN Short Text 11 U.S. Social Security number, including dashes. Comments Long Text A freeform space for notes; Access automatically chooses a field size. All the field types listed as samples in this table are really text fields, even the ones for phone numbers. This is because Access sees their content as text rather than as a number that could be used in a calculation. (Check out the following table for field-naming no-nos.) Prohibited Symbols Symbol Name / Forward slash ? Question mark * Asterisk - Dash ; Semicolon " Double quotes : Colon ' Single quote ! Exclamation point $ Dollar sign # Pound sign % Percent & Ampersand Of course, another field type (listed in the Type column) is neither a Short Text nor Long Text field — you also see the Hyperlink field. This data type is also considered text, but the Hyperlink data type stores URLs, as URLs — not just as a string of text and punctuation. If all this text versus numbers stuff is confusing you, remember that computers think there’s a difference between a number (that you’d use in a calculation) and a string of digits, such as the digits that make up a phone number. When it comes to different kinds of text fields, it’s a matter of how much text will be stored in the field, and if it needs any special formatting in order to work properly in the database.

Article / Updated 12-05-2018

ScreenTips in Access 2019 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. Not all onscreen features have ScreenTips, but for anything you can click to make something happen — as when a dialog box opens, Access performs some task for you, or something is created — these 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. 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 Options dialog box, select General. The options in the dialog box change to show other options related to ScreenTips, file formats and folders, and how your name and initials are stored, as shown. 4. In the first section of the dialog box, click the ScreenTip Style drop-down list. 5. Choose from the following options: Show Feature Descriptions in ScreenTips: This option displays ScreenTips with extra information, as shown here. Here you see that in addition to the name of the button, a brief description of how it works (or its effect) is displayed for your benefit. It even points to more assistance and information — through the “Tell me more” link at the bottom of the ScreenTip. Don’t Show Feature Descriptions in ScreenTips: If you want just the facts, ma’am, this is for you. ScreenTips will show just the button name with no further explanation. Don’t Show ScreenTips: Want to go it alone? Turn off ScreenTips. 6. Click OK to close the Access Options dialog box. Establishing the level of detail included in ScreenTips isn’t the end of your options. You can also choose whether to include keyboard shortcuts in ScreenTips. This is on by default, and it’s pretty useful.

Article / Updated 12-05-2018

When you open a Microsoft Access 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 here (Home, Create, External Data, and Database Tools). These tabs are not to be confused with the context-sensitive tabs that appear when various database objects (the tables, forms, queries, or reports that make up your database) are created or edited. 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 components are available on the Create tab, but anything that works with existing data will be dimmed. After you open a table, report, query, or form, the tools for that object become available. Displaying a form in Layout view, for example, adds the Form Layout Tools group of tabs to the main set of five tabs, as shown. Clicking tabs in Access 2019 To move from one tab on the Ribbon to another, simply click the tab’s name. It’s easy to see which tab is currently open — as shown here, the Create tab stands out from all the other tabs, and you can see all its buttons. When you mouse over another tab, that tab brightens, too, but you don’t see its buttons until or unless you click the tab. After you’ve created an object — say a table, by clicking Table on the Create tab — the Home tab is displayed, and a new label (Table Tools) appears above the context-sensitive Fields and Table tabs. See? Access knows what you need based on whatever you’ve just done within the application. Using the Access 2019 buttons Access buttons come in two varieties: Buttons that do something when they’re clicked: Either opening a dialog box or wizard or performing some change or task in your open table, report, query, or form. Buttons that represent lists or menus of choices: This latter variety comes in two flavors of its own: Drop-down list buttons are accompanied by a small, down-pointing triangle, appearing to the button’s right. When you click the triangle, a list of options appears, as shown. Some buttons have a down-pointing triangle at the bottom of the button (as shown here). Click the bottom half of the button (or on the triangle), and a menu appears. The File tab and Quick Access tools If you’re fresh from using a pre-2007 version of Office (XP and previous), you’ll be relieved to see a File tab. Office 2007 users lost that familiar word in that version’s interface, replaced then by an Office button, with no comforting word File on it. The word File came back in 2010, however, displaying Backstage view, for opening files, saving files, starting new files, printing, and customizing Access through the Options command. The File tab remains in Access 2019, and is shown here. When you’re in a database and click the File tab, you’re taken to the Info display, showing information about the open database. Where’s the Quick Access Toolbar? It appears while you’re in a database, but it disappears when you go to the File tab. No problem; to see it again, just click the Back button in the File panel, and you’re back to your database, with the Quick Access Toolbar in the uppermost left of the workspace. If you want to customize the Quick Access Toolbar, click the triangle at the right end of the toolbar. It offers a pop-up menu with several choices, from a list of commands you can add to the toolbar (such as New, Open, Email, or Quick Print — the ones already in use have a check mark next to them) to commands that allow you to Customize Quick Access Toolbar and Show Below the Ribbon. Access panes, panels, and context-sensitive tools Depending on what’s going on within the workspace — that is, what you’ve just done as you edit your table, report, query, or form, or which button you’ve clicked on one of the Ribbon tabs — Access offers relevant onscreen tools and panels. As an example of this context-sensitive feature, if you open a table and click the Report button on the Create tab (see the Reports section of the Create tab), not only does a report appear, but you also get new tabs — Design (shown here), Arrange, Format, and Page Setup, under the heading “Report Layout Tools”. As you work with Access, you’ll get a feel for what’s going to appear when you do certain things. Things appear and disappear as you work because Access offers you just what you need for the task you’re performing or feature you’re using.

Article / Updated 12-05-2018

If you’re upgrading to Access (or Office) 2019 from the 2013 or 2016 interface, you’ll find it looks very familiar — and you’ll find much of it to be the same as what you’re accustomed to. Let’s start with the File tab, where a vertical list of commands creates context-sensitive changes to the main workspace — essentially everything to the right of that left-hand menu panel. To start a new database, click the word New in the File tab’s vertical menu of options. Starter templates appear along with the Blank Desktop Database button. Most of the time, you’ll be clicking the Blank Desktop Database button so you can create a completely customized database. If you’ve opened an existing database, click the Info command (as shown) to get important statistics related to the open database. Two big buttons and a link appear, from which you can Compact & Repair the database files, Encrypt the open database with a password, or see the Properties for the open database by clicking the View and Edit Database Properties link. The Save, Save As, Print, and Options commands each lead to either more command choices or a dialog box. For example, choosing Save As produces two options — to save the open database with a new name (Save Database As) or to save a new version of the active database object (Save Object As), which allows you to save the table, form, query, or report you have open at the time with a new name. For now, I’ll show you the basic workspace in three states: When Access first opens up When a new database is being built, either from scratch or when you’ve started with one of Access’s database templates When you’re working on an existing database 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. Whether you’re starting Access to view and edit an existing Access database (which gives you what you see here) or are about to create your own (which opens the application and displays the template icons, shown in the second figure), you can get to the tools you need right away. This first figure shows an existing database open to one of its tables; its other components are listed on the left side of the workspace. You can open an existing database by double-clicking it by name in the File Explorer window or from an icon on your desktop; you can start Access from the Start menu or the taskbar (if you pinned Access to it), and then pick which existing database you want to work with; you can start a new, blank database from scratch; or you can start out with one of the Access templates. This figure shows the various template icons displayed when you choose New from the left-hand panel. If you opened Access by using the Start menu or a desktop/taskbar icon and now you want to open an existing database, you can use the Open command in the panel on the left (as shown). This allows you to open either a recently used database or browse for one you haven’t opened in a while. The Open command’s list of options includes: Recent: Shows the list of recently used files and folders. When you use the Recent list, clicking any one of the Recent Databases listed opens that sucker right up, displaying its parts on the panel on the left side of the workspace. Sites: Opens a chosen folder in your SharePoint server. This PC: Provides a list of database files found in the last folder you used to save an Access database. If you have never saved an Access database in the open installation of Access, this takes you to the Documents folder, Windows’ default location to save files generated in any application running on your computer. Add a Place: Adds SharePoint and OneDrive locations. Browse: Opens an Open dialog box through which you can navigate to the drive and/or folder containing the database you want to open. So that’s it, really — any way you want to get started is available either by opening the Access application from the Start menu, the desktop, or the taskbar — or File Explorer, if you want to open a database (.accdb) file and start both Access and your selected database at the same time. After you get to working, however, it's time to use the onscreen tools that don’t appear until you open a database. Read on for a whirlwind tour of the Access workspace, including views and explanations of all the major bells, whistles, and buttons.

Article / Updated 12-05-2018

When you look at all the applications in Microsoft Office 2019 — Word, Excel, PowerPoint, Outlook, and of course, Access — you’ll see some features that are consistent throughout the suite. Access has several features in common with the rest of the applications in the Microsoft Office suite. You’ll find the same buttons on several of the tabs, and the Quick Access Toolbar appears in all the applications. If you already know how to open, save, and print in, say, Word, you’re probably ready to do the same things in Access without any difficulty. To make sure you’re totally Access-ready, here’s a look at the basic procedures that can give you a solid foundation on which to build. How to open Access Access opens in any one of several ways. So, like a restaurant with a very comprehensive menu, some people will love all the choices, and others will say, “I can’t decide! There are just too many options to choose from!” Now, you’ll run into situations in which one of the ways is the glaringly best choice — hands down, and that one will be the way to go. But what if you’ve never heard of it? You’ll be trying to find my phone number (I’m unlisted — ha!) so you can give me a piece of your mind. So to acquaint you with all your choices (so you’ll be ready for any situation), here are all the ways you can open Access: Windows 8.1 users can utilize any of several methods to start an application — click the lower left corner of the screen to display the Start icon, press the Windows key on the keyboard, or if you have a touchscreen, tap the Start button. Once the Start screen appears, tap the Access application tile. If you’ve recently used Access, you’ll see it in the list on the left side of the Start menu. Just choose Start→  Microsoft Access 2019, and Access opens. Double-click any existing Access database file on your desktop or in a folder (as shown). Access opens automatically. Good news: Access 2019 will open database files you created with previous versions of Access, and should support whatever features are employed within those database files. All your tables should open properly, and reports, forms, and queries should all work fine, too. If some helpful person has added Access to the Quick Launch toolbar (on the taskbar), you can click the Access 2019 icon (it looks like an A on the cover of a book), and there you go. Access opens for you right then and there. How to select a starting point in Access So, Access is open, and (assuming you opened it from the Start menu or from the Quick Launch portion of the taskbar) you’re staring at the Access interface. You may see features whose purposes elude you or that you don’t yet know how to use. Look at the ways Access offers you to get started with your database, be it an existing one that needs work or a new one you have all planned out and ready to go. Opening an existing database Well, this is the easy one. If a database already exists, you can open it by clicking the File tab (at the upper left of the workspace) and choosing Open from the list of commands that appears. As shown here, a panel opens, displaying the types of files you can open (just to the right of the long red File panel) and the databases you’ve most recently used. Click the word Recent in the list to the near left and then click the database in the Recent list, and it opens, listing its current tables, queries, reports, and forms on the far left side of the window. When the database opens, you can open and view its various parts just by double-clicking them in that leftmost panel; whatever you open appears in the main, central part of the window. The following figure shows an example: a table, ready for editing. After you open a table, you can begin entering or editing records. If you want to tinker with any existing queries, you can open these, too, just by clicking them in the list on the left side of the workspace. Starting a new database from scratch So, you don’t have a database to open, eh? Well, don’t let that stop you. To start a new one, all you have to do is open Access. A database file holds all your database components. Everything associated with the data is part of the database, including: All the tables that house your data Queries that help you search and use the data Reports that show what your data is and what it means Forms that allow people to view, enter, and edit data After Access is open, click the New command in the File tab (if that’s not already the active command). From the resulting display, you can click the Blank Desktop Database button (shown here) to get started. Next, give your database a name (see the dialog box that appears in the following figure), and click the Create button. The X in the caption represents a number; Access assigns consecutive numbers to the default names. The figure shows a 1 added to the filename. If this is your absolute first database in a fresh installation of Access, the filename offered in this panel will be Database1. Note that you don’t need to type a file extension here; Access will add the correct one for you. What is that little yellow folder in the dialog box where you named your new database? It allows you to choose a folder (other than the default Documents folder in Windows 8/8.1) into which you can save your database. Click the folder icon after typing a name for your database, and then use the resulting File New Database dialog box to choose a location — an existing subfolder within Documents, the desktop, a network drive (if you’re on a network, say at your office), or your Office 365 OneDrive. The dialog box looks very familiar to anyone who’s used any Windows application, so this won’t be new territory for you. At this point, with your new database open, you can begin entering records into your first table or begin naming your fields and setting them up. The field names go in the topmost row (the ID field is already created, by default in the new table), and the label Click to Add is atop the column with the active cell. If you choose to save your table now (right-click the Table1 tab and choose Save), you can name your table something more useful than Table1. Starting with a template Access provides templates (prepared files that work sort of like database cookie cutters) for your new database needs. You’ll find a set of template icons in the same New panel where we just chose a Blank Desktop Database. As shown, you can choose a template category by clicking any of the words under the Search for Online Templates search box, and search online for templates in that category. Once the online search is complete (assuming you’re online at the time), a series of big buttons, one for each template found that matches your search, appears. Note that a larger list of categories — to be used if you want to search again for another category of templates — appears on the right.

Article / Updated 12-05-2018

Here are ten of the most common Microsoft Access problems and their solutions. Don’t give up hope if your problem is not on this list. Chances are, if you’re having the problem, someone else has already had it and figured out the solution. That’s why the Internet is a valuable resource. That’s just not normal—normalizing empty Access tables One of the hardest things to do (yet most important when building a database) is building the table structures properly. This process is known as normalization. A properly normalized database should never have just one table containing redundant information. Consider the following table: Customer Address City State Zip Telephone Jones 125 Main Street Jonestown NJ 08000 609-555-1244 Jones 125 Main Street Jonestown NJ 08000 609-555-7890 Smith 1542 Jones Hwy Laramie WY 82051 307-555-5412 Wilson 78 Smith Circle Jones CA 90000 451-555-8645 Do you see the redundant information? Right you are. It’s the customer name and address. What is causing the redundancy? Right again! It’s the two phone numbers for customer Jones. The correct way to normalize this table would be to split it into two tables — one for customer name and address information and the second for phone numbers. This would eliminate the need to repeat the second Jones record in the Customers table. If you’re having trouble normalizing empty tables, fill each table with five to ten records. Viewing the tables with data usually makes it easier to spot normalizing issues. Here’s how to get started normalizing your tables: Examine each table as it is currently structured. Are you repeating any information unnecessarily (as you saw with the address and customer name earlier)? If you have duplicate information, determine why you’re repeating it (for example, the multiple phone numbers for one customer). Break the one table into two tables to eliminate the redundancy. Repeat Steps 1 through 3 for each table until all redundancy is eliminated. You may find that splitting one table into two still does not eliminate all redundancy in a table. In that case, keep splitting the tables until all redundancy is gone. You type 73.725, but Access changes it to 74 Automatic rounding can frustrate the living daylights out of you, but correcting it is easy. By default, Access sets all number fields to accept long integers. As you may remember from your high school math days, an integer is a negative or positive whole number. To accommodate decimals, you change the field-size setting so it accepts decimals. Here’s how: Open the table in Design view and then click the field that’s not cooperating. On the General tab of the Properties area at the bottom of the screen, click the Field Size box. Click the down arrow at the end of the box, and then select Single, Double, or Decimal from the drop-down menu that appears. Save the table, and your automatic rounding problem is over. For details about the difference between Single, Double, and Decimal field sizes, press the F1 key while in the Field Size property box. The Help screen gives a detailed description of each field size, the numbers it will hold, and the amount of space reserved for that size. Access requires an Internet connection to use its help system. The words they are a-changing—problems with Access AutoCorrect Sometimes those “helpful” features in Access can become a nuisance. One such feature is called AutoCorrect. You may be familiar with it from Microsoft Word, where it is often a great thing. Databases, however, often contain acronyms, part numbers, and the like. AutoCorrect can have a field day with such “words”. You may not even realize it as you enter your data. You have two choices to resolve this problem. Undo AutoCorrect’s effects as they occur. Press Ctrl+Z right after AutoCorrect has botched your data entry. Access puts the data back to the way you typed it. Unfortunately, for this to work you actually have to notice that Access has changed what you entered. Turn AutoCorrect off entirely. To turn off AutoCorrect, follow these steps: 1. Click the File tab in the upper left corner of the Access screen. 2. Click the Options button in the menu down the left side of the screen. The Access Options dialog box appears. 3. Click Proofing from the list on the left. Your proofing choices appear. 4. Click the AutoCorrect Options button. The AutoCorrect dialog box appears. 5. Uncheck some or all of the check boxes in the AutoCorrect dialog box. You can disable some or all of the AutoCorrect features, depending on what AutoCorrect is doing to annoy you at present. Uncheck the Replace Text as You Type option if you no longer want Access to “fix” your “spelling errors” for you. 6. Click OK two times to save your changes. You can now type your problem text correctly, without AutoCorrect’s interference, and have it stay as you typed it. Was there and now it’s gone—accidental data deletions in Access You might’ve heard this one a lot over the years: “The database deleted my record!” Well, I’ve got news for you: The database doesn’t do anything without us humans commanding it. And humans can make a couple of mistakes: Accidental deletion: There are several ways to delete a record accidentally. Usually a keyboard shortcut for Delete is pressed, such as Ctrl+– (delete record) or Ctrl+X (cut). The Undo command (Ctrl+Z) will not reverse the deletion of a record. Data error: A record may appear deleted if someone inadvertently changes a particularly vital piece of information. For example, suppose the record in question contains an order date of 12/15/19, and someone inadvertently changes the date to 12/15/09. The order date isn’t what’s expected, so the record may seem to have been deleted. If a data error makes the record seem deleted, there are several possible fixes, as outlined in the following sections. Undo Don’t panic. Before doing anything else, press Ctrl+Z. That’s the Undo command. If the record comes back, you’re in luck. Undo reverses data-entry errors that may cause the record to appear deleted. However, this will work only if you Undo right after the data-entry error takes place. Search for the missing record If you try the Undo command and the record doesn’t come back, there’s still a chance that a data-entry error is hiding it by putting it where you don’t expect it to be. Open the table that contained the record and search for it in some way other than you normally would. Look for anything out of the ordinary on similar records. Here are some examples: If you normally search for orders by date, search by client. See whether an order similar to the missing one exists for that client and has an unusual date (say, the same month and day as the missing order but with the wrong year). Try looking at all orders on the date in question to see whether the client on each order seems to be correct. It could be that the client was changed inadvertently on the missing order. Backup recovery If you can’t find the record anywhere, copy the record from a backup of the database file. This solution works only if you’ve backed up your database since the record was originally added. If you back up at night and the record was entered during the same day it went missing, that record will not be in your backup. You run an Access query, but the results are unexpected Query-writing is an art form. Even the experts mess up every now and then. Here are some common solutions to unexpected query results: Check criteria for accuracy. A single misplaced keystroke is all it takes to turn your query into a dud. Check your criteria for spelling or syntax errors — and then run the query again. Try the Unique Values property. Ever see two copies of each record in your query results when you were expecting just one? A quick fix often comes from using the Unique Values property. This property tells Access to stop with the doubling, already — and, if the query results contain a group of exact duplicates, to return only one row from the group. Here’s how to use this property: 1. Open the problem query in Design view. The Design tab on the Ribbon appears. 2. Click the Property Sheet button from the tab’s Show/Hide Ribbon group. The Property Sheet window opens to the right of the query grid. 3. Click in the gray area between the field lists in the top half of the query grid. The Property Sheet should now display Query Properties. (Look right under the Property Sheet’s title bar to confirm this.) 4. Click in the Unique Values row of the Property Sheet. A drop-down list arrow appears at the end of the Unique Values row. 5. Select Yes from the drop-down list and run the query. The doubling should disappear. Correct the selection logic. Juggling a bunch of AND and OR connections in a query can quickly mess up even the hardiest of database designers. Fix table relationships. If your query results show way too many records, and the query uses two or more tables, improper relationships (also called joins) are the likely cause. Check table relationship types. If your query involves two or more tables, and you get fewer records than you expected, incorrect table relationships are the likely cause. For example, if you have an order entry database and run a query listing all customers and their orders, by default, you'll see only those customers who have placed an order. To see all customers, whether or not they’ve placed orders, do the following: 1. In Design view, right-click the join (the line connecting the two tables) and choose Join Properties from the menu that appears. 2. Examine the types of joins offered and choose the one that says something like “Include ALL records from ‘Customers’ and only those records from ‘Orders’ where the joined fields are equal”. The actual text you see differs according to the names of your tables. To query aficionados, this is called an outer join. Very cool. 3. Click OK and run the query. You should now have all records from the Customers table whether or not there are corresponding records in the Orders table. If your query involves several criteria, some calculated fields, and numerous relationships, try breaking the task into several smaller steps instead of trying to solve the problem all at once. The step-by-step approach lets you focus on each piece, one at a time, making sure each works perfectly before moving on to the next one. If your query still doesn’t work no matter what you do, ask someone else to take a look. I’ve often worked on a tough query problem for hours, shown it to someone else, and heard those magical words: “That’s simple. Just do this.” And the problem is solved. Getting a fresh pair of eyes on the problem often solves things fast. The dreaded Parameter dialog box in Access At some point, when opening a query, form, or report, you’ll see a Parameter dialog box when you don’t want to see a Parameter dialog box. Do you throw your hands in the air and curse the universe? Of course not! Whenever you see a Parameter dialog box unexpectedly (you can set them on purpose), it means that Access can’t find a field referenced by either the form or report or the query behind the form or report. Say that the problem is with a report. To troubleshoot, start with the query behind the report. Open that query in Datasheet view and see if you get the parameter. If you do, what field is it asking for? That field is the one Access can’t find. So, switch the query to Design view and find the column with the field that Access can’t find. The problem field is usually a Calculated field that references other fields. Is each field and table name spelled correctly? If not, correct the spelling errors. Is each field in the table it’s supposed to be in? For example, if your reference reads Orders.LastName and the LastName field is in the Customers table, correct the error by typing Customers.LastName. If the query runs without a parameter, then the problem is on the report. So, open the report in Design view and check each control on the report that is bound to a field. If Access can't find one of the fields the control is supposed to display, it’ll put a green triangle in the upper left corner of the control. Check each one for the green triangle. If you find the green triangle, check the spelling of the field referenced by the control. For example, if the control is supposed to display LastName (no space) and the reference in the control says Last Name (space), then remove the space so that the control on the report matches the field name from the query. Also check the report’s underlying query to confirm the problem field is selected in the query. The slowest Access database in town An Access database may end up on the shared drive of a business so it’s available to everyone who needs it. The problem with placing the entire Access database on the shared drive is that it often runs slowly on each user’s workstation (that’s a fancy word for an individual computer). You’ll also likely run into errors if multiple people attempt to use the database at the same time. The complaints start rolling in, and you don’t know what to do. The solution to this problem lies in splitting the Access database file into two separate files: Front end: Contains all the database objects except the tables The front end resides on the user workstation. Back end: Contains just the tables The back end resides on the shared server. The front end is linked to tables in the back end. All you’re really sharing is the data — so the data is all that should go on the shared drive. By setting things up this way, the only information that must travel across the network is the data requested by the user. Such a setup dramatically speeds database performance and allows multiple users to enter and edit data at the same time. Splitting the dataset is not as hard as you might think. Access makes it a snap with the Database Splitter Wizard. Follow these steps to split your database: Back up the database you want to split. If anything goes wrong (unlikely, but hey, you can never be too safe when it comes to data!), you can try again with the backup copy. If necessary, move the database you want to split to a folder on your shared drive. This step allows the Database Splitter to set up table links properly for you. Open the database file you want to split from the shared folder. Make sure you have a backup copy of this database before going any further. Also make sure all database objects are closed. Click the Database Tools tab on the Ribbon. The Move Data group appears on the Ribbon. It contains a button called Access Database. Click the Access Database button. The Database Splitter Wizard dialog box appears. Click the Split Database button and let the wizard do its thing. You will be prompted for a back-end database filename. Enter a name, sit back, and watch the fun unfold before your very eyes. Copy the front-end file (the original file you split) to each user’s workstation. Have the users open the file from their workstations — and see how they marvel at the improved speed of the database! You are a hero. Yea! Don’t have a shared drive or want to get rid of your share? You can still have multiple users in your database at one time by placing your data in the cloud. Your Access database file is as big as a house As time goes by, you find your database file growing larger and larger. This is a result of deleting objects and records over time. If, for example, you create a query and then later delete it because it’s no longer needed, Access doesn’t automatically remove the space occupied by that query from the database file. The same is true for records. As you delete records from a table, the space that those records occupied in the database file remains. Eventually, the file can become four or five times the size required to hold the data and objects within it. Why should you care if the file size increases? Here are two reasons: A smaller database file runs faster. Performance is a key component to happy database users. You want your forms to load quickly and your queries and reports to run as fast as possible. A regularly compacted database is more stable. If the database is used often, compacting regularly helps keep file and table corruption from occurring. The Compact and Repair command removes the excess. It is good practice to compact your database regularly (once a week is usually fine). Always compact it after making any design changes. Here’s how: Open the bloated database and click the Database Tools tab on the Ribbon. The Tools group appears at the very left of the Ribbon. Click the Compact and Repair Database button from the Tools group. The status bar (lower right of your screen) displays a progress bar that notifies you of how the compact process is progressing. When the progress bar disappears, compacting is complete — and you’ll be left with a much trimmer (faster and more stable) database file. If you’ve split your database, don’t forget to compact both the front- and back-end files. Want a database file to compact each time you close it? Follow these steps: Click the File tab on the Ribbon. Click the Access Options button in the menu bar down the left side of the screen. The Access Options dialog box appears. Click Current Database from the list on the left. Options for the current database appear. Check the Compact on Close check box. Click OK to save your changes. Click OK from the resulting message box. Close the database and note the lower right status bar. The database is compacting before it closes! Compact on close is used best on the front-end file only. Compacting the back end on close may cause corruption of the back-end file should another user be in it when you close out of it. Do not turn on Compact on Close on the back-end file. You get a mess when importing your spreadsheet ito Access It’s common practice to upgrade a collection of spreadsheets to an Access database after the spreadsheet solution no longer suits your needs. It’s also common to find the imported spreadsheet (now table) data in a state of disarray. The easiest way to solve this problem is by cleaning up the spreadsheet before you import it. Here are a few tips for a tidy import: Double-check information coming from any spreadsheet program to be sure that it’s consistent and complete. Above all, make sure that all entries in each column (field) are the same data type (all numbers, all text, or all whatever). Remove any titles and blank rows from the top of the spreadsheet. An ideal spreadsheet for import will have field names (column headings) in row 1 and data starting in row 2. Make sure your spreadsheet column headings are short and unique so Access can easily translate them to field names during import. We’re sorry; your Access database file is corrupt It started out as a day just like any other. However, on this day, you are getting an error when you open the front end of your split Access database. You can’t seem to open any forms or reports. It’s funny how a few little messages can ruin your day. You start wondering if you backed up the data file last night and when the file was actually corrupted. Then you start wondering how you’ll get out of this mess. Fear not. There is a simple solution to a corrupt database. Here are the steps: Browse to the folder that contains the back-end file. Double-click the file to open it. Access will launch and attempt to repair the file. You should see a repair progress bar on the right part of the status bar. If all goes well, the file opens. Close the back-end data file. Reopen the front-end file and everything should be working normally. If, after following the preceding directions, the corrupted file still doesn’t open, you have a serious problem that could take some effort to clean up. The next step is to resort to a backup copy of the database. Check what data is missing between the backup and your recollection of the corrupted file. Yes, you’ll have to reenter any missing data. Sorry! If you don’t have a backup, all hope is not lost. You can buy software designed specifically to repair corrupted Access database files. Try searching the web for repair corrupt Microsoft Access database files. Make sure the software works with Microsoft Access 2019 and it is from a legitimate company.

Article / Updated 12-05-2018

Technical experts — the people who know Microsoft Access 2019 inside and out — might be a little intimidating, but they’re important — and they (we?) in no way intend to intimidate. They’re important to average Access users because they provide invaluable advice, and they’re important to Access itself because they drive the way Microsoft continuously improves its products. These men and women are the people who test Office products before new versions are released to the public, and they’re the ones who write books to help users of all levels make the most of the software. So, the people who develop databases for a living are an essential resource to the average user, to the “power user,” and to the software manufacturer as well. Here is a compilation of some of the best advice gathered from a long list of Access experts. Knowing they were offering suggestions for new users, they offered advice to fit your needs and to help ensure that you really can use Access confidently and effectively. By the time you finish, you’ll have given your efforts the right amount of planning and organizing — and you’ll have solid plans for moving forward with your development and use of the databases you build with Access. So, here’s the sage advice — in ten quick bites. Document everything as though one day you’ll be questioned by the FBI Don’t skimp on the time spent documenting your database. Why? Because you’ll be glad later on that you didn’t skimp. You’ll have all your plans, your general information, and all your ideas — those you acted on and those that remained on the drawing board — ready the next time you need to build a database. You’ll also have them to refer to when or if something goes wrong with your current database. You accidentally deleted a saved query? No problem. Refer to your documentation. Forgot how your tables were related? Check the documentation and rebuild the relationships. Need to explain to someone why or how you set something up? Refer to your notes and wow them with your forethought and careful consideration. So, what should this glorious documentation include? Well, everything. But here’s a list to get you started: General information about the database: File/data locations (with specific network paths or Internet URLs) Explanation of what the database does Information on how it works Table layouts: Include field names, sizes, contents, and sample contents. If some of the data comes from esoteric or temporary sources (say, the credit card data that you download monthly online), note that fact in the documentation. Summary of reports: Report names An explanation of the information on the report If you need to run some queries before creating a report, document the process. (Better yet, get a friendly nerd to help you automate the work.) Queries and logic: For every query, provide a detailed explanation of how the query works, especially if it involves multiple tables or data sources outside Access (such as SQL tables or other big-time information-storage areas). Answer the question “Why?”: As you document your database, focus on why your design works the way it works. Why do the queries use those particular tables? Granted, if you work in a corporate environment, you may not know why the system works the way it does, but it never hurts to ask. Disaster-recovery details: The backup process and schedule Where backups are located (you are making backups, right?) and how to restore a backed-up file What to do if the database stops working If your database runs an important organizational function — such as accounting, inventory, contact management, or order entry — make sure that a manual process is in place to keep the organization going if the database malfunctions — and remember to document the process! If you need help with any of these items, ask someone! Whether you borrow someone from your Information Technology department or rent a computer geek, get the help you need. Treat your documentation like insurance — no organization should run without it. Every 6 to 12 months, review your documentation to see whether updates are needed. Documentation is useful only if it’s up to date and if someone other than you can understand it. Likewise, make sure you (or your counterparts in the office) know where the documentation is located. If you have an electronic version, keep it backed up and have a printout handy — something you’ll be glad you did if you or someone else attempts to recycle your database parts using the Application Parts feature. Keep your Access database fields as small as possible As you build tables, make your text fields the appropriate sizes for the data you keep in them. By default, Access sets up text (known as Short Text) fields to hold 255 characters — a pretty generous setting, particularly if the field holds measly two-letter state abbreviations. A hundred or more extra spaces — that go unused in most text fields — wouldn’t seem like anything to lose sleep over, but multiply that space across a table with 100,000 customer addresses in it, and you end up with lots of megabytes of storage space that are very busy holding nothing. Adjust the field size with the Field Size setting on the General tab in Design view. Use number fields for real numbers in Access databases Use number fields for numbers used in calculations, not for text pretending to be a number. Software applications perceive a huge difference between the postal code 47999 and the number 47,999. The application views a postal code as a series of characters that all happen to be digits, but the number is treated as an actual number that you can use for math and all kinds of other fun numeric stuff. The other reason a zip code isn’t a Number field? If you’re in the U.S. and your zip code starts with zero, the application cuts off the leading zero and stores just the non-zero digits in the field – 01234 becomes 1234. Not good! When choosing the type for a new field with numbers in it, ask yourself a simple question: Are you ever going to make a calculation or do anything math-related with that field? If you’ll calculate with the field, use a Number type. If you won’t calculate with the field, store the field as Short Text. Validate your Access data Validations can help prevent bad data from getting close to your tables. Validations are easy to make, quick to set up, and ever-vigilant (even when you’re so tired you can’t see straight). If you aren’t using validations to protect the integrity of your database, you really should start. Use understandable names in Access to keep things simple When building a table or creating a database, think about the database file, field, and table names you use: Will you remember what the names mean three months from now? Six months from now? Are the names intuitive enough that someone else can look at the table and figure out what it does, long after you’ve moved on to bigger and better things? This becomes even more important as you start using the tools for putting your Access tables and databases online and sharing them via SharePoint — potentially you’re bringing millions of users “to the table”. Also, with the Application Parts feature, the components of your databases can be recycled to help speed the creation of a new database. You don’t want mysterious names for the parts of your database to spread to a new database — to create nightmarish mysteries there too — right? Delete Access field values with great caution Whenever you’re deleting field values from a table, make sure you’re killing the values in the right record — check again, and then only when you’re sure, delete the original. Even then, you can still do a quick Ctrl+Z and recover the offending item provided you undo right after catching the mistake. Why all the checking and double-checking? Because after you delete a field value and do anything else in the table, Access completely forgets about your old value. It’s gone, just as though it never existed. If you delete a record from a table, the record is really gone — because there is no Undo available for an entire record. If that record happened to be important and you didn’t have a current backup file when the record went away, you’re out of luck. Sorry! Backup, backup, backup your Access databases Did I make that clear enough? Always keep a backup of your work! There’s no substitute for a current backup of your data — particularly if the data is vital to your personal or professional life. Effective strategies often include maintaining backup copies at another location in case a disaster destroys your office, be it at a different office or in the cloud. If you’re thinking that you’ve never needed a backup before so why bother, think about floods. Think about newscasters saying that an area currently underwater has never flooded before. Picture people’s lives floating down the street. Whether you’re faced with a real disaster of hurricane proportions, a fire, or your computer’s hard drive deciding to die (and that does happen — even if it has never happened to you before), you’ll be much happier if you have a backup of your database. Think, think, and think again before taking action in Access You know the carpenter’s slogan, “Measure twice, cut once”? The same can be said for thinking when it comes to your database. Don’t just think about something, come to a quick conclusion, and then dive in. Wait, think it through again, and then maybe think about it a third time. Then draw a conclusion and begin acting on it. With all the power Access gives you, coupled with the capability to store thousands upon thousands of records in your database, a relatively simple mistake can be quite costly because of the potential ramifications in terms of data loss or an “un-undoable” action taken in error. Get organized and stay organized when working in Access Although the suggestions to get organized and to keep it simple may seem to be at odds, these two pieces of advice are really companionable. Keeping things simple can often be a way to avoid the need for a lot of organization after the fact. Whereas you probably got tired of hearing your parents remind you that “there’s a place for everything, and everything in its place” (or if they were less poetic, “Clean your room!!!”), they were right. If you keep your database organized, you’ll save yourself time and grief. A well-planned, well-organized table will be easier to query, report on, and include in a form. It’ll also sort and filter like lightning. Yes, you can get too organized. In fact, over-organizing is altogether too easy. Temper your desire to organize by cultivating another passion: working with as few steps as possible. Limit the number of folders and subfolders you use — a maximum of five levels of folders is more than enough for just about anybody. If you go much beyond five levels, your organization starts bumping into your productivity (and nobody likes a productivity loss, least of all the people who come up with those silly little slogans for corporate feel-good posters). There’s no shame in asking for Access Help If you’re having trouble with something, swallow your ego and ask for help. Saying “I don’t know” — and then trying to find out about what you don’t yet know — holds no shame. This rule is especially important when you’re riding herd on thousands of records in a database. Small missteps quickly magnify and multiply a small problem into a huge crisis. Ask for help before the situation becomes dire.