Laurie Ulrich Fuller

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!

Article / Updated 03-01-2017

With Access' Query Wizard, you enter table and field information, and the Simple Query Wizard takes care of the rest. Here's how you create a query: On a piece of paper, lay out the data you want in your query results. A query returns a datasheet (column headings followed by rows of data), so make your layout in that format. All you really need are the column headings so you'll know what data to pull from the database. Determine the table location of each piece of data (column heading) from your paper. Write down the table and field name that contain the data matching the column heading on the paper above the column heading. In the Database window, click the Create tab on the Ribbon and then click the Query Wizard button from the Queries section. The New Query Wizard dialog box appears, asking you what kind of Query Wizard you want to run. Choose Simple Query Wizard and click OK. Choose the first table you want to include in the query. You'll use the Tables/Queries drop-down menu, which shows all the tables (and any existing queries) in your database. Click the down arrow next to the Tables/Queries drop-down menu. Click the name of the table or query to include in this query. Select the fields from that table for your query. For each field you want included in your query, click the name of the table or query to include in this query, and in the Available Fields list, double-click each field from this table or query that you want to include in the query you're creating. If you add the wrong field, just double-click it in the Selected Fields list. It will go back home. If you just want to start all over, click the double-left chevron (that's what you call the symbol that looks like a less-than sign) and all the selected fields go away. After you select all the fields, click Next. If the wizard can determine the relationships between the tables you selected, a window appears. If you don't see the window, not to worry. Access just wants you to name the query instead. Skip the next step. If you include fields from two tables that aren't related, a warning dialog box appears. The dialog box reminds you that all the selected tables must be related before you can run your query — and suggests that you correct the problem before continuing. And you'll need to do so, so if you're trying to include fields from more than one table and the tables are not yet related, exit out of the Query Wizard (click Cancel) and then go set up the relationship(s). You can return to the Query Wizard after you've done so. If the wizard asks you to choose between a Detail and a Summary query, click the radio button next to your choice and then click Next. Detail creates a datasheet that lists all records that match the query. As the name implies, you get all the details from those records. Summary tells the wizard that you aren't interested in seeing every single record; you want to see a summary of the information instead. If you want to make any special adjustments to the summary, click Summary Options to display the Summary Options dialog box. Select your summary options from the check boxes for the available functions — Sum, Avg, Min, and Max — and then click OK. In the wizard page that appears, select a radio button for what you want to do next. If you want to make your query snazzy: Select the Modify the Query Design option. The wizard sends your newly created query to the salon for some sprucing up, such as the inclusion of sorting and totals. If you want to skip the fancy stuff: Select the Open the Query to View Information option to see the Datasheet view. The wizard runs the query and presents the results in a typical Access datasheet. Type a title for your query in the text box and then click Finish. The wizard builds your query and saves it with the title you entered; then Access displays the results.

Step by Step / Updated 03-27-2016

When you open Access 2016, the workspace offers you a list of recently opened databases, along with templates you can use to start a new database. To open a database from the Recent Databases list, just point to it and click once. If you remember where your database is stored, but it isn’t in the Recent list, use either of the following options to use the Open view:

Step by Step / Updated 03-27-2016

If you want the database in which you’re placing the data to replace the source in Access 2016, then import. This is the option for you if you’re creating an Access database to replace an old spreadsheet that no longer meets your needs. Also import if the source data is supplied by an outside vendor in a format other than an Access format. For example, suppose you receive cash-register sales data from an outside vendor on a monthly basis in spreadsheet format. Access is a great tool for reporting, so you can import the data into Access and use its reporting tools to generate your reports. Here are the steps for importing or linking data sources to your Access database:

Step by Step / Updated 03-27-2016

Adding a forgotten field to your table in Access 2016 is as easy as stopping by the store to pick up forgotten milk. No need for angry words. With the field-challenged table open, calmly follow these steps to add the field you’re missing:

Article / Updated 03-26-2016

The following keyboard shortcuts are especially useful in Access 2016. Some keystrokes work anywhere in Access 2016, whereas others work only in specific views, as noted below. Keystroke Function F1 Opens the Help window. Alt+F Opens the File tab. Ctrl+N Creates a new blank database. Ctrl+O Opens an existing database. Ctrl+P Opens the Print dialog box. F11 Shows/hides the Navigation pane. F6 Cycles different areas of the program window. Alt+H Displays the Home tab on the Ribbon. Alt+C Displays the Create tab on the Ribbon. Alt+X Displays the External Data tab on the Ribbon. Ctrl+S Saves the current object in Design mode. Ctrl+C Copies selection to the Clipboard. Ctrl+V Pastes selection from the Clipboard. F12 Opens the Save As dialog box. Ctrl+F4 Closes the active document window. Alt+F4 Closes the active application window. Ctrl+Z Undoes the last operation. Ctrl+; Inserts the current date. Ctrl+: Inserts the current time. Ctrl+' Copies same field data from previous record. F2 Toggles selecting all data in the field or placing cursor in edit mode. F9 Recalculates fields on a form or refreshes a lookup combo or list box list. Ctrl+Enter Inserts a line break in Datasheet and Form view. Ctrl+Enter Opens selected object from Navigation pane in Design view. Ctrl+ + Inserts a new record. Ctrl+- Deletes current record. Right Arrow Moves selected control to the right in Design or Layout view. Left Arrow Moves selected control to the left in Design or Layout view. Down Arrow Moves selected control down in Design or Layout view. Up Arrow Moves selected control up in Design or Layout view. Shift+Right Arrow Increases selected control width in Design or Layout view. Shift+Left Arrow Decreases selected control width in Design or Layout view. Shift+Down Arrow Increases selected control height in Design or Layout view. Shift+Up Arrow Decreases selected control height in Design or Layout view. F4 Toggles property sheet window in Design view. Shift+F2 Zooms in on current field in Datasheet and Form view.

Article / Updated 03-26-2016

If you like keyboard shortcuts when you're working with software, Access 2016 has a Key Tips feature that can help make entering data move more quickly. The following section describes how to use it: Press the Alt key. When you want to switch tabs and issue commands with the keyboard in Access 2016 (rather than with the mouse), press the Alt key. As shown in the following figure, pressing Alt causes numbers and letters to appear in small squares on the Quick Access Toolbar and the Ribbon's tabs. Press a Key Tip to set the focus to that item (like C in this case for the Create tab). Key Tips appear for the item, as shown in the following figure. Press a Key Tip (like TN to create a new table). When you press one of the characters on your keyboard, the command assigned to the Key Tip runs.

Article / Updated 03-26-2016

Access has some great wizards that make the importing process easy. Here are the steps for importing or linking data sources to your Access database: Open the Access database that will hold the imported data and click the External Data tab on the Ribbon. The Import & Link group of buttons appears on the Ribbon. Each button is connected to a wizard that walks you through the process: Common file formats such as Excel or Text (labeled as Text File) have their own specific buttons; you can find the less common file formats using the More button. Click the button that matches your file format. A Get External Data dialog box specific to the selected file format appears onscreen. Select the data source that you want to import or link to Access. Usually this is a file. It could also be a SharePoint site or an Outlook folder. Select the method of data storage. This is where you tell Access whether to import or link the data. Follow the remaining steps in the Get External Data dialog box and then click Finish. From this point forward, the steps depend on which data format you're importing. Follow the prompts carefully. The worst that can happen is that you get an imported (or linked) table full of gibberish. If you do, check the format of the source file. For example, if the source is a text file and you get gibberish, you may need to confirm that the text file was saved as a delimited file (with a character — a comma, for example — placed between fields). It's also possible that the source file isn't in the correct format (for example, you may think it's an Excel spreadsheet, but it's not). If you expect to import or link to this type of file often, click the Save Import Steps check box. The check box will be located on the last screen of the wizard. After you check the box, you'll be prompted for a name for your import. If you import this data on a regular basis, such as the first day of the month, click the Create Outlook Task check box. Access will set up an Outlook task for you that includes a button to run the task from Outlook. If you saved the import, click Save Import to complete the import process and save the import for future use.

Article / Updated 03-26-2016

The Report Wizard requires a few more decisions from you than the Report tool, but it's more flexible than the instant Report tool. Here it goes: In your database window, click the Ribbon's Create tab and then click the Report Wizard button. (It's right there in the tab's Reports section.) The Report Wizard dialog box appears, listing all the fields in the active table. You'll also find a drop-down list from which you can choose other tables and queries and two columns of Selected and Available fields, which you use to determine which fields from the selected table(s) will be used in your report. Use the Tables/Queries drop-down list to choose the table you want to start with. The fields from the table you select appear in the Available Fields box. Add fields to your report by double-clicking them in the Available Fields box. By double-clicking, you add the fields to the Selected Fields box, and they become part of the report. You can also click a field once and then click the button with a > symbol on it. Repeat Steps 2 and 3 for each table and/or query in the database that you want to include in the report. Click Next to move on to the next page of the Report Wizard; then click Next again. If, at any point, you want to add all the fields in a given table or query, just click the >> button to add all the Available Fields to the Selected Fields list. Clicking Next twice bypasses grouping questions, which, for a simple report, are often unnecessary. Choose a sort order for your report — typically sorting on the field that folks will use to look up information in the report. Click Next. For example, if your report documents a list of employees, Last Name might be a good choice. A report on product sales would be useful in Product Number or Product Name order. You can sort by more than one field, choosing up to four fields to sort by and either Ascending or Descending for the sort order on each field. Next, if you've chosen fields from more than one table, you're asked to choose how you want to view them. This essentially indicates which table is the dominant or main table for the report. In the case of the demo here, choose Events, which is the default because it's the table from which you selected the first report fields. Another step that appears if you've included fields from more than one table is shown below. In this step, you choose how you'll group your report's records. EventType is chosen here, so that all the Collection events are together, then Education, then Fundraising, in that order. To apply the grouping, click the field name in the box on the left, and then click the right-pointing arrow. This places the selected field in blue, above the rest of the fields included in the report. Choose a Layout and an Orientation from the two sets of radio buttons and click Next. Layout options (Tabular or Columnar) are simple — you either want to see your report as a list (Tabular) or in sections (Columnar), in which each record appears in a section on its own. Justified is similar to Tabular, but groups the fields in a sort of stacked jumble. Orientation decisions (Portrait or Landscape) are generally easier if you envision the report in your head — are there more fields than will fit across a sheet of 8.5-inch-wide paper? If so, choose Landscape to give yourself 11 inches of paper (or 10 inches, to allow for the smallest margin possible) across which your fields will appear. Click Next. A default name for your report now appears in this next step in the wizard. Give your report a name. Type a name in the long box at the top of the dialog box. At this point, you also need to decide how to finish things up — with a Preview of the report, or by leaping right into Design view to make more changes to your report's appearance and content. For now, choose to Preview the Report, which is the default. When you click Finish, the report appears in a Preview window, at which point you can print it or save it for future use. (Press Ctrl+S, or click the Save button and give the report a name, when prompted, to take care of the saving business.) After you've saved it — or if you have no need to save it or continue viewing it — you can close it by right-clicking the report's tab and choosing Close from the pop-up menu. If you make additional changes and haven't chosen to save, you'll be asked if you want to close without saving. Of course, you'll probably want to make changes to the report, such as changing column widths where information is being cut off, such as field names (see the FundsNeeded and FundsRaised labels) and their data (see the EventDate label and data), as shown below. This is done in either Design or Layout view, and to switch to either view, right-click the report's tab and choose the view you want to work in from the popup.

Article / Updated 03-26-2016

Relationships between fields, tables, records, and so on, are the most common things you deal with in Access 2016. Keep these ten tips in mind: A one-to-many relationship connects one record in the parent table to many records in the child table. Set the relationship to connect orders to customers or locations to events. One customer can have many orders, and one location can have many events. This way, you don't have to repeat all the customer contact information on each order, nor all the location information on each event. A one-to-one relationship connects one record in the parent table to one record in the child table. This isn't a common relationship type but can be used if you need to split a table that contains many fields into two tables. Relate the primary key field in the parent table with that same field (called the foreign key) in the child table. This is the most common scenario. The parent table contains a primary key field, and the child table contains the same field name. For example, a Customers and Orders table might share a CustomerID. CustomerID is normally the primary key of Customers and a foreign key in Orders. Fields joined in the relationship must be of the same data type. You can't relate a text field in the parent table to a number field in the child table or vice-versa. The fields must be either text fields or number fields in each table. Enforce referential integrity in the Edit Relationships dialog box to prevent "orphan" record entry in the child table. An orphan is a record in the child table that doesn't have a corresponding record in the parent table. The classic example is an order in an Orders table for a customer not in the Customers table. Referential integrity prevents entering these kinds of orphan orders. Set Cascade Update Related Fields in the Edit Relationships dialog box to update the key value in the child table when it's updated in the parent table. Suppose you have two tables, a list of categories in an ExpenseType table (field name Category) and a field in an Expenses table called Category. The Category field in Expenses is populated by a combo box that pulls its data from the ExpenseType table's Category field. Further suppose that a typo was made for Dining; it was spelled Dinning. If you'd set Cascade Update Related Fields, you can edit Dinning in ExpenseType to Dining, and it will change on every record where used in the Expenses table. Set Cascade Delete Related Records in the Edit Relationships dialog box to delete related records in the child table when the corresponding record is deleted in the parent table. With this setting on in a relationship between LocationID in Locations and LocationID in Events, delete a location record and you'll delete all the events for that location. Setting relationships between tables automatically sets joins for those tables when building queries. Relationship joins are carried through to query Design view. If you set a relationship between Locations and Events on LocationID, you'll see that join line when you add those tables to a new query in query design. Delete a field that is part of a relationship and you'll delete the relationship. You can't have a relationship between two fields if one of them is missing, right? So, Access removes the broken relationship when you delete a field that's part of that relationship. If a primary key is part of a relationship, you can't change the primary key in that table to another field without first deleting the relationship. To change primary keys in a table where its primary key is part of a relationship, you must first open the Relationships window and delete the relationship before Access will allow you to change the primary key to another field in that table. In the Relationships window, right-click on the join line between the two tables and choose Delete to delete the relationship.