CIS-140 Lesson SEQUENCED- 1 Database Essentials, CIS-140, CHPTR=2- CREATING DATABASE TABLES, CIS- 140 CHPTR 3 =Working with Tables and Database Records,, CIS-140, CHAPTR+4=Modifying Tables and Fields, CIS-140 CHAPTER 5, ADVANCED RELATIONSHIPS...
The following is a summary of the first three normal forms:
Any changes you make to the data in the source are reflected in the linked table in the destination database, and vice versa. You find out more about normalizing tables
Click to access
Featured
13. Click
Next.
10. Click the Grouping Options button at the bottom of the dialog box.
The Grouping Intervals dialog box appears,
9. In the Navigation Pane, select the Part-Time Employees:
Table if it is not already selected.
Identify Object Dependencies 1. Click the Product Placements:
Table in the Navigation Pane to select it.
Use the Keyboard to Navigate among Records
11. Press the Tab key to move to the next row.
Creating a Blank Database
3. In the File Name box, type BlankDatabase.
Define and Modify a Multifield Primary Key
5. Click any field name to remove the selection.
5. On the Arrange tab, in the Table group, click the Stacked button.
The In Production control is added to the bottom of the stacked layout.
16. On the Home tab, in the Records group, click the Delete button arrow and
then click the Delete command on the menu that appears.
6. In the Show row, under the First Name field, click the Show checkbox
to deselect it. The First Name field data will not appear in the query results.
7. In the Text color section, click the Ellipses button
to display the Color dialog box. Notice the options available, then click Cancel to close it.
10. Press the Delete key
to remove the table.
8. In the Linked Table Name box,
type Benefit_Providers.
12. Click the with A on top down arrow on the Group On Company Name row in the Group, Sort, and Total Pane
and then select with Z on top from the drop-down menu. The sort order is changed from ascending to descending order.
4. Click the Total On menu down arrow
and then select Contract Amount.
11. Click the Finish button to create the forms. The Doctors form
appears with the Samples Given subform,
Take Note If you save a report or other object) while a filter is
applied, it will be available the next time that
2. In the Tools group on the Table Tools Design contextual tab,
click the Delete Rows button. The field row is deleted from the table.
4. On the Home tab, in the Sort & Filter group,
click the Remove Sort button. The Sort is removed.
7. On the Home tab, in the Records group,
click the Totals button. The Total row is hidden.
16. On the Print Preview tab, in the Zoom group,
click the Zoom button arrow and then select 50% from the menu.
Set a Field Property in Datasheet View and Design View 4. On the Home tab, in the Views group,
click the bottom half of the View button and then click Design View.
Apply a Theme the AlpineSkiHouse-final
database that is open from the previous exercise.
11. CLOSE the
database.
16. CLOSE the
database.
2. CLOSE the
database.
4. Click the Setup button. The Page Setup
dialog box appears, . Click the Cancel button.
6. Click Next again and then click Finish to
display a simple select query in Datasheet view.
SORTING AND FILTERING DATA AND SETTING FIELD VIEWING OPTIONS WITHIN A TABLE It is often helpful to display data in order,
display similar records, or hide and freeze certain fields without affecting the preexisting data.
8. Click the ZIP column header to
display the corresponding field properties.
5. Click the Font size:
down arrow and then select 9.
10. Position the mouse pointer and
drag to draw a rectangle
11. Select the Contact First Name
field and then click the > button.
13. Click the Field cell in the first blank column (to the right of the newly created calculated
field and then click the Builder button in the Query Setup group to open the Expression Builder dialog box.
5. Click the Field cell in the first blank column (to the right of the TradeInValue
field and then click the Builder button in the Query Setup group to open the Expression Builder dialog box.
3. Double-click the Description
field to add it to the form.
4. Double-click the In Stock
field to add it to the form.
5. Double-click the Price
field to add it to the form.
Troubleshooting If your chart is not displaying correctly
for example, the text looks stretched), you need to increase or decrease the width and/or height of the placeholder.
STEP BY STEP Import Data from Excel 1. OPEN the Tailspin database
from the data files for this lesson and then SAVE the database as Tailspin-final.
3. On the Form Layout Tools Design contextual tab,
in the Themes group, click the Themes button. A gallery of themes appears (see Figure 5-10).
3. Click the Description
label to select it.
Troubleshooting If you click the Close button (the X in the upper-right corner
, Access closes and you will have to reopen it for the next exercise.
Freeze/Unfreeze and Hide/Unhide Fields
1. OPEN the Customers table.
Sort Data within a Table
1. OPEN the Customers table.
Apply a Filter
1. OPEN the Inventory table.
Create a Delete Query
1. On the Create tab, in the Queries group, click Query Wizard.
Create a Make Table Query
1. On the Create tab, in the Queries group, click the Query Wizard button.
Save a Filter as a Query
1. On the Create tab, in the Queries group, click the Query Wizard button.
Split a Database
1. On the Database Tools tab, in the Move Data group, click the Access Database button. The Database Splitter Wizard appears.
Use a Template to Create a Database
1. On the top of the startup screen window, in the Search for online templates search box, type personal, and then press Enter.
Define and Modify a Multifield Primary Key
1. Press and hold the Ctrl key.
14. In the Navigation Pane, double-click the Applicants
: Table to open the new table with imported data.
4. Click the Room ID field to select it and then click the
< button to move it back to the Available Fields list. Click the Next button.
29. CLOSE the
Field List and the form.
9. Change to Datasheet view and enter the records shown in Figure 9-3 in the table.
The ID field will be automatically generated, so just press Tab to get past it.
Backstage view • Options:
Use this option to customize language, display, proofing, and other settings.
8. Type Gross Sales
and then press the Tab key.
Rename a Table 6. Click Rename. The table name i
s now selected for renaming
Defining Input Masks You use an input mask whenever you want users to enter data in a specific way. An input mask can require users to enter dates in a specific format,
for example, DD-MM-YYYY, or telephone numbers that follow the conventions for a specific country or region.
4. Click the Room ID header to select it. Position the pointer over the
right border until you see a double-sided arrow.
in the Records group. You can also click the Record Selector box,
right-click the selected record,
in the Records group. You can also click the Record Selector box,
right-click the selected record,and then select New Record from the shortcut menu.
12. Click the Fill
Effects button.
6. Type Delete Query as the title and then click Finish to
display the results of the simple select query.
8. On the Arrange tab,
in the Table group, click the Stacked button. The controls and labels are arranged in a stacked layout.
9. Click Finish. A Link Spreadsheet Wizard message appears
informing you that Access finished linking.
Commands Results
Ctrl+End Moves cursor to the last field in the last record
3. Select Text Filters and then click Begins With. The
Custom Filter dialog box appears.
5. Place the insertion point in the Dimensions box,
click the down arrow and then click 12 X 28.
• Count: Counts the number of items in a column • Sum: Sums a column of numbers
• Average: Averages a column of numbers
Define Input Masks for Fields 1. In the Field Name column in the
upper portion of the table design grid, click in the Start Date cell.
Use a Template to Create a Database
4. In the Assets template results list, click the Updated: Asset tracking database. The Updated: Asset tracking template preview screen appears.
Expand button expanded Table name Available fields
4. In the list of fields, double-click Artist to add it to the form.
Sort Data within a Table
4. On the Home tab, in the Sort & Filter group, click the Remove Sort button. The sort is removed from the Customer ID field.
Review and Modify Data Types for Fields
4. On the Ribbon, click the Fields tab. Notice in the Formatting group that the Data Type is Date/Time.
Review and Modify Data Types for Fields
6. Click the Last Name header. Notice that the Data Type is Short Text and that no formatting options are available for that data type.
Use Navigation Buttons
6. Click the New (blank) record button. The insertion point moves to the first column and last row of the table.
Use a Template to Create a Database
6. Click the folder icon to the right of the File Name box. The File New Database dialog box appears.
Modify Table Properties
8. Click OK.
Apply a Filter
9. Click in the Pounds field of the first record.
7. Click the Finish button.
A datasheet form appears (see Figure 5-9).
4. Click the Next record button on the record navigator at the bottom of the form.
Continue clicking through all the records to see the records in order according to price.
7. On the Arrange tab, in the Position group, click the
Control Padding button and select Medium from the menu.
Creating a Table from Another Table Another way to create a table is to copy the structure of an existing table using the
Copy and Paste commands. In this exercise, you copy the structure of an existing table to create a new table.
Commands Results
Ctrl+Up Arrow Moves cursor to the current field in the first record
3. Click the Current Database button on the left to display the
Current Database menu of the Access Options dialog box.
6. Click Less Than to display the
Custom Filter dialog box.
17. OPEN the database in regular mode.
Notice that a password is no longer required to open the file.
5. Click the Value (Z) axis check box to remove the check mark.
Notice that the values on the Z axis are removed.
Creating a Blank Database
4. If you want to save the file in a location other than the one shown beneath the File Name box, click the folder icon to the right of the File Name box and browse to a different location.
Save a Table
15. Notice the new table object named Backup of Sample Table in the Navigation Pane.
Creating a Table from Another Table
4. Click OK. The new table appears at the end of the list of database table objects in the Navigation Pane.
13. Click the From center button in the Shading
styles section and then click OK.
ATTACHING AND DETACHING DOCUMENTS Access 2016 allows you to attach documents,
such as Word documents or photo files, to records in a database.
7. Click Next to display the final screen in
the Find Duplicates Query Wizard.
13. Click the Group By cell below the Make field cell and
then click the down arrow to display the aggregate function menu.
12. On the Ribbon, in the Views group, click the bottom half of the View button and
then select Report View from the menu. The report is shown in Report view. Scroll down to see all the records.
Commands Results
Tab or Right Arrow Moves cursor to the next field
11. Double-click Sales Team Backup:
Table in the Navigation Pane to open the new table,
15. Right-click the Legend and choose Format Legend from the shortcut menu.
The Format Legend dialog box appears. Select the Font tab if it is not already displayed. The Font tab of the dialog box appears,
2. On the External Data tab, in the Import & Link group, click Excel.
The Get External Data - Excel Spreadsheet dialog box appears,
Database design principles include standards and guidelines that can be used to determine if your database is structured correctly. These are referred to as normal forms.
There are five normal forms, but typically only the first three are applied because that is usually all that is required.
8. Click the In Stock
control to select it.
10. In the Update To row of the asking price
field, type [AskingPrice] + 500.
You need to be familiar with writing expressions, which are similar to
formulas, and be familiar with the criteria that you specify when designing a query.
17. Right-click on the left axis that shows the
number of dollars and choose Format Axis.
Use the Control Wizard to Add Button Controls
1. Change to Design view, if necessary.
Find and Replace Data
1. OPEN the Customers table.
Find and Replace Data
13. Press the down arrow to remove the selection and allow Access to save the change.
Database Essentials
Access
Take Note When you create a new field in a table and then enter data in it,
Access 2016 automatically tries to detect the appropriate data type for the new column.
9. On the Design tab, in the Query Type group, click Update.
Access adds the Update To row in the query design grid,
12. The table opens as a PDF or XPS.
CLOSE the file and return to Access.
[Products WHERE
CategoryID = Forms.
9. SLECT ANY SORT OPTION
FOR THE LOOKUP FIELD
4. Click the >> button to move all the fields from the Available
Fields to the Selected Fields box and then click Next.
4. Click the
ID control to select it.
7. Type Fabrikam,
Inc. in the Company box
3. SELECT TH
LOOKUP TAB
12.ADJUST THE WIDTH
OF THE COLUMNS AS DESIRED
7. Click
OK.8. CLOSE
Database Essentials
Office 365
Database Essentials
OneDrive
Click to access
Open
Database Essentials
Open
SOFTWARE ORIENTATION Navigation Pane
Pane menu
6. Click the Close button on
Photo Label to close the form
Modify Table Properties 9. Click the Close button on the
Property Sheet pane to close it.
5. Click the Field cell in the first column, click the down arrow and then click
Referred By on the drop-down menu.
15. Type $Fabrikam09fc and then click OK.
The database is now decrypted.
13. Click the Delete button.
The sort is cleared.
Data Type Example Description Currency Registration Fee: $50.00 Stores monetary data with precision to four decimal places.
Use this data type to store financial data and when you don't want Access to round values.
16. CLOSE the
Used Cars Sold table.
IF THE RELATIONSHIP AND COMBO BOX WERE DELETED BECAUSE AN ERROR WAS MADE
WHEN CREATING A RELATIONSHIP, REPEAT THE LOOKUP WIZARD TO CREAT THE RELATIONSHIP CORRECTLY.
5. SAVE
YOUR CHANGES.
6. Press the Tab key again to
accept the Short Text data type.
2. In the Show Table dialog box, double-click Inventory to
add it to the upper section of the query design grid.
6. Click the Value (Z) axis check box
again to insert the check mark.
3. In the list of table fields, double-click Year, Make, Model, Trim,
and AskingPrice to add those fields to the design grid at the bottom of the page
18. Type 2008-2017 Income
and Expenses in the Title box.
5. In the Available Fields box, double-click the Last Name, First Name, Specialty,
and Hospital fields to move them to the Selected Fields
6. CLOSE the worksheet
and then CLOSE Excel.
3. Select the text in the label
and then type Investment:.
5. In the Detail section, click the Future Period Contract Projections subform,
as shown in and then press Delete on the keyboard to delete the subform.
Create Fields 4. Type On-site staff?
as the column name.
22. A message saying that the wizard couldn't rename your table
because it's open and the new query will be saved as Books_NEW appears. Click OK.
Create Fields 3. Right-click the Field1 column header to
display the shortcut menu and then click Rename Field.
7. In the Price is less than or
equal to box, type 500 and then click OK.
Use the Report Wizard 1. OPEN Messenger database from the data
files for this lesson and then SAVE it as Messenger-final.
Take Note You cannot use the Find command when you are
in Report Design view; however, you can use it in tables, forms, and queries.
Database Essentials
return to Access
Set a Field Property in Datasheet View and Design View 5. In the Field Name column in
the middle portion of the table design grid, click in the Location cell.
Encrypt and Decrypt a Database
1. Click the File tab and then click Info, if necessary. On the Info menu that appears, click the Encrypt with Password button. A Microsoft Office Access message box appears saying you must open the database in Exclusive mode.
Arrange and Anchor Controls within a Layout
1. Click the Inventory table in the Navigation Pane to select it.
Enter, Edit, and Delete Records
7. Click Kona in the record to position the blinking insertion point there. Delete Kona, type Hawaiian, and then press Tab.
Attach and Detach Documents
7. Click OK. The number of attachments in the first record changes to 1.
Save a Table
7. In the Table Name box, type Sample Table.
Use a Template to Create a Database
7. Navigate to the location where you want to save the file and then click OK.
5. Click the down arrow on the As menu and then select Query,
Notice that you can also save this table as a duplicate table
12. CHECK THE ENABLE DATA INTEGRITY CHECKBOX AND SELECT EITHER THE CASCADE DELETE
OR THE RESTRICTED DELETE RADIO BUTTONS AS DESIRED
4. Type Summary
Query in the Save box.
6. Click the Save button on the Quick Access Toolbar. The Save As dialog box appears with
Rooms in the Report Name box. Click OK. Notice that the Rooms report is listed in the Navigation Pane.
7. In the Navigation Pane, double-click the Applicants:
Table to open the table. The existing data has been replaced with new imported data
Finding and Replacing Data The Find and Replace dialog box searches only one table at a time; it does not search the entire database.
The Look In menu allows you to choose to search by field or to search the entire table.
Define Table Relationships 1. On the Database Tools tab in the Relationships group, click the Relationships button.
The Relationships view appears with the Customers table represented.
18. Click the Finish button to accept the settings.
The Report Wizard creates the report, shown in Figure 11-4, with the groups you specified.
4. Click the Add a group button. Select Region Borough) from the Group On menu.
The Report is now grouped on the Region Borough) field.
13. Click the Close button.
The Sample Preview dialog box closes.
Using the Application Parts Gallery and Quick Start
The Templates group on the Create tab contains the Application Parts gallery that you can use to insert predefined templates.
3. On the Report Layout Tools Design contextual tab, in the Themes group, click the Themes button.
The Themes gallery of predefined report themes appears.
3. Click the with no totals down arrow.
The Totals menu appears.
10. Click the File tab
and then click Options.11. Click the Current Database button on the left,if it's not already selected.
11. Click the File menu
and then click Save
13. Click the File tab
and then click Save.
15. Click the File tab
and then click Save.
8. In the Expression Elements section, click the Toy Summary element,
and then find and double-click the Price field in the Expression Categories section.
Freeze/Unfreeze and Hide/Unhide Fields 2. Select the Last Name field,
and then on the Home tab, in the Records group, click the More button.
Modify Table Properties 4. On the Design tab, in the Show/Hide group,
click Property Sheet. The Property Sheet pane appears on the right of the Access window
9. On the Design tab, in the Results group,
click Run to display the records to be deleted,
11. On the Design tab, in the Results group,
click Run. An alert message appears asking if you are sure you want to update the records.
14. On the Design tab, in the Results group,
click Run. The query results are displayed,
4. On the External Data tab, in the Export group,
click Saved Exports to open the Manage Data Tasks dialog box.
2. In the New Query dialog box,
click Simple Query Wizard and then click OK.
9. Select the Company Name field in the Available Fields list and then
click the > button to place it on the Prototype label. Press Enter.
3. If the Field List pane is not already displayed,
click the Add Existing Fields button on the Design tab in the Tools group. The Show All Tables link appears.
6. On the Home tab, in the Sort & Filter group,
click the Advanced button and then click Filter by Form.
14. With the controls and labels still selected, on the Arrange tab, in the Sizing & Ordering group,
click the Align button and then select Right from the menu that appears. The labels are right-aligned to the controls.
2. On the Home tab, in the Find group,
click the Find button. The Find dialog box appears
21. On the Report Design Tools Design tab, in the Tools group,
click the Property Sheet button, if necessary.
2. On the Create tab, in the Queries group,
click the Query Wizard button to display the New Query dialog box.
25. On the Home tab, in the Records group,
click the Refresh All button to ensure the chart displays the latest table data.
11. In the Table group on the Ribbon,
click the Remove Layout button.
12. On the Arrange tab, in the Table group,
click the Remove Layout button.
7. On the Home tab, in the Sort & Filter group,
click the Remove Sort button. The Sort is cleared.
13. On the shortcut menu that appears,
click the Rename command.
3. On the Create tab, in the Reports group,
click the Report Wizard button. The first Report Wizard dialog box appears.
Use the Group, Sort, and Total Pane 1. In the Navigation Pane,
double-click the Corporate Sales report to open it. Notice that the report is not arranged by groups.
Database Essentials
new
Database Essentials
opened
8. On the Save Export Steps
screen, click Close.
Review and Modify Data Types for Fields 9. Scroll to the far right and then click the Click to Add column header. In the Data Type drop-down list that appears,
select Yes/No ). Once you click Yes/No, notice the name of the column header, Field1, is highlighted.
4. Type preview in
the Subject box.
9. The results of the query are displayed, as shown in Figure 12-28. Results include all of the rows from
the first table and only those records from the second table that match the join field in the first table.
5. Click in the upper-left corner of the Page Footer section, hold the mouse button, and drag to
the lower-right corner to create a rectangular placeholder where the chart will be inserted
7. Click OK. Notice the file path and name of the saved template in
the message box that appears. Click OK to close the message box.
5. Click Next to display
the next screen.
4. Click the Axes tab to display
the options on the tab.
For example, when referential integrity is enforced, Access will not permit a Customer ID value as the foreign key in the Order Summary table that does not have a matching Customer ID value as
the primary key in the Customers table. In this way, referential integrity ensures your tables contain logically related data.
The foreign key is used to reference the data from
the primary key to help avoid redundancy.
When you divide information into separate tables,
the primary keys help Access bring the information back together again.
Modify Table Properties 5. Place the insertion point in
the property box for Description.
Separate, neither the area code data nor the phone number data is
unique and can be duplicated; however, together they form a unique set of numbers that cannot be duplicated.
26. Scroll to the bottom of the report to
view your chart, which should look similar to
Set Database Properties
1. OPEN the Fabrikam-final database that you saved in an earlier exercise.
Create an Update Query
1. On the Create tab, in the Queries group, click the Query Wizard button.
Find Unmatched Records
1. On the Create tab, in the Queries group, click the Query Wizard button. The New Query dialog box appears.
following folder in both Windows 8 and Windows
10: C:\Users\<username>\AppData\Roaming\
Save a Table
17. CLOSE the Backup of Sample Table. STOP. CLOSE the database and then EXIT Access.
Attach and Detach Documents
9. Click the Open button. The attachment, an invoice document, opens in Word.
Print Table Relationships 4. Click the Close button to close the Relationships for Fourth Coffee-final tab.
A message appears asking if you want to save changes to the report. Click No.
8. Click the Add a group button and then select Company Name from the menu.
A new group level is added to the report.
Take Note If you want to sort or filter data,
Access will exclude the Total row by default
The table is structured through rows and columns, which become meaningful as you enter appropriate data.
Any time you add a new column to the table, Access defines a new field for that column's data.
5. Click OK. The Encode 'New Employees'
As dialog box is displayed
Define and Modify a Multifield Primary Key
Be sure you are still in Design View on the Order Summary table.
4. When you release the mouse button, the Command Button Wizard appears. In the
Categories: section, click Form Operations, and in the Actions section, click Close Form.
Database Essentials
Connect to
2. SELECT THE
DESIRED FIELD
For example, if you open a Word resume that is attached to a record, the Word program starts and you view the document in Word.
If you do not have the program that was used to create a file, Access prompts you to choose a program you do have to view the file.
Troubleshooting In step 7, don't double click the form.
If you do, the form opens and you can't delete the form when it is open.
1. Double-click the Rooms report in the
Navigation Pane to open it.
15. Click
Next.
6. Click OK to return to the Get External Data - Access Database dialog box and then click Close.
Notice the newly imported Minute Employees table appears in the Navigation Pane.
Database Essentials
Open dialog
7. Click the In Stock field control to select it.
Press and hold the Shift key and then select the In Stock control label.
10. Click the Next button.
The fourth Chart Wizard dialog box appears.
6. On the Design tab, in the Header/Footer group, click the Title button.
The title control with the title Toy Summary is inserted in the Report Header section. The text in the title is selected.
8. Click the Form Title label, delete Form
Title and then type Doctors.
5. Double-click Image
Title to add it to the form.
9. Click the Custom
Types tab.
NAVIGATING AMONG RECORDS Database tables can be very large, and contain useful information that can be manipulated in different ways.
When a table contains many records and fields, it is important to be able to navigate among them.
4. Click the New Rule button. The New Formatting Rule dialog box appears.
You will create a new rule based on criteria you will enter.
The Print Preview tab, as shown in has commands for viewing tables, queries, forms, and reports in
a variety of ways so you can adjust their layout and view your changes before you print them.
Define Database Tables 1. On the Database Tools tab, in the Relationships group, click Relationships to display
a visual representation of the relationship between the Students and Guardians tables
15. Click the Value check box
again to remove the check mark.
5. Click the File tab
and then click Save.
14. Click the In Production control label that says Text and a number)
and then select the text in the label.
17. Click the empty cell next to the Format box, and then click the down arrow to display the Format menu,
as shown in Select Euro from the options that appear to format the expression result as Euro.
16. Select the ID label and control and in the Sizing & Ordering group, click the Align
button, and then select Right from the menu that appears.
7. On the Design tab, in the Query Type group,
click Append. The Append dialog box appears,
3. Use the File Open dialog box to locate the Minute Insurance Access
database file and then click Open.
Click to access
desktop database
12. Click the Table2 name and then click the Rename Table button. If you receive a message stating that an
expression you entered is the wrong data type for one of the arguments, click OK. The Table Analyzer Wizard dialog box appears.
2. Scroll through the table to become
familiar with the fields in the table.
For example, area code field data and phone number
field data can be combined to create a unique combination of numbers that cannot be duplicated.
6. Double-click the In Production
field to add it to the form.
4. Double-click the Description
field to add it to the report.
1. Click the Location
header to select it.
9. Type $Fabrikam09fc
in the Verify box
3. Type Fabrikam2002-2003
in the file name box.
• Standard Deviation: Measures how widely values are dispersed from an average value (a
mean) in a column
8. Click the Save button
on the Quick Access Toolbar.
Freeze/Unfreeze and Hide/Unhide Fields 8. Click the Maximize button
on the application window.
15. SAVE the
report.
16. Type In Production in the text box and
reposition the control to fix spacing if necessary.
5. Right-click the Price field to display the
shortcut menu and then click Number Filters.
3. If you want to specify a different destination, click Browse to open
the File Save dialog box. Choose a folder and then click Save.
9. Click the Add a sort button in
the Group, Sort, and Total pane.
Delete a Field 2. Right-click the column header to display the shortcut menu and
then click Delete Field. In the dialog box that appears, click Yes.
6. Click the File tab and
then click Save.
2. Right-click in a blank area in the Navigation Pane and
then select Paste from the menu. The Paste As dialog box appears.
3. In the Criteria row of the Position field, type Like "*Manager"
, as shown in to display all records that end with the string "Manager" in the Position field for example, Assistant Manager, Sales Manager, Product Manager, and so on
Apply a Filter 18. On the Home tab, in the Sort & Filter group
, click the Selection button, and then select
13. Scroll down and to the right, if necessary
, to see the data contained in the records.
Adjust Page Margins and Change Page Orientation for Forms and Reports
1. Click the Modified Inventory Form in the Navigation Pane and then click the File tab.
Apply a Theme GET READY. USE the Graphic Art-final database
1. Double-click the Image Info form in the Navigation Pane to open it.
Save a Database Object as Another File Type
1. Double-click the Income & Expenses Summary table in the Navigation Pane to open it.
Add a Table to a Query
1. Double-click the Industry Friends Query in the Navigation Pane to open it.
Create a Query from Multiple Tables
1. In the Navigation Pane, double-click Employees: Table to open the table.
Save Export Specifications
1. In the Navigation Pane, select the New Employees: Table.
Export from a Query to Word
1. In the Navigation Pane, select the Part-Time Employees Query.
Export from a Table to Excel and Other Formats
1. In the Navigation Pane, select the Part-Time Employees: Table.
Create and Delete a Report
1. OPEN AlpineSkiHouse from the data files for this lesson and then SAVE the database as AlpineSkiHouse-final.
Configure Database Options
1. OPEN Fabrikam Inc from the data files for this lesson and then SAVE the database as Fabrikam Inc-final.
Back Up a Database
1. OPEN Fabrikam from the data files for this lesson and then SAVE the database as Fabrikam-final.
Use the Keyboard to Navigate among Records
1. OPEN Fourth Coffee from the data files for this lesson.
Create a Custom Table in Design View
1. OPEN Lucerne Publishing from the data files for this lesson and then SAVE the database as Lucerne Publishing-final.
Before you begin these steps, make sure that your computer is on. Sign on, if necessary, and start Access.
1. OPEN Wingtip Inventory from the data files for this lesson and then SAVE the database as Wingtip Inventory final.
Merge Databases
1. OPEN the Alpine database from the data files for this lesson, and then SAVE the database as Alpine-final.
Create a Simple Form and Delete a Form
1. OPEN the Graphic Art database from the data files for this lesson and then SAVE the database as Graphic Art-final.
Attach and Detach Documents
1. OPEN the Order Summary table.
Create a Table Using the Application Parts Gallery and Quick Start
1. On the Create tab in the Templates group, click the Application Parts button to display the gallery shown
GET READY. USE the Graphic Art-final database
1. On the Create tab, in the Forms group, click the Blank Form button. A new blank form is created in Layout view.
GET READY. USE the Graphic Art-final database
1. On the Create tab, in the Forms group, click the Form Design button. A new blank form is created in Design view 2. On the Form Design Tools Design contextual tab, in the Tools group, click the Add Existing Fields button. The Field List pane appears.
Create a Calculated Query Field
1. On the Create tab, in the Queries group, click Query Design.
Create an Aggregated Query
1. On the Create tab, in the Queries group, click Query Design.
Use the Database Documenter
1. On the Database Tools tab, in the Analyze group, click the Database Documenter button. The Documenter dialog box appears,
Run Import Specifications
1. On the External Data tab, in the Import & Link group, click Saved Imports to open the Manage Data Tasks dialog box.
USE the AlpineSkiHouse-final database that is open from the previous exercise.
1. On the Home tab in the Views group, click the bottom half of the View button. Select Layout View from the menu.
Use Filter by Form USE the Graphic Art-final database .
1. On the Home tab, in the Sort & Filter group, click the Advanced Filter Options button and then click Filter By Form. A form filter appears.
Creating a Table from Another Table
1. On the Navigation Pane, right-click the Comments table database object and then select Copy from the shortcut menu.
Review Database Fields
1. On the Student List form, click the ID for record 5 to display the Student Details dialog box for Sharon Hoepf, as shown i
Review Database Fields
1. On the Student List form, click the ID for record 5 to display the Student Details dialog box for Sharon Hoepf, as shown in Figure 1-16.
HOW TO: REMOVE A LOOKUP BY CHANGING THE DISPLAY CONTROL PROPERTY
1.OPEN THE TABLE IN DESIGN VIEW
Use a Template to Create a Database
10. Click to place the insertion point in the first cell of the Item field and then type Canon EOS Rebel T3i. Press Enter.
Use the Keyboard to Navigate among Records
10. Press the Tab key to move to the next cell.
Review and Modify Data Types for Fields
10. Rename the field by typing Additional Contact Info on File?. Press Enter and then click the Additional Contact Info on File? header.
Attach and Detach Documents
11. Click the Access button on the taskbar, if necessary, to return to Access.
Use a Template to Create a Database
11. Click the Shutter Bar Open/Close Button, if necessary, to display the Navigation Pane to see all the objects in the database.
Apply a Filter
11. Click the check boxes to remove the check marks beside Blanks), 30, 35, 40, and 50. Only the check mark beside 25 should remain.
Enter, Edit, and Delete Records
11. On the Home tab, in the Records group, click the Delete button drop-down arrow.
Review and Modify Data Types for Fields
11. On the Ribbon, in the Formatting group, click the down arrow in the Format box to display the menu of formatting options for the Yes/No data type.
Save a Table
11. Under the File Types heading, click the Save Object As command.
Enter, Edit, and Delete Records
12. A dialog box appears asking if you are sure you want to delete the record. Click Yes.
Find and Replace Data
12. Click Cancel to close the Find and Replace dialog box.
Create a Blank Database
12. Click File and then click Close to close the database.
Review and Modify Data Types for Fields
12. Click outside the menu to close it.
Attach and Detach Documents
12. In the Attachments dialog box, click the Remove button and then click OK. The attachment is removed from the record.
Save a Table
13. In the Save As dialog box that appears, type Backup of Sample Table in the area under "Save 'Sample Table' to:" as shown
Enter, Edit, and Delete Records
13. Notice that the Undo button on the Quick Access Toolbar is not available because you cannot undo a record deletion. CLOSE the table.
Find and Replace Data
14. CLOSE the table.
Save a Table
14. Click OK.
Save a Table
16. The Backup of Sample Table should now be open. Notice the table contains the same row you created in the Sample Table table.
Create a Blank Database
2. In the templates section of the New screen, click the Blank desktop database icon. A Blank desktop database screen appears in the center of the screen, as shown
Defining Database Tables
3. CLOSE the Students tab.
Attach and Detach Documents
3. In the Add & Delete group on the Table Tools Fields contextual tab, click the More Fields button. The More Fields menu appears
Use a Template to Create a Database
3. In the search box, type assets and then press Enter.
Apply a Filter
3. On the Home tab, in the Sort & Filter group, click the Filter button. A menu appears.
Use Navigation Buttons
3. Select and then delete the number 2 in the Current Record box. Type 5 and then press Enter. The selection moves to the fifth record.
Enter, Edit, and Delete Records
3. Type Hazelnut and then press Tab.
3. Click
30 X 40.
Attach and Detach Documents
4. Click Attachment under Basic Types. The Attachment field is inserted in the table.
Use the Keyboard to Navigate among Records
4. Click Enable Content.
The Ribbon and dialog box launcher
4. Click the Home tab.
Create a Table Using the Application Parts Gallery and Quick Start
4. In the Navigation Pane, double-click Comments to display the newly created table with fields for comments. CLOSE the Comments table by clicking the Comments table Close button.
Apply a Filter
4. Point to Text Filters. A second menu appears. Select Contains (see Figure 3-9). Click Contains.
Find and Replace Data
4. Type Elm into the Find What box.
Define and Modify a Multifield Primary Key
4. With the rows still selected, click the Primary Key button again to remove the primary key designation from both fields.
Use Object Tabs
5. Click Close to close the form.
Creating a Blank Database
5. Click Create button create the blank database. Access creates the database and then
Set Data Validation Rules
5. Click OK.
Save a Table
5. Click to place the insertion point in the cell under the Click to Add field and then type Sample Data.
Attach and Detach Documents
5. Double-click the first row of the Attachments field. The Attachments dialog box appears.
Use a Template to Create a Database
5. In the Desktop asset tracking template preview screen, click in the File Name box and then replace the default file by typing Assets, as shown
Review and Modify Data Types for Fields
5. In the Format box, click the down arrow to display the menu of formatting options for that type,
Use the Keyboard to Navigate among Records
5. In the Navigation Pane, double-click Coffee Inventory: Table to open the table.
Create a Table Using the Application Parts Gallery and Quick Start
5. OPEN the Application Parts menu and then click Contacts.
Sort Data within a Table
5. Select the First Name field, right-click in the field to display the shortcut menu, and then click Sort A to Z. The data in the First Name field is sorted in ascending order.
Enter, Edit, and Delete Records
5. Type 09252016 and then press Enter.
Find and Replace Data
5. Type Little Elm into the Replace With box.
Creating a Table from Another Table
6. Click File and then click Close to close the database.
Enter, Edit, and Delete Records
6. Highlight sunrise in the Search box and then type Kona to locate the Kona record.
Use the Keyboard to Navigate among Records
6. Notice that the first cell of the first record is selected.
Use the Keyboard to Navigate among Records
7. Press the Down Arrow key to move down to the next row. Use the Keyboard to Navigate among Records Notice that the first cell is selected.
Review and Modify Data Types for Fields
7. Scroll to the right and then click the Address header.
Sort Data within a Table
8. CLOSE the table. If a dialog box appears asking if you want to save changes to the table, click No.
Save a Table
8. Click OK to close the dialog box and return to the table, which now is labeled Sample Table.
Use a Template to Create a Database
8. Click the Create button at the bottom of the preview pane. The preview pane indicates that the template is being downloaded. When the download is complete, the preview pane closes.
Define and Modify a Multifield Primary Key
8. Click the Save button on the Quick Access Toolbar.
Enter, Edit, and Delete Records
8. Click the Undo button on the Quick Access Toolbar. Notice Hawaiian disappears and Kona reappears.
Attach and Detach Documents
8. Double-click the attachment number in the Attachment field. The Attachments dialog box appears.
Review and Modify Data Types for Fields
8. In the Data Type box, click the down arrow and then click Short Text to change the data type.
Save a Table
9. Click the File tab to display the Backstage view.
17. CLICK THE FINISH TO COMPLETE THE WIZARD
A LOOKUP WIZARD DIALOG BOX APPEARS
4. In the Tools group on the Table Tools Design contextual tab, click the Insert Rows button.
A blank row is inserted above the Gross Sales field.
Defining and Modifying Data Types for Fields When designing the database, you set a data type for each field column) that you create to match the information it will store.
A data type controls the type of data a field will contain—whether it is text, number, date/time, or some other type of data.
In database applications like Access, you can create a relational database.
A relational database stores information in separate tables and these tables are connected or linked by a defined relationship that ties the data together.
Allowing Zero-Length Strings in a Field When the Allow Zero Length field property is set to Yes, you can enter zero-length strings in a field.
A zero-length string contains no characters; you use the string to indicate that you know no value exists for a particular field.
2. On the Create tab, in the Forms group, click the More Forms button. On the menu that appears, select Split Form.
Access creates the form and displays it in Form view and Datasheet view at the same time
3. On the Create tab, in the Forms group, click the Form button.
Access creates the form and displays it in Layout view Your form may be slightly different.
17. Click OK. The Microsoft
Access dialog box appears again.18. Click OK.. CLOSE the database.
Find and Replace Data 11. Click Find Next again.
Access displays a message saying that no more occurrences of the word have been found. Click OK.
Take Note If you do not add an extension to your database file name,
Access does it for you—for example, Access.accdb.
Apply a Filter 5. The Custom Filter dialog box appears. Type and then click OK.
Access filters the database to display only the records containing the word Decaf. A filter icon is displayed in the header row of the field.
11. On the Design tab, in the Query Type group, click Delete.
Access hides the Show row in the lower section of the design grid and adds the Delete row
Use the Backstage View GET READY. START
Access if it's not already running.
Then select the outermost field and then select the type of sort that you want. After you sort one or more columns,
Access inserts sort arrows in the header row to show that the field is sorted. These sort commands remain with the table until you remove them.
Take Note For example, if you type a price, such as $10,
Access recognizes the data as a price and sets the data type for the field to Currency.
Find and Replace Data 8. Click the Find Next button.
Access searches the table, finds, and selects the word Elm.
If an operation that violates referential integrity is performed once this option is selected,
Access will display a dialog box with a message
Delete a Table Take Note If the table was related to one or more additional tables,
Access would ask if you wanted to delete those relationships before deleting the table.
3. Navigate to the location where you want to save the back-end file and then click Split.
After a few moments, the Database Splitter message box appears stating that the database was successfully split.
2. Click the >> button to move all the fields from the
Available Fields box to the Selected Fields box.
4. Click the >> button to move all the fields from the
Available Fields to the Selected Fields box and then click Next.
5. In the Themes group, click the Fonts button. Select Cambria from the menu, Click the font and the new Font theme is applied.
Be sure to select the Cambria font and not the Office 2007-2010 Cambria font.)
Use a Template to Create a Database 9. Access creates and then opens the Assets database, as shown
Because this database was downloaded from Office.com a trusted source), click Enable Content on the Message Bar.
Use the Table Analyzer 1. Double-click the
Books table in the Navigation Pane to open it.
Define and Modify a Multifield Primary Key 2. Click the Row Selector box beside the Paid row. Continue to hold down the Ctrl key and then click the Order ID Row Selector box.
Both fields should be selected 3-5). If not, continue to hold the Ctrl key and then click the Paid Row Selector box again.
Define and Modify a Multifield Primary Key 7. On the Design tab, in the Tools group, click the Primary Key button.
Both rows should have a key displayed beside them. The resulting composite key is more valid.
5. On the Data tab, in the Control Source row, click the
Build button looks like three periods, ...). The Expression Builder dialog box appears.
SOFTWARE ORIENTATION Navigation Pane
By default, the Navigation Pane, shown in Figure 1-6, appears on the left side of the Access screen each time you create or open a database.
2. SELECT THE FIELD
CONTAING THE COMBO BOX LOOKUP
New Products
CategoryID];
19. On the Print Preview tab, click the Print button. The Print dialog box appears.
Click OK to print or click Cancel to close the dialog box. Notice that you can access the Setup dialog box from here as well.)
5. Navigate to the data files for this lesson and select Toys.jpg.
Click OK. The picture is inserted in the Report Header section.
2. Click the File tab and then click the Save As tab to display the Save As menu.
Click the Save Object As command in the File Types category
16. Click the ID field in the Book Sales table to select it.
Click the Set Unique Identifier button. A primary key is inserted
3. Click Save As to display the Save As menu.
Click the Template option in the Database File Types category.
11. When you release the mouse button, the Combo Box Wizard appears.
Click the button beside I will type in the values that I want and then click Next.
22. Click the Data tab of the Property Sheet.
Click the down arrow at the end of the Enabled cell and then select Yes. This enables the chart to accurately display the associated table data.
Create a Multivalued Lookup Field 12. Click the Finish button. A new column named Coordinator appears after the ID field.
Click the down arrow in the first cell to display the list of names
18. Notice that the Table Analyzer Wizard has detected two similar records, one with an incorrect zip code and phone number.
Click the down arrow in the first row of the Correction field and then select Leave as is. This is the correct record.
4. Click the Objects that depend on me option button.
Click the right-pointing triangle beside the Showroom Contact table to see the tables and forms that depend on the Showroom Contact table.
5. On the Arrange tab, in the Merge/Split group, click the Split Horizontally button.
Click to the right of the ID field control and notice the orange border of the new column created after the split.
5. Click Next to display the next screen.
Comma should be selected as the delimiter.
4. Click the > button or double-click each item to move the Region (Borough), Sales Person Last Name,
Company Name, and Contract Amount fields from the Available Fields list to the Selected Fields list.
In this exercise, you practice defining and modifying a multifield primary key. Two or more primary keys in a table are called the composite key.
Composite keys are useful in unique situations when a combination of data from two fields needs to provide a unique identifier in a table.
11. Click the Close button on the Employees
Contact Query window to close the query.
4. Click the Next record button on the record navigator at the bottom of the form.
Continue clicking to see the five records that contain the word "print" in the Media field.
From Design view, change the data type for the ResidenceAssignment field to use a lookup list. The values in the list should include ResidenceName from the ResidenceHalls table. Items in the list should sort alphabetically by the ResidenceName field. Allow Access to hide the key column. Enforce data integrity, and do not allow deletions that would violate the integrity of records. Save the table.
Correct In the Access Table, you clicked in the Lookup Wizard Cell, clicked the Drop-Down Button, and clicked the Lookup Wizard Item. Inside the Lookup Wizard dialog, you clicked the Next button. Inside the Lookup Wizard dialog from the Table Select list, you selected Table: ResidenceHalls, selected Table: ResidenceHalls. Inside the Lookup Wizard dialog, you clicked the Next button. Inside the Lookup Wizard dialog from the
The selected field, Terms, is a lookup field. Modify the lookup field properties so data entry is limited to items on the list.
Correct Inside the Action Catalog dialog from the All Actions list from the Actions list, you selected Filter/Query/Search. Inside the Action Catalog dialog from the All Actions list from the Actions list from the Filter/Query/Search list, you selected OpenQuery, double-clicked the OpenQuery list item. You clicked the undefined t. In the acMnuMacroQuery Name menu, you clicked the Tuition menu item.
Add a comment to this macro with the text: This macro runs a query that calculates tuition
Correct Inside the Action Catalog dialog from the All Actions list from the Program Flow list, you double-clicked the Comment list item. You focused the t, changed the t. Inside the Action Catalog dialog, you clicked the All Actions list, clicked the All Actions list. You clicked the undefined t.
Change the Navigation Pane grouping option so all database objects of the same type are grouped together all tables together, all forms together, etc.)
Correct You clicked the Navigation Pane Header, clicked the Category/Group menu, and clicked the Navigation Pane Header. In the Category/Group menu, you clicked the Object Type menu item.
Open the Navigation Pane.
Correct You clicked the Shutter Bar Open/Close button.
Create a new desktop database from the Updated: Students template. Do not change the location. Name the database: Students
Correct You opened the backstage view, clicked the New navigation button, clicked the Updated: Students template, typed Students in the File Name box, and clicked the Create button.
Open the Run Tuition Query macro so you can edit it in the Macro Builder.
Correct You right-clicked the Run Tuition Query macro. In the Right-Click menu, you clicked the Design View menu item.
5.SAVE THE TABLE WHEN YOU ARE
DONE EDITING ALL PROPERTIES.
9. Click the Description field control to select it. Press and hold the Shift key and then click the empty space below the
Description field control to select the cell. Both the Description field control and cell below should be outlined in orange.
Review Database Fields 2. Click the Guardian Information tab and then click the Emergency Information tab.
Each field on each tab is an example of the type of information that could be contained in a database table.
Customer IDs, serial numbers, or product IDs usually make good primary keys.
Each table should have a primary key, and some tables might have two or more.
12. In the Navigation Pane, right-click the
Employees Contact Query to select it.
15. In the Navigation Pane, click the Marketing
Employees query to select it.
10. Click the Close button on the Non-Stamford
Employees tab to close the query
14. CLOSE the New
Employees table.
8. CLOSE the Minute
Employees table.
Commands Results
End Moves cursor to the last field in the current record
16. CLOSE
Excel.
SOFTWARE ORIENTATION Navigation Pane
Expand or collapse the group
2. Click the
File tab.
Take Note You can also add a Total row to queries open in Datasheet view and to a split form open in
Form view. You cannot add a Total row to a report, but you can use aggregate functions in reports using other methods.
Field Property Use This Field Property To Field Size Set the maximum size for data stored as a Short Text, Number, or AutoNumber data type.
Format Customize the way the field appears when displayed or printed. Decimal Places Specify the number of decimal places to use when displaying numbers.
Define Control Tab Order
GET READY. USE the Wingtip Inventory-final database that is open from the previous exercise.
"An error occurred trying to import file," the import operation failed. If the data imports and you find just a few missing values, you can add them directly to the table.
However, if you find that entire columns or a large number of values are either missing or were not imported properly, use Access Help to troubleshoot the results and correct the problem in the source file. After you have corrected all known problems, repeat the import operation.
If you prefer to use the mouse, you can move among records by clicking the navigation buttons, which you will do in a later exercise.
However, in this exercise, you use the keyboard to navigate among records.
Take Note You cannot apply visual formats to controls bound to Attachment and OLE Object fields.
However, you can change the format of the label associated with the control
14. Click the Close button to close the Used Cars Sold Query and save the changes when prompted.
If another dialog box appears informing you that another user may have changed the data, click Yes
Take Note If you plan to append the data to an existing table, ensure that the name of each column exactly matches the name of the corresponding field.
If the name of a column is different from the name of the corresponding field in the table, the import operation will fail. To see the names of the fields, open the table in Design view in Access
Troubleshooting If Access displays a message warning you that some of your data may not be displayed, this means the controls on the label are too wide for the allotted space.
If this happens, try reducing the size of the controls in Design view so that they fit in the space available for a single label or try reducing the page margins using Page Setup.
a Message Bar appears warning you that certain content has been disabled.
If you know you can trust the database, click Enable Content.
9. Position the pointer under the Price control and then click to create the text box control Stockphoto Bound control dragged from the Field List pane as shown in
If you need to move the control, select it and move the mouse pointer over the selection border until it appears as a four-sided pointer, and then click and drag it to the appropriate location.
you open the report.
If you want to open the report and see the filter already applied, set the Filter On Load property setting to Yes.
By default, Access searches the field that was selected when you opened the Find and Replace dialog box.
If you want to search a different field, select the field while the dialog box is open; you do not have to close it first. In this exercise, you find and replace table data.
Sorting allows you to order records. For example, an office contact list that displays employees in alphabetical order by last name would help the user find information for a particular employee quickly.
If you wanted to view only the records of employees in a particular department, you could create a filter to display only those records.
5. Click the Close button on
Image Info to close the form.
16. CLOSE the
Inventory table.
If you do not have a field in an existing database that you think will make a good primary key, you can use a field with the AutoNumber data type.
It does not contain factual information such as a telephone number) about a record, and it is not likely to change. In this exercise, you define a primary key.
18. Click Next. The fourth Label Wizard dialog box appears.
It provides you with Sort by options.
9. Click and drag the ID control out of the layout.
It will not move out of the layout.
Use a Template to Create a Database
LAUNCH Access to display the startup screen.
11. Select a location for the file and then click Publish.
Leave the default name Part-Time Employees.pdf.)
14. SELECT THE FIELD WHOSE VALUE YOU WANT TO STAORE IN THE TABLE FIELD
MOST LIKELY THIS SHOULD BE THE MATCHING FIELD YOU SELECTED IN STEP 6.
4. In the Inventory field list, double-click Year,
Make, Model, AskingPrice, and TradeInValue.
17. Click Yes on the dialog box asking you if you want to permanently delete the
Marketing Employees query. The query is now permanently deleted from the database
14. In the blank area of the dialog box, type the following:
Markup in Euros: AskingPrice] - [TradeInValue]) * .92
Click to access
Microsoft
6. Click the Display Navigation Pane check box to remove the check mark, as shown in and then click OK. A
Microsoft Access message box appears saying you need to close and reopen the database for the changes to take effect
2. Double-click the chart.
Microsoft Graph opens.
11. Change to Design view and remove the Model,
Mileage, AskingPrice, and Trade In Valuefields from the design grid. The Year and Make fields should be the only ones remaining on the grid.
4. In the Inventory field list, double-click Year, Make, Model,
Mileage, asking price, and TradeInValue to add them to the design grid.
5. Change the number for
Mobile Phone field to 81817815
4. In the list of table fields, double-click Year, Make,
Model, and Asking Price to add those fields to the design grid.
Create a Navigation Form 1. On the Create tab, in the Forms group, click the
Navigation button and then click Horizontal Tabs. A new Navigation form appears in Layout view
4. Use the File Open dialog box to locate the
New_Employees spreadsheet file and then click Open.
5. Click
Next again.
6. Click the
Next button.
8. Click the
Next button.
5. Click
Next.
6. Windows (default) should be selected. Click OK.
Notepad opens and the new file with exported data is displayed
10. On the Arrange tab, in the Sizing & Ordering group, click the Size/Space button. In the Size category on the menu that appears, click To Fit.
Notice all the field controls and labels are slightly resized in the layout based on the best fit of the text within the In Production field control and label since the layout forces all fields to conform to this action.
Freeze/Unfreeze and Hide/Unhide Fields 7. Press the Right Arrow key several times until the Last Name field scrolls off from view.
Notice how the Last Name field moved with the other fields when the Right Arrow key was pressed several times.
9. Click the Shutter Bar Open/Close button to collapse the Navigation Pane.
Notice it is not entirely hidden, as shown in Figure 1-9.
7. Right-click any of the purple Data Series columns in the chart to display the shortcut menu.
Notice that Series "SumOfIncome" is displayed in the Chart Objects list box.
14. Double-click Used Cars Sold: Table in the Navigation Pane to open it.
Notice that all the records for cars sold before March 31, 2016, have been deleted,
6. OPEN Fabrikam Inc-final_be.
Notice that it contains only the tables for the database.
Freeze/Unfreeze and Hide/Unhide Fields 3. Select Freeze Fields from the menu that appears.
Notice that the Last Name field moves to the first field position in the table.
14. Scroll down to the bottom of the Book Sales table.
Notice that the Lookup to Author Contact Information field was added.
7. Click the Move up arrow at the end of the Group On Sales Person Last Name row.
Notice that the Sales Person Last Name group is now the top level group in the report.
15. Double-click Inventory: Table in the Navigation Pane to open it.
Notice that the asking price for all 2012 cars has been increased by $500.
Enter, Edit, and Delete Records 2. Type 21905 and then press Tab.
Notice that the asterisk has changed to a pencil icon, indicating that the record is being edited.
17. Click the Show data table check box to insert a check mark.
Notice that the datasheet is added to the bottom of the chart.
9. Click the 3D Column Chart button—the second icon in the first row.
Notice that the description of the chart type is displayed on the right,
10. Select Room Name from the select field list.
Notice that the field was sorted in ascending order by default and a line was added describing the sort.
The Ribbon and dialog box launcher 2. Click the Create tab to make it the active tab.
Notice that the groups of commands change.
10. Click the Show legend check box to remove the check mark.
Notice that the legend is removed from the chart.
Creating a Table from Another Table 5. Double-click Copy Of Comments to open the new table.
Notice that the structure of the new table is the same as the table from which it was copied, as shown
14. Click the Value check box to insert a check mark.
Notice that values are added to the columns in the chart.
4. Click Import the source data into a new table in the current database and then click OK. The Import Text Wizard appears,
Notice the Delimited option button is selected and the data preview of the file contains fields separated by commas. In this case, you will keep the delimited selection.
20. OPEN the Fabrikam Inc-final database.
Notice the Navigation Pane is displayed and the form is not.
Enter, Edit, and Delete Records 1. The insertion point should be positioned in the first field of the new, blank row at the bottom of the datasheet.
Notice the asterisk in the Record Selector box, which indicates that this is a new record, ready for data.
25. On the Arrange tab, in the Sizing & Ordering group, click the Align button. In the menu that appears, click the To Grid button.
Notice the control and labels slightly move. The upper-left corners of all the labels are now aligned to their nearest grid points.
12. In the Data tab, click the down arrow on the Control Source row and then select the In Production field, as shown in Click In Production.
Notice the control now displays the field name In Production, which means that it is now bound to the control.
Use the Ribbon 1. Click the Home tab to make it active. As shown in the Ribbon is divided into groups of commands.
Notice the dialog box launcher in the lower-right corner of the Clipboard group.
16. Select 12 in the Size: menu and then click OK.
Notice the font size of the legend text has increased.
9. Click the Next Page button on the page navigation bar until you reach page 4.
Notice the relationship diagram included in the report,
7. On the Print Preview tab, in the Zoom group, click the Two Pages button. The first two pages of the report are displayed on the screen,
Notice the second page has no report data on it. You need to correct these issues in the Report Design view to ensure the report print layout is appropriate before printing.
6. Click OK in the Format Chart Area dialog box.
Notice the shading style of the chart background changes to your selection.
15. Change to Report view to see the changes you've made.
Notice the text formatting changes as well as the currency formats for the Investment and Price fields.
Create a Multivalued Lookup Field 2. Click Next to accept the default setting (I want the lookup field to get the values from another table or query) and display the next screen in the Lookup Wizard.
Notice you have a choice of two tables to provide the values for the lookup field you are creating. The first table, Employees, should already be selected for you.
13. To make it easier to see which cars are selected, select the Flag column, click Selection in the Sort & Filter group, and then select Does Not Equal 0.
Now you can see all high-mileage cars that may be overpriced (since there are no low mileage cars that are underpriced).
Data Type Example Description
Number Age: 19
11. In the Position section of the Page Numbers dialog box, select the Bottom of Page Footer] option and then click
OK. If necessary, scroll to the bottom of the report window. The Page number control is inserted in the Page Footer section near the bottom of the report.
How to: Create a new database using a template
OPEN ACCESS TO DISPLAY THE LIST OF SAMPLE TEMPLATE OR IF ACCESS IS ALREADY OPEN, CLICK THE FILE TAB TO OPEN THE BACKSTAGE VIEW AND CLICK THE NEW BUTTON
HOW TO: CREATE A RELATIONSHIP USING THE LOOKUP WIZARD
OPEN THE TABLE CONTAINING THE FOREGIEN KEY FIELD
Save a Database as a Template1.
OPEN the Graphic Art database from the data files for this lesson.
4. SELECT THE I WANT THE LOOKUP FIELD TO GET THE VALUES FROM ANOTHER TABLE
OR QUERY RADIO BUTTON AND CLICK NEXT.
16. CHECK THE ENABLE DATA INTEGRITY CHECK BOX AND SELECT EITHER THE CASSCADE DELETE
OR THE RESTRICT DELETE RADIO BUTTONIF APPROPRIATE
2. Click the All
Object Types tab.3. Click the Tables tab.
Troubleshooting If the In Production text within the label is partially hidden, change to Design view and select the In Production control and label.
On the Arrange tab, in the Position group, click the Control Margins button, select None from the menu, and then Save the report.
13. All the controls and labels should still be selected.
On the Arrange tab, in the Sizing & Ordering group, click the Size/Space button and then select To Fit from the menu that appears. Notice that all the field controls independently change their width to best fit their text since they are no longer part of a layout..
Use Arithmetic and Logical Operators to Group Data 1.
On the Create tab, in the Queries group, click Query Design.
STEP BY STEP Create an Append Query 1.
On the Create tab, in the Queries group, click the Query Design button.
2. Click the File tab and then click Info, if necessary.
On the Info menu that appears, click the View and edit database properties link. The Fabrikam-final.accdb Properties dialog box appears
3. On the Create tab, in the Forms group, click the More Forms button.
On the menu that appears, click the Multiple Items button. Access creates the form and displays it in Layout view
7. If the search bar does not appear at the top of the Navigation Pane, right-click the All Access Objects header of the Navigation Pane.
On the shortcut menu, click Search Bar. A search bar is now displayed at the top of the Navigation Pane.
Take Note The process for adding a background image to a form or a report is the same.
Once shown how to add a background image to a form, you can add a background image to a report in the same manner.
Take Note The process for adding or removing a control to and from a form and a report is the same.
Once shown how to add/remove a control to/from a report, you can add/remove a similar control to a form in the same manner
Take Note The process for arranging the control alignment, size, and position on a report is the same as for a form.
Once shown how to arrange these for a form, you can arrange them for a report in the same manner
Take Note The process for setting report control properties is the same for a form.
Once shown how to set a report control property, you can set a form control property in the same manner.
SOFTWARE ORIENTATION Navigation Pane
Open/Close button
SOFTWARE ORIENTATION Navigation Pane
Opens Navigation
• Open Exclusive:
Opens so that the database is only available to you. Others will receive a message that the file is already in use.
Backstage view • Feedback:
Opens the Windows Feedback dialog box, which allows you to provide feedback to
Order By On Load Automatically apply the sort criteria in the Order By property by setting to Yes) when the table is opened in Datasheet view.
Order By On Provide an alternate method to the Order By On Load property by automatically apply
10. Click the Close button on Photo Label to close the form.
PAUSE. LEAVE the database open to use in the next exercise
9. OPEN the Fabrikam Inc-final database. Notice that the Navigation
Pane is not visible and the Showroom Contact Form is displayed at startup,
Text Format Choose Rich Text to store text as HTML and allow rich formatting. Choose Plain Text to store only text. Text Align Specify the default alignment of text within a field or control.
Precision Specify the total number of digits allowed, including those both to the right and the left of the decimal point when decimal Field Size property is set using Number data type).
Define and Modify a Multifield Primary Key 6. Click the Row Selector box beside the Order ID row.
Press and hold the Ctrl key and then click the Row Selector box beside the Customer ID row. Both fields should be selected.
12. Click the
Price control.
SELECT [ProductID ,
ProductName] FROM
6. Double-click the In
Production field to add it to the form.
11. On the Design contextual tab, in the Tools group, click the
Property Sheet button. The Property Sheet appears.
23. CLOSE the
Property Sheet.
3. Use the File Open dialog box to locate the Benefit_
Providers spreadsheet file and then click Open.
20. Select the Calculated
Query in the Navigation Pane.
8. Click the Finish button to accept the default selection and complete the query. The Employees Contact
Query is displayed,. The results show all of the records, but show only the five fields that you specified in the Query Wizard.
7. Click the Close button on the Industry Friends
QueryFilter1 tab to close the query
6.SELECT THE MATCHING FIELD FROM THE LIST OF AVAIABLE FIELDS AND CLICK THE MOVE SINGLE FIELD BUTTON TO MOVE FIELD INTOT THE SELECTED FIELDS WINDOW
REMEBR THAT THE MATCHING FIELD IS THE PRIMARY LKEY IN THE TABLE.
4. ENTER THE NEW VALUE
REPEATE STEPS THREE AND FOUR AS DESIRED
Table 4-2 lists the available field properties and what they control.
Remember that the properties available depend on the field's data type.
2. On the Create tab, in the Reports group, click the
Report Design button. A new blank report is displayed in Design view Page header section Detail section Add Existing Fields button
3. In the Paste As dialog box, replace the name of the report in the
Report Name field with Sales by Salesperson and then click OK.
13. SAVE the report and then change to
Report view to view your changes.
Compact and Repair a Database 1. Click the File tab and then click Info if the Info menu doesn't already appear).
Select Compact & Repair Database on the Info menu that appears, as shown in Click Compact & Repair Database. Access compacts and repairs the database.
3. Click the down arrow in the Book Title column of the Total row.
Select Count from the menu. The number of records in the column is counted, and the number 11 is displayed.
Enter, Edit, and Delete Records
Select Delete Record from the menu (. Click Delete Record.
5. Notice the three options you have when importing data
Select the option button for Import the source data into a new table in the current database and then click OK. The Import Spreadsheet Wizard appears
You can also filter by selection in a report. If you want to view only the reservations for 12/13/16, select that date in the Check-in field and then click the
Selection button. That date will appear in the menu, so that you can choose Equals 12/13/16, Does Not Equal 12/13/16, and so on.
LeadTime: [RequiredDate
ShippedDate]
Data Type Example Description
Short Text Last Name: D'Amato
3. Click Close to close the
Show Table dialog box.
Using a Template to Create a Database Access offers a variety of templates to help get you started.
Some templates are immediately available for your use because they are built into Access whereas others can be easily downloaded from the Internet.
There are built-in and online templates are available that you can use to track issues, manage contacts, and keep a record of expenses.
Some templates contain a few sample records to help demonstrate their use.
Modifying Table Properties You can set properties that control the appearance or behavior characteristics for an entire table in the table's property sheet.
Sometimes it is necessary to describe the purpose of a table by modifying the table's Description property since others who view your table may require more information about its purpose.
Apply a Filter 15. On the Home tab, in the
Sort & Filter group, click the Selection button.
2. Click the Group On Sales Person Last Name row in the Group,
Sort, and Total pane, and then click the More button to view the available grouping options.
Sorting Data within a Table To sort data means to arrange it alphabetically, numerically, or chronologically.
Sorting within a table displays all the records in the table in the order that you select.
9. Click anywhere in the
Stamford Employees field list.
Data Type Example Description
Stores numeric data that can be used in mathematical calculations.
Take Note Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords do not mix these elements.
Strong password: W5!dk8@R. Weak password: CAR381. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.
14. CLOSE the Samples Given
Subform and the Doctors form
Create Fields 5. Click the More Fields button in the Add & Delete group on the
Table Tools Fields contextual tab. The More Fields menu appears
10. Place the insertion point in the Criteria row of the asking price field and then press Shift+F2 to display the Zoom dialog box.
Take Note The Zoom dialog box is used to create a larger workspace to help you focus on expressions and limit typing errors.
5. Under Available Fields, double-click Last Name, First Name, E-mail Address, Mobile Phone, and Position to move them to the Selected Fields box.
Take Note To remove a field from the Selected Fields box, double-click the field. This moves it back to the Available Fields box.
11. Rename the SingleOneColumnRight Labels form to Doctors Blank Form.
Take Note You can also add fields from multiple tables to your Navigation form using the Field List pane in both Design and Layout views to allow for even greater customization.
Limiting the choices a user can make to enter into a field helps protect the validity of your data.
Take Note You can also create lookup columns that allow for a single selection of a value.
2. Click the File tab and then click Options.
The Access Options dialog box appears.
6. Change to Design view. Notice that there is a Region Borough) Header for that group and a Sales Person Last Name header for that group.
The Company Name and Contract Amount fields are arranged in the Detail section.
Define Table Relationships 5. Click the Customer ID primary key field in the Customers table, drag it to the Customer ID field of the Order Summary table, and then release the mouse button.
The Customer ID field represents the common field between the two tables. The Customer ID field of the Order Summary table is now set as the foreign key.
For example, in a Customers table and an Orders table, one customer could have many orders.
The Customer ID would be the primary key in the Customers table the one) and the foreign key in the Orders table the many).
The Ribbon and dialog box launcher 6. Click the dialog box launcher in the lower-right corner of the Text Formatting group.
The Datasheet Formatting dialog box appears, as shown
10. On the Arrange tab, in the Merge/Split group, click the Merge button.
The Description field control and cell have now merged into one,
4. On the Format tab, in the Font group, click the Font Color button and then select Black from the Theme Colors menu.
The Description label displays in black. Change the font color to black for the In Stock, Price, and In Production labels.
Find and Replace Data 2. On the Home tab, in the Find group, click the Find button.
The Find and Replace dialog box appears with the Find tab displayed.
12. If the total row does not appear, click Totals on the Records tab of the Home tab, and then select Sum from the Flag fields Total drop down.
The Flag expression field has a 0 where the expression is false and a -1 where the expression is true. At the bottom of the Flag column, you see that there are 11 cars that fulfill the expression,
7. In the How do you want to view your data? box, click by Doctors.
The Form with subform(s) option button should be selected, and the Form Wizard should look like
2. On the External Data tab, in the Import & Link group, click the Access button.
The Get External Data - Access Database dialog box appears.
3. On the Design tab, in the Grouping & Totals group, click the Group & Sort button.
The Group, Sort, and Total pane appears at the bottom of the screen
8. On the Report Layout Tools Design contextual tab, in the Grouping & Totals group, click the Group & Sort button.
The Group, Sort, and Total pane appears at the bottom of the screen, as shown in Figure 6-11.
14. In the Grouping & Totals group on the Ribbon, click the Group & Sort button.
The Group, Total, and Sort pane is removed.
4. In the Get External Data - Access Database dialog box, click OK.
The Import Objects dialog box appears. Notice the Tables object tab is selected by default.
controls to select them.
The In Production field control should already be selected.
8. On the Arrange tab, in the Move group, click the Move Down button once.
The In Stock field control and label move to the bottom of the layout.
6. In the Expression Categories list, scroll down and double-click In Stock.
The In Stock field is inserted in the expression box
9. On the Format tab, in the Font group, click the Center button.
The In Stock text is centered in the control box.
7. Click OK to close the Documenter dialog box.
The Object Definition report appears in Print Preview.
1. Click the Open Other Files command on the left side of the Access startup screen.
The Open screen appears,
12. OPEN the database in Exclusive mode again.
The Password Required dialog box appears.
Creating a Table from Another Table 2. Right-click in a blank area of the Navigation Pane and select Paste from the shortcut menu.
The Paste Table As dialog box appears, as shown Notice the default name, Copy Of Comments, in the Table Name box.
Define a Primary Key 4. On the Design tab, in the Tools group, click the Primary Key button.
The Primary Key button is highlighted. A key icon appears on the Order ID row to designate the field as a primary key
5. Click the Options button.
The Print Table Definition dialog box appears,6. Click OK to close the Print Table Definition dialog box.
2. Click the Print button.
The Print dialog box appears.
1. Click the File tab and then click the Print tab on the menu.
The Print tab displays available options.
16. On the Design tab, in the Show/Hide group, click the Property Sheet button.
The Property Sheet pane for the Markup in Euros expression is displayed.
10. Click PDF or XPS on the External Data tab.
The Publish as PDF or XPS dialog box appears.
Freeze/Unfreeze and Hide/Unhide Fields 4. Click the Restore Down button on the application window.
The Restore Down button now becomes the Maximize button
4. Click the File tab and then click Save.
The Save As dialog box appears.
Save a Table 6. Right-click on the Table1 tab to display the shortcut menu and then click Save.
The Save As dialog box appears.
2. In the Database File Types category, click Access 2002-2003 Database and then click the Save As button.
The Save As dialog box appears. Notice that Microsoft Access Database (2002-2003) is displayed in the Save as type: box.
2. Click the File tab and then click Save As.
The Save As menu appears
3. Click Simple Query Wizard and then click OK.
The Simple Query Wizard appears
Removing a Filter After applying a filter, you may need to return to records not displayed by the filter.
The Toggle Filter button lets you toggle between viewing the filtered records and viewing the table without the filter.
Freeze/Unfreeze and Hide/Unhide Fields 10. Click the More button and then select Unhide Fields.
The Unhide Columns dialog box appears. Notice the check mark is missing from the Customer ID check box, signifying that it is hidden.
14. On the File tab, on the Info menu, click the Decrypt Database button. If you hadn't opened the database in Exclusive mode, you would get a message prompting you to do so.)
The Unset Database Password dialog box appears.
5. Press the Delete key on the keyboard and the Trim column is deleted.
The asking price field moves to the left to replace the Trim column.
3. Click the Show all tables link, and then the expand button to the left of the table name.
The available fields display from the Photo Exhibit table
3. Click the By Row button on the toolbar.
The chart is changed to show all the expenses together and all the income together,
3. On the Home tab, in the Sort & Filter group, click the Ascending button.
The column is sorted in ascending alphabetic order.
Define and Modify a Multifield Primary Key 3. On the Design tab, in the Tools group, click the Primary Key button. A key icon should be displayed beside both of the two selected fields.
The combination of data from these two fields do not make a sensible composite key, and this designation will be removed.
8. On the Arrange tab, in the Table group, click the Stacked button.
The controls andlabels are arranged in a 2-column stacked layout.
9. On the Design tab, in the Grouping & Totals group, click the Hide Details button.
The data in the Contract Amount field is hidden.
5. On the Home tab, in the Records group, click the Refresh All button.
The data in the report and in the chart are updated.
4. Type 1 into the Custom Filter dialog box and then click OK.
The data is filtered to show only the rooms on the first floor.
Sort Data within a Table 3. On the Home tab, in the Sort & Filter group, click the Descending button.
The data is sorted from largest to smallest, as shown The data is sorted and an arrow is inserted in the header row indicating that the data is displayed in descending sort order
3. In the Open dialog box, click the Open button.
The existing database opens.
5. Double-click Room ID.
The field is inserted onto the design grid.
16. Click the Next button.
The fifth Report Wizard dialog box appears.
Use the Report Wizard 1. On the Create tab, in the Reports group, click the Report Wizard button.
The first screen of the Report Wizard appears.
2. Click and drag the Doctors form object from the Navigation Pane to the Add New tab near the top of the form.
The form tab is renamed Doctors and all the Doctors form's controls appear. A new Add New] tab appears next to the Doctors tab.
5. In the Page Size group, click the Margins button. On the Margins menu that appears, click the Narrow option.
The form's field borders have been adjusted and should now be within the right margin. The print layout now appears complete.
11. Click the Show legend check box again to insert the check mark.
The legend is displayed in the preview.
Modify Table Relationships 6. Click the Enforce Referential Integrity box and then click OK.
The line appears thicker, with the number 1 beside the first table and the infinity symbol ∞) beside the second.
You can also sort records on multiple fields. When you are using multiple fields, determine in which order you want them to be sorted.
The primary sort field is called the outermost field. A secondary sort field is called an innermost field.
Set Data Validation Rules 7. Select the number 1900 and then replace it by typing 2016.
The property boxes should look like those shown
13. Right-click the Inventory Query document tab and then choose Datasheet View to display the update query results,
The query contains fields that you don't update, so Access does not display those fields in the results.
10. In the Navigation Pane, double-click the Employees Contact Query to run it.
The query results are displayed in Datasheet view.
18. On the Design tab, in the Results group, click Run.
The query with the new calculated Markup and Markup in Euros fields is displayed,
8. On the Home tab, in the Views group, click the lower half of the View button and then select Print Preview from the menu.
The report is displayed in Print Preview. The Price field header and data in the last column exceed the width of the right margin.
12. On the Print Preview tab, in the Page Layout group, click the Portrait button.
The report is displayed in portrait orientation again.
5. Click the Add a group button again and then select Sales Person Last Name from the Group On menu.
The report is now also grouped on the Sales Person Last Name field.
5. Click the Toggle Filter button on the Ribbon.
The report returns to its unfiltered state.
Sort Data within a Table 7. On the Home tab, in the Sort & Filter group, click the Remove Sort button.
The sort is removed from both the First Name and Last Name fields.
3. On the Home tab, in the Sort & Filter group, click the Remove Sort button.
The sort order is removed from the Referred By field.
2. On the Tables tab, click Inventory, click Add, and then click Close.
The table field list appears as a window in the upper section of the query design grid.
8. In the Table Name box, click the down arrow and then click Used Cars Sold.
This is the table you want to append to. The Current Database option button should be selected.
11. Click the Next button to display the final screen, and then click the Finish button to accept the default settings in this screen and display the query
This query shows the name, position, and email address of the employee who referred each industry friend.
5. Click Next to display the next screen in the Find Duplicates Query Wizard.
This screen asks if you want to show other fields of the duplicate record besides just the ones with the duplicate data.
Set a Field Property in Datasheet View and Design View 3. Click the Required check box in the Field Validation group on the Table Tools Fields contextual tab.
This setting determines that a value must be entered in the Location field for all records in the table.
Create a Multivalued Lookup Field 10. In the What label would you like for your lookup field? box, type Coordinator.
This will create a new label named Coordinator for your column.
Take Note You should save your files in a separate directory from where your data files are stored.
This will ensure that you don't overwrite the original data files with your updated files.
Create a Multivalued Lookup Field 7. Click the down arrow in the first box and then click Last Name.
This will sort the Lookup column in alphabetical order by Last Name.
Take Note If you don't want to link to the entire worksheet, define a range in Excel that includes only the cells to which you want to link.
To create a named range, select the cells, and then right-click and choose Define Name. In the New Name dialog box, type a name for the range and then click OK.
Take Note As an alternative to printing labels, you can print addresses directly onto envelopes.
To do this, you will need to create a custom label instead of a predefined label and set the Label Type setting to Sheet Feed.
Take Note You can import only one Excel worksheet at a time during an import operation.
To import data from multiple worksheets, repeat the import operation for each worksheet.
Modifying Table Relationships A table relationship is represented by the line that connects the tables in the Relationships window.
To modify the relationship, you can double-click the line to display the Edit Relationships dialog box or delete the line to delete the relationship.
In Design view, select the rows you want to designate as primary keys and then click the Primary Key button.
To remove multiple primary keys, select the rows, and then click the Primary Key button.
7. Type Inventory Summary by
Toy and then press the Enter key.
Use the Keyboard to Navigate among Records3. Click the Save As command. The Save As dialog box appears.
Type Fourth Coffee-final in the File name box. Find the location where you will save the solution files for this lesson and then click Save.
Use Navigation Buttons 4. Click the Search box to position the insertion point.
Type into the Search box. Notice that the selection moves to the first occurrence of the word
15. Type Sales and then click OK Insert and Delete Rows in Design View GET READY.
USE the Lucerne Publishing-final database and the Sales table that are open from the previous exercise.
Backstage view • Print:
Use this option to quick-print to a printer, open a dialog box from which to choose print options, or preview your document before printing.
Once a primary key is defined, you can use it in other tables to refer back to the table with the primary key.
When a primary key from one table is used in another table, it is called the foreign key.
16. Go to Report view to view your changes.
When finished, change back to Design view.
Note that the purpose of this button changes accordingly—when the records are filtered, the button is used to remove the filter, and when the filter is removed the button is used to apply the filter.
When you are finished using the filter, you can permanently remove it. In this exercise, you permanently remove the filter you previously applied.
Take Note Unless you choose a different folder, Access uses the following default location in
Windows Server 2008/2012, and Windows 7 and later to store your databases: c:\Users\<user name>\Documents
5. On the Format tab, in the Background group, click the Background Image button, click the Browse command to locate the
Winter Theme Background.jpg image file, and then click OK. Notice the form's background image is now that of the image
In Datasheet view, you can navigate among records using the up, down, left, and right arrow keys to move to the field you want.
You can also use the Tab key to move from field to field in a record and from the last field in a record to the first field of the next record.
The Edit Relationships dialog box allows you to change a table relationship.
You can change the tables on either side of the relationship or the fields on either side. You can also perform actions like enforcing referential integrity and choosing cascade options.
Take Note In the Get External Data - Excel Spreadsheet dialog box, Access also allows you to append a copy of the records to an existing table in the database.
You can choose Append a copy of the records to the table and then select an available table from the drop-down list.
14. Click the Next button. The fourth Report Wizard dialog box appears.
You can choose from three different layouts for your report as well as two different orientations.
CREATING A TABLE Another way to create a table is to copy the structure of an existing table and then paste it into the database.
You can copy a database object and paste it into the same database or into a different database that is open in another instance of Access.
Creating Multivalued Lookup Fields In Office Access 2016, you can create a multivalued lookup field that allows you to select more than one choice from a list—without having to create a more advanced database design.
You can create a field that holds multiple values, such as a list of employees that you have assigned to a particular event.
ENTERING, EDITING, AND DELETING RECORDS Keeping a database up-to-date and useful is an ongoing process.
You can easily enter data by positioning the insertion point in the table cell where you want to add data and begin typing.
ENTERING, EDITING, AND DELETING RECORDS Keeping a database up-to-date and useful is an ongoing process.
You can easily enter data by positioning the insertion point in the table cell where you want to add data and begin typing. To insert a new record, select any record in the table and then click the New button on the Home tab
MODIFYING A DATABASE TABLE After a table has been created, you may need to modify it.
You can make many changes to a table—or other database object—using its property sheet.
Backstage view • Save As: Use this option to save the current database object such as a table, query, form, or report) as a new object or save the database in another format that is compatible with earlier versions of Access.
You can save the database to a document management server for sharing or you can package the database and apply a digital signature.
12. Click the Next button. The third Report Wizard dialog box appears.
You can sort in either ascending or descending order and by up to four fields.
Take Note You cannot attach files to databases created in versions of Access prior to Access 2007.
You cannot share attachments with a database created in these prior versions of Access.
6. Double-click Price to add it to the form.
Your form should look similar to Figure 5-5.
5. Double-click Media to add it to the form.
Your form should look similar to Figure 5-7.
15. Type In Production: and then press Enter.
Your screen should look similar to
23. If Access Help appears on your screen, close it.
Your screen should look similar to
8. Change back to Form View and view the changes.
Your screen should look similar to
6. Click the In Production field control and drag it to the right of the ID field control to place it next to it. The In Production label and field control is now in the column.
Your screen should resemble Figure 8-33. Notice the new additional space automatically created to the right of the other field controls.
Modify Table Properties 6. Press Shift+F2 to open the
Zoom dialog box to provide more space.
11. In the Expression Categories box, double-click TradeInValue;
[Inventory TradeInValue] should appear in the expression and next to the minus sign -)
Reviewing Database Fields Reviewing preexisting database fields can give you an idea of the type of information that you can store in a database. In this exercise, you open
a database that is further along in the process of being developed and view the fields in a form to see what a more advanced database looks like.
Orientation Set the view orientation, according to whether your language is read left-to-right or right-to-left. Description Provide
a description of the table. Default View Set Datasheet as the default view when you open the table.validation Rule Supply an expression that must be true for you to add a record or change a record.
WORKING WITH PRIMARY KEYS As you learned in Lesson 1,
a primary key is a column that uniquely identifies a record or row in a table.
The Open screen The Open dialog box Each time you start Access 2016, you open a new instance, or copy, of Access.You can only open one database at a time in
a single instance of Access.In other words, you cannot start Access, open one database, and then open another database—not without first closing the first database.
Take Note A combo box is also known as a drop-down control, because it is a combination of
a text box with a list box that is hidden until you select the arrow next to the text box and it drops down.
When you create a copy of a table by copying and pasting, you can re-create just the table's structure or both its structure and data. Access also provides you with
a third option that appends data to an existing table. If this option is chosen, Access will attempt to append the table's records to another table you specify.
among these tables allows Access to bring that information back together
again through objects such as reports and queries so that you can display information from several tables at once. It is much easier to create effective reports and queries when you start out with well-defined table relationships.
18. Click the Show data table check box
again to remove the check mark.
Take Note Be aware that changing a data type might cut off some or
all of the data in a field; in some cases, it might remove the data entirely.
24. SAVE and CLOSE
all tables and queries.
3. In the Available Fields box, double-click the Week Name, Sales Rep, Product,
and Quantity fields to move them to the Selected Fields box.
6. Double-click Room Name, Bed Size,
and Rate to add them to the design grid.
2. In the Show Table dialog box, double-click Sales Team
and Used Cars Sold to add them to the design grid.
To delete a table or other database object like a report, form, or query, right-click it in the Navigation Pane
and click Remove or Delete. Or, select the table in the Navigation Pane and then press Delete.
Follow this rule when searching for all wildcard characters except exclamation points !)
and closing brackets ]) where you would simply search for these without any surrounding brackets.
CREATING A TABLE Application Parts were introduced in Access 2010
and consist of predefined templates that you can add to an existing database to help extend its functionality.
Getting Started with Microsoft Access The Access startup screen shown in provides options for opening an existing database,
and creating a new blank database or a new database from a template. This is the default view after starting Access.
3. Change to Report View to view the Report and related subform,
and cycle through the two records in the top subform using the record navigation buttons on the subform.
24. Click any blank space on the design grid
and drag to draw a box around the labels and
In a one-to-one relationship, both tables have a common field with the same data. Each record in the first table can only have one matching record in the second table,
and each record in the second table can have only one matching record in the first table.
controls so they are all selected, or press
and hold the Shift key and then select each label and control.
8. In the Fields box, click Sales Price,
and in the Functions box, click Sum,
SOFTWARE ORIENTATION Records Group, Record Selector Box, and Record Shortcut Menu There are a few ways you can enter record data, delete data from individual fields of records,
and insert and delete entire records, using the Records group, Record Selector box, and commands in the Record shortcut menu
Create a Multivalued Lookup Field 9. Click Next to accept the default selection and to hide the primary key column to ensure only relevant
and meaningful data displays in the lookup column later. The final screen of the Lookup Wizard displays.
Data Type Example Description Stores large amounts of text—up to 1 gigabyte (GB) —but only the first 64,000 characters of text,
and numbers or a combination of both) will be visible on the screen.
Freeze/Unfreeze and Hide/Unhide Fields 5. Press the Right Arrow key to scroll the table's fields to the left,
and stop when you reach the ZIP/Postal Code field. Notice that the Last Name field stays fixed as the other fields scroll.
2. On the Create tab, in the Forms group, click the Form Design button. A new, blank form is created,
and the Field List pane is displayed. If it isn't displayed, click the Add Existing Fields button in the Tools group on the Design tab.)
Use the Control Wizard 1. On the Create tab, in the Forms group, click the Form Design button. A new, blank form is created,
and the Field List pane is displayed. If it isn't, click the Add Existing Fields button in the Tools group on the Ribbon.)
6. CLOSE the report
and the database.
19. CLOSE the report
and the table
6. CLOSE the document
and then CLOSE Word.
16. SAVE the query as Aggregated Query
and then CLOSE the query
10. SAVE the query as Join Query
and then CLOSE the query Expression Description
STOP. CLOSE the database
and then EXIT Access.
Take Note It is often easiest to add and arrange all the bound controls first,
and then add the unbound and calculated controls to complete the design of the report.
12. Right-click the document tab for the query
and then choose Design View.
Define Table Relationships 3. Select Order Summary
and then click Add.
9. On the Home tab, in the Sort & Filter group, click the Advanced button
and then click Advanced Filter/Sort to display the new query filter design grid and tab, as s
7. In the Total cell of the Year field, click the down arrow to display the menu
and then click Count. Notice the cell displays 26, denoting 26 values (years) in the Year column.
7. Click the Field cell in the second column, click the down arrow
and then click Last Name on the drop-down menu.
7. Click the option button for option 2:
and then click OK to create a left outer join.
11. Type Book Sales
and then click OK.
PrimeShip: Ship] * 1.1 Creates a field called PrimeShip,
and then displays shipping charges plus 10 percent in the field.
TotalInventory: [UnitsInStock + UnitsOnOrder] Creates a field called TotalInventory,
and then displays the sum of the values in the UnitsInStock and UnitsOnOrder fields.
Print Table Relationships 3. Click OK to keep the default settings,
and then print the report or click Cancel to not print the report.
4. On the Home tab, in the Sort & Filter group, click the Advanced button,
and then select Advanced Filter/Sort from the menu. An Industry Friends QueryFilter1 tab appears,
Remove a Filter 3. On the Home tab, in the Sort & Filter group, click the Advanced button,
and then select Clear All Filters from the menu.
Create the Print Layout 1. Right-click the Corporate Sales report in the Navigation Pane
and then select Copy from the shortcut menu that appears.
9. Click the down arrow on the Data Type column
and then select Currency from the menu.
Find and Replace Data 6. Click the down arrow beside the Look in menu,
and then select Current document if it is not already selected.
2. Click Browse, navigate to the data files for this lesson,
and then select Information
A new record is added to the end of the table. Select existing data to edit or delete it.
and then select New Record from the shortcut menu. A new record is added to the end of the table.
Create Fields 7. Click the Status field drop-down box button to view the available options,
and then select Not Started (
14. With the controls and labels still selected, on the Arrange tab, in the Sizing & Ordering group, click the Align button
and then select Right from the menu that appears. The labels are right-aligned to the controls.
2. Position the mouse pointer on the design grid and drag down
and to the right to create and position a control the size Reposition as necessary to get it to match the figure.
In this exercise, you modify the Validation Rule and Validation Text properties for the End Date field. Data can be validated in several ways,
and you will often use multiple methods to define a validation rule. Each of the following can be used to ensure that your users enter data properly:
Take Note At this point, you could import data from other sources, such as an Excel spreadsheet or
another Access database, to populate this database. You simply create your new database as you just did, and then use the import commands on the External Data tab
15. Click and drag the ID field from the Field List pane to
any blank spot on the design grid above the Description field.
1. On the Create tab, in the Forms group, click the Form Wizard button. The Form Wizard
appears (see Figure 5-8).
Take Note Many of the aggregated functions work only on data fields set to specific data types. For example, if you
are in a column that only displays text values, some functions—such as Sum or Average— are not relevant, and are therefore not available.
Take Note In the expression, you will notice a combination of operators. The And and Or operators
are logical operators, whereas the - is an arithmetic operator, and the < and > signs are comparison operators.
Define Table Relationships 4. Click Close. The Customer table and Order Summary table
are represented in Relationships view.
Modify Table Relationships 2. Select Delete. A message appears asking if you
are sure you want to delete the relationship. Click Yes. The line disappears.
Take Note Simply click the control label and/or the control to view an orange box
around them to resize accordingly. Shift+click the Control label and the control to select all of them to move them.
9. On the Arrange tab, in the Table group, click the Tabular button. The controls and labels are
arranged back in a tabular layout. The tabular format indents the position of the columns by default.
9. Click the Data Type down
arrow and then click Short Text
Allow Zero Length 3. Click the down
arrow to display the menu.
2. On the Database Tools tab, in the Relationships group, click the Relationships button to display the table relationship,
as shown in The Employees table has a defined relationship with the Industry Friends table as indicated by the relationship line connecting the two tables.
6. Click Date Sold and then click Next. The next screen appears,
asking how you want to group your Date/Time column information.
Define Input Masks for Fields 3. Click the Input Mask Wizard button. A message box appears
asking if you want to save the table now.
7. Click Month and then click Next The next screen appears,
asking you what number you want calculated for each column and row intersection.
6. Release the mouse button. The first Chart Wizard dialog box appears
asking you which table or query you would like to use to create your chart.
Field List pane with expand button and
available fields Fields inserted in Design view
12. On the Arrange tab in the Rows & Columns group, click the Select Row button. Both the In Stock label and field control should
be selected as well as an empty column created when we dragged the In Production field control to the right of the ID field control.
23. Change to Report View and see that the button has
been changed and the rows are now separated by alternate colors.
time you press it. Notice that the tab order
begins with the Description field, moves
12. Press the Spacebar to insert a
blank space between fields.
7. Click the Next record button on the record navigator at the
bottom of the form to see the second record in the form filter results.
Database Essentials
box
4. Click the >> button to move all the fields from the Available Fields
box to the Selected Fields box and then click Next.
Take Note You can attach a maximum total of 2 GB of data,
but each individual file cannot exceed 256 MB in size.
You can set a few of the available field properties in Datasheet view, but to access all of the available field properties, you must open the table in Design view. For example, you can modify the Field Size property in both Datasheet and Design views,
but you can only modify the Text Align property—which allows you to specify the default alignment of text within a field—in Design view.
4. On the Create tab, in the Queries group, click the Query Wizard
button to display the New Query dialog box.
6. In the Close Preview group, click the Close Print Preview
button to return to the Access database screen.
8. Click and hold the Press and Hold to View Sample
button to see a preview of the chart.
to the In Stock field, the Price field, the In Production field, and then the Close Form
button. The tab order then continues to the next record in sequence and through the same fields as before.
You can add input masks to table fields by running the Input Mask Wizard or
by manually entering masks in the Input Mask field property.
Additionally, you can use the Save Object As command in the Backstage view to create a duplicate of a database object like a table, query, or report)
by specifying an alternate name. You can even save objects in other formats such as the Adobe Portable
7. In the Expression Elements
category, click Operators.
Freezing/Unfreezing and Hiding/Unhiding Fields Sometimes you may need to
change the view of a table's data to more efficiently find the information you are looking for.
Take Note When you try to save the report, you may see a dialog box that says, "The object is locked. Any
changes you make will be discarded when the form is closed." Click OK whenever you see this dialog box throughout this lesson.
In the Properties group, select the Field Size property box to define the text length for a field, which limits the number of
characters allowed for input. You can also select other field properties like Default Value, and Name & Caption.
Take Note Microsoft Graph has its own Help system. To access it, double-click a
chart to launch Microsoft Graph and choose Microsoft Graph Help from the Help menu or press F1.
4. Select the Showroom Contact
check box in the Tables list.
11. Select the Save import steps
check box to display the specification details.
13. In the Navigation section, click the Display Navigation Pane
check box to insert a check mark.14. In the Navigation section, click the Navigation Options button.The Navigation Options dialog box appears, Notice the grouping and display options that are available and then click Cancel.
6. Click Next to display the next screen and then select the First Row Contains Column Headings
check box. Access uses these column headings to name the fields in the table.
6. Right-click the Sales Team Query document tab and then
choose Design View to display the query in Design view.
10. Click Next to display the next screen, which is where you
choose the primary key. Keep the default selection, which lets Access add the primary key.
Create a Multivalued Lookup Field 13. Click Flood/Kathie and Mares/Gabe on the list and then click OK to
choose those multiple values for the field.STOP. CLOSE the database and then EXIT Access.
6. Click the Sort cell in the first column, click the down arrow and then
click Ascending on the drop-down menu.
The Ribbon and dialog box launcher 10. Click File and then
click Close to close the database. Access appears with no database open.
7. On the Home tab, in the Views group, click the lower half of the View button and then
click Datasheet View. Notice that the First Name field doesn't appear.
6. In the Bed Size field,
click King in the second row.
7. On the Design tab, in the Query Type group,
click Make Table. The Make Table dialog box appears,
4. Click Table: Used Cars Sold and then
click Next to display the Select Fields screen.
3. Click Crosstab Query Wizard and then
click OK to display the Crosstab Query Wizard
10. Click the Table1 name to select it. Click the Rename Table button. If you receive a message stating that an expression you entered is the wrong data type for one of the arguments,
click OK. The Table Analyzer Wizard dialog box appears. Take Note You can also double click on the table name to rename it.
17. Press and hold the Shift key and then click both the labels and controls for all the controls. In the Sizing & Ordering group,
click the Align button, and then select Left from the menu that appears. All the controls are left-aligned as a group.
16. Select the ID label and control and in the Sizing & Ordering group,
click the Align button, and then select Right from the menu that appears.
Rename a Table 1. On the Create tab, in the Templates group,
click the Application Parts button and then click Comments to create a new table.
8. In the Records group, click the Delete button arrow and then
click the Delete command on the menu that appears.
Change Views 4. On the Ribbon, in the Table Tools group,
click the Fields tab to display the contextual commands for that view.
2. On the Home tab, in the Sort & Filter group,
click the Filter button. A menu appears.
4. On the Design tab, in the Header/Footer group,
click the Logo button. The Insert Picture dialog box appears.
2. On the External Data tab, in the Export group,
click the More button. On the menu that appears, click Word. The Export - RTF File dialog box appears.
10. On the Design contextual tab, in the Header/Footer group,
click the Page Numbers button. The Page Numbers dialog box appears.
13. On the Design tab, in the Tools group,
click the Primary Key button.
3. On the Create tab, in the Reports group,
click the Report button. The report appears in Layout view, as shown in Figure 6-2. Notice the Report Layout tools that appear in the Ribbon.
3. On the Query Tools Design tab, in the Query Setup group,
click the Show Table button to display the Show Table dialog box.
8. On the Home tab, in the Records group,
click the Totals button again. The Total row reappears
10. On the Design tab, in the Grouping & Totals group,
click the Totals button and then select Count Records from the menu.
2. On the Home tab, in the Records group,
click the Totals button. The Total row appears below the row with the asterisk (*) in the record selector box.
Troubleshooting If all text for the properties is not visible,
click the left border of the Property Sheet and drag to widen it.
12. Click the Close button on the Industry Friends Query tab to
close the query and then SAVE the query if prompted.
8. Click the Close button on the Employees Contact Info Query tab to
close the query. When prompted to save, click Yes.
10. Click the Field cell in the Total
column of the new Flag field.
The Quick Start category of these templates contains a collection of predefined objects arranged by parts for tracking things such as
comments, contacts, and issues. In this exercise, you will quickly create a table using the Application Parts gallery and Quick Start.
Create Crosstab Queries 1. OPEN the Importers database from the
data files for this lesson and then SAVE it as Importers-final.
Defining Table Relationships You define a table relationship in the Relationships window. To create that relationship, you place common fields in tables and
define the relationships between the tables. Common fields used in different tables do not have to have the same names, but they usually do.
Take Note The Short Text data type and the Long Text data type each has unique properties and size limits,
depending on whether you're using them in a desktop database or an Access web app.
6. Move the insertion point above the Model field on the
design grid until it turns into a bold down arrow. Click to select and highlight the Model column.
Save a Table. 1. From the File tab, click the New command and then click the Blank
desktop database icon; a Blank desktop database screen appears in the center of the screen.
5. Click the file path and edit it by changing the
destination file name to New Employees2. txt
Click to access
dialog box
3. Click Browse to open the File Open
dialog box to locate the source file.
Set Data Validation Rules 3. Click the Expression Builder button to
display the Expression Builder dialog box
Define Input Masks for Fields 2. Click the Input Mask property box in the lower portion of the table design grid to
display the Input Mask Wizard button (...) on the far right of the cell.
2. Click the down arrow next to All Access Objects at the top of the Navigation Pane to
display the menu, Categories Groups that relate to the selected category
Create a Multivalued Lookup Field 6. Click Next to accept your settings and
display the next screen in the Lookup Wizard.
16. Click the Data Table tab to
display the options on the tab
13. Click the Data Labels tab to
display the options on the tab.
7. Click the Gridlines tab to
display the options on the tab.
9. Click the Legend tab to
display the options on the tab.
Apply a Filter 6. Click the Toggle Filter button in the Sort & Filter group to
display the records without the filter.7. In the second record in the Product Name field, double-click the word Decaf to select it
2. Right-click the Price field to
display the shortcut menu.
2. Double-click the chart. The Microsoft Graph software launches,
displaying the chart in a view similar to Design view,
3. CLOSE the database but
don't close Access.
4. In the Sales Team field list,
double-click E-mail Address.
Modify Table Properties 2. In the Navigation Pane,
double-click Events to open that table.
2. In the Show Table dialog box,
double-click Inventory to add the table to the design grid.
Add Criteria to a Query 1. In the Navigation Pane,
double-click the Employees Contact Info Query to open it.
8. In the table field list in the Inventory table window,
double-click the Model field to add it back to the query design grid as the last column.
Create a Split Form 1. In the Navigation Pane,
double-click the Sales Reps table to open it.
Change Format Options 1. In Design view,
double-click the chart to open Microsoft Graph.
Take Note To tell which table is the left table or the right table in a given join,
double-click the join to view the Join Properties dialog box.
7. In Filter by Form view, click the Year field
down arrow and then select 2010,
6. Click the Font weight:
down arrow and then select Normal.
12. In the Application Options section, click the Display Form
down arrow and then select none) from the menu
2. Place the insertion point in the Dimensions box and then click the
down arrow on the right to display the list of options shown in Figure 5-13.
6. The database objects are displayed with details. Click the right side of the Navigation Pane and
drag to make it wider so all the information can be read, as shown
Take Note Access automatically creates a new column of placeholders in a layout when a field control is horizontally split and another field control is
dragged into the placeholder created next to the horizontally split field control.
3. In the Tables/Queries
drop-down list, click Table: Inventory.
3. In the Tables/Queries
drop-down list, click Table: Sales Team.
5. Double-click the In Stock
field to add it to the report.
10. In the same manner, move the Rate label and
field to position it below the Bed Size field.
3. Click the >> button to move all the
fields into the Selected Fields list.
Once you have decided to create a Student List table, you need to determine what information you want to store in the table—such as age, birthdate, or tuition. Organize each piece of information into the smallest useful part—
for example, use First Name and Last Name instead of just Name if you want to sort, search, calculate, or report using either a first name or a last name or both).
Delete a Field 4. Click the column header
for the Status field.
9. On the Print Preview tab, in the Page Size group, click the Margins button and then select Narrow
from the menu that appears. The Price field header and data are now completely visible.
14. Select the Count aggregate
function from the menu, . Click Count.
In this exercise, you dd fields to a table using both the Click to Add column and the Add & Delete group. The last column in a table in Datasheet view
has a Click to Add column, which you can use to add a field simply by clicking on the Click to Add column header and choosing a data type from the menu that appears.
4. Change to Design view. Notice that the Sales Person Last Name Header
has been deleted from the grouping area.
5. Click E-mail Address in the Fields in "Employees" list. Click E-mail Address in the Fields
in "Stamford Employees" list. Click the <=> button to display them in the Matching fields box, as shown in Figure 7-9. These fields contain data that is in both tables.
Creates a field called LeadTime, and then displays the difference
in number of days) between the values in the RequiredDate and ShippedDate fields.
8. Click the row selector to the left of the Combo10 field to select it. Click and hold the row selector and drag it up until the black horizontal line is
in place above the In Stock field; release the mouse button. The Combo 10 field should now be the second item in the list.
Enter Captions 3. Type Venue. The caption property has now been set to Venue and will display as a column name
in table Datasheet view, as well as labels for forms, reports, and queries.
4. Expand the Page Footer section. On the Report Design Tools Design tab,
in the Controls group, click the Chart button. The pointer changes to a plus sign with a chart icon.
12. Click OK to insert the expression
in the Criteria row of the AskingPrice field.
The one-tomany relationship type signifies that each customer record
in the Customers table can have many order records in the Order Summary table.
Displays the values for ProductID and ProductName
in the Products table for records in which the
10. Type Summary Report
in the Save box.
Remove a Filter 1. Select the Pounds field and then on the Home tab,
in the Sort & Filter group, click the Filter button. A menu appears.
3. Type Fall Collection
in the Title box.
6. Double-click the join line between the tables,
indicating which fields are joined. The Join Properties dialog box opens,
9. Click OK to accept the default date and time formats. The Date and Time controls are
inserted in the Report Header section of the report,
Take Note When replacing data, it is usually a good practice to click Replace
instead of Replace All so that you can confirm each replacement to make sure that it is correct.
10. Select the text box control if
it isn't already selected.
7. Resize the label and field controls that you
just added until your screen resembles
21. Leave the default name, click the Modify the
label design option button, and then click Finish
12. Click the row selector to the
left of the ID field to select the row.
8. Click the General tab. Notice that this tab displays the file type,
location, and size as well as the dates the file was created, modified, and accessed.
2. On the Home tab, in the Views group, click the
lower half of the View button and then click Design View.
Take Note Before you use this command on a shared file,
make sure no one else has the file open.
the records in the table. Or, select the Required check box to
make this a required field, where all instances of this field must contain a value.
In a third type of relationship, called a many-to-many relationship,
many records in the first table can have many records in the second table.
Database Essentials
media
22. Click the Detail row. Click the Alternate Row Color
menu and then select Automatic.
9. Select Sum from the Group By
menu in the Total row of the Flag field.
4. Click the Next button. The Table Analyzer Wizard dialog box, screen 2, appears. This dialog box provides
more information about what the wizard will do. Click the two Show me an example buttons to read more about how splitting the table is helpful.
SAVING A DATABASE OBJECT Access automatically saves data that you have entered any time you add an Application Part like a Quick Start template,
move to a new record, close an object or database, or quit the application. But you will need to save the design of a table, or any other database object, after it is created.
3. If necessary, click the expand button next to the Photo Exhibit table
name to show a list of fields related to the table, and then double-click Image Title to add it to the form.
10. In the Description box, type Export
new employee information to a text file.
11. CLOSE the report. Notice that the
new report is listed in the Navigation Pane
Take Note Pictures or attachments that are part of the source data are
not exported to Word. Expressions are not exported either, but the results are.
Take Note Some of the available data types in Access 2016 can only be chosen in table Design View and
not from the Data Type drop-down menu on the Ribbon. You will learn more about table Design View
15. Click OK. Notice that all the report data
now fits and will print on two pages.
4. Click Datasheet as the layout for the form. Form layouts help determine the positioning
of controls, objects, and data on a form.
A Quick Start field is a predefined set of characteristics and properties that describes a field, including a field name, a data type, and a number
of other field properties. Quick Start fields allow you to quickly add commonly used single fields or several related ones.
5. Click the row selector to the left
of the Combo10 field to select it.
5. Click the down arrow in the International Sales column
of the Total row and then select Sum from the menu.
With the insertion point placed in the first empty cell
of your new, blank database, you can begin typing to add data.
Take Note If an Access 2016 database file contains complex data,
offline data, or attachments created in Access 2013, you will not be able to save it in a format earlier than Access 2007.
7. On the final screen, type Exit_Inventory_Form as the default button name and then click Finish. The default button name indicated on your screen may differ depending
on how many controls you have previously attempted to include.) Notice that the image on the Button control on the form has changed to the Exit Doorway picture
The data type template can be accessed by opening the table in which you want to create the field and then clicking the newly created data type template
on the More Fields menu in the Add & Delete group on the Table Tools Fields tab on the Ribbon
Notice that the word Unbound is shown in the control and the word Text and a number depending
on the number of controls you have created in this session) appear in the label.
Apply a Filter Less Than or Equal to 30. The records are filtered accordingly. PAUSE. LEAVE the database open to use in the next exercise. Take Note Only
one filter can be applied per column. When you apply a filter to a column that is already filtered, the previous filter is removed and the new filter is applied.
3. If you want to specify a different destination, click Browse to
open the File Save dialog box. Choose a folder and then click Save.
However, you can open multiple databases at the same time by double-clicking another database's icon. For example, to open two Access databases, start Access,
open the first Access database, and then open a second Access database by double-clicking its database icon in File Explorer referred to as Windows Explorer in previous versions of Windows).
3. Table: Employees is the default selection in this screen. This table will contain the
records you'll want to display. Click the Next button to display the next screen in the Find Unmatched Query Wizard.
11. Change to Report view and scroll through the
report records to view the calculated totals.
9. SAVE the
report.
6. The Books table should be selected in the list; if it is not,
select it. Click the Next button. The Table Analyzer Wizard dialog box, screen 4, appears.
9. Click the Tabular option button to
select that as the layout for your subform.
21. The Yes, create the query option button
should be selected. Click the Finish button.
Setting Field Properties You can control the appearance of information, prevent incorrect entries, specify default values,
speed up searching and sorting, and control other appearance or behavior characteristics by setting or modifying field properties.
Take Note When printing lengthy reports, you can choose other options,
such as printing a range of pages,
Use the Lookup Wizard to create multivalued fields. The Lookup Wizard guides you through the process of creating a field or lookup column
that can "look up" data that exists in one or more tables to automate the complexity of manually relating tables.
2. Click Browse to open
the File Open dialog box.
3. Click Browse to open
the File Open dialog box.
9. Click the Save Object As command in
the File Types category and then click Save As.
Create a Blank Database 1. Click the File tab to open the New screen in Backstage view. Take Note The New screen appears in the Backstage view after you click on
the File tab when no database is open. When a database is open, the File tab takes you to the Info screen in the Backstage view.
2. On the Home tab, in the Sort & Filter group, click
the Filter button. A menu appears on the field,
1. Click the row selector to the left of
the Gross Sales field to select the entire row.
2. Click the Group On Sales Person Last Name row in
the Group, Sort, and Total pane, and then click More.
Define Input Masks for Fields 7. Click Finish. The input mask appears in
the Input Mask row
8. Click the down arrow in the Total cell of
the Mileage field and then click Average.
Format Controls on a Report 1. Double-click the Toy Summary report in
the Navigation pane to open it and then change to Design view.
Format Controls on a Form 1. Double-click the Inventory form in
the Navigation pane to open it if it's not open already and then change to Design view.
1. Double-click the Inventory form in
the Navigation pane to open it in Form view.
Define Table Relationships 7. Click Create. A relationship line representing the one-to-many table relationship of the Customers and
the Order Summary tables is displayed. You just created a one-to-many relationship between these tables using Customer ID, the common field.
9. Click OK. The expression appears in the Control Source row of
the Property Sheet as well as in the control box in the Detail section of the report
2. On the Create tab, in the Queries group, click
the Query Wizard button. The New Query dialog box appears
12. Click the Save button on
the Quick Access Toolbar
20. CLOSE Print Preview and the report. PAUSE. LEAVE the database open to use in the next exercise. Take Note You can add the Print Preview and/or the Quick Print command to
the Quick Access Toolbar by clicking the Customize Quick Access Toolbar down arrow at the end of the toolbar and then selecting Print Preview or Quick Print from the menu
12. Click the Save button on
the Quick Access Toolbar.
13. Click the Save button on
the Quick Access Toolbar.
17. Click the Save button on
the Quick Access Toolbar.
2. Click and drag the Future Period Contract Projections form object from the Navigation Pane to
the Report Detail section under the Company Name and Contract Amount controls.
14. Type Report Design in
the Report Name box and then click OK.
12. CLOSE the Sales Team Backup table and
the Sales Team query. SAVE the changes when prompted.
12. In the Navigation Pane, double-click
the Samples Given Subform to open it.
3. In the Advanced category, click Back Up Database and then click
the Save As button. The Save As dialog box appears,
5. Click the Horizontal option button in
the Shading styles section and then click OK.
12. Click the More Options button in
the Sort line. Notice the options available for customizing a sort
the Backstage View 2. Click Open and then navigate to
the StudentData database from the data files for this lesson.
12. On the Home tab, in the Views group, click the lower half of
the View button and then click Datasheet View. The prompt appears in the Enter Parameter Value dialog box.
Modify Table Properties 3. On the Home tab, in the Views group, click the bottom half of
the View button and then click Design View from the menu that appears.
10. On the Home tab, in the Views group, click the lower half of
the View button and then click Design View.
5. On the Home tab, in the Views group, click the lower half of
the View button and then click Design View.
9. On the Design menu, in the Views group, click the lower half of
the View button and then click Form View to display the form in Form view (see Figure 5-6).
2. On the Home tab, in the Views group, click the lower half of
the View button and then click Layout View on the View menu.
7. On the Home tab, in the Views group, click the lower half of
the View button and then select Design View.
12. On the Home tab, in the Views group, click the lower half of
the View button and then select Layout View. The split form is displayed with the datasheet on top
3. On the Home tab, in the Views group, click the lower half of
the View button, and then select Design View.
Create Fields 2. Scroll to the right of the Events table to display the last column and then click the Click, to Add header. Click Yes/No from the menu of available data types that appear. You are going to add a new field with
the Yes/No data type in which you can indicate whether events will have on-site staff. A new field named Field1 is added, and the Click to Add column becomes the last column in the table.
The Page Size group controls margins and page size; the Page Layout group controls page orientation and printing options;
the Zoom group controls viewing options; and the Data group controls all aspects of data exporting.
5. Notice the two options you have when importing data. Click Import tables, queries, forms, reports, macros, and modules into
the current database and then click OK. The Import Objects dialog box appears. Click the Options button to display additional import options,
Use the Report Wizard 1. OPEN Messenger database from
the data files for this lesson and then SAVE it as Messenger-final.
You can also rename or delete a table, but keep in mind that such a change could possibly break the functionality of
the database, because in a relational database the various components work together.
3. Drag the In Production field to
the design grid below the ID control.
CREATING FIELDS AND MODIFYING FIELD PROPERTIES A field has certain defining characteristics such as a name that uniquely identifies
the field within a table, and a data type that is chosen to match the information to be stored.
Link Master Fields List the primary key field(s) in this table that match the child fields for the subdatasheet. Filter On Load Automatically apply
the filter criteria in the Filter property by setting to Yes) when the table is opened in Datasheet view.
3. Click Chart on the menu bar and then select Chart Options from
the menu that appears. The Chart Options dialog box appears
4. Select the control, right-click and then select Properties from
the menu to display the Property Sheet if it isn't already displayed.
6. Click anywhere on the Form view above the datasheet and notice that
the mobile phone number has been changed there as well.
Create a Multivalued Lookup Field 11. Select the Allow Multiple Values check box to allow for
the multiple selections of values. The completed dialog box is shown
Think carefully before you rename a table. If existing database objects, such as queries or reports, use data from that table,
the name modification might break the functionality of the database. In this exercise, you create a new table and then rename it using the shortcut menu.
6. Click OK. The table object is saved as a query object and
the new Summary Query appears in Datasheet view.
3. Click the Next button to move to
the next page in the Form Wizard
6. Click the Next button to display
the next screen in the Find Unmatched Query Wizard.
Click the box for the field property you want to set. Alternatively, you can press F6 and then move to the property using the arrow keys. Type a setting for
the property or, if an arrow appears at the right side of the property box, click the arrow to choose from a list of settings for the property.
11. CLOSE
the reports
12. Click and drag the Price field control to
the right of the ID field control
Troubleshooting If the borders are not within the right margin, change to Design view and resize the column to
the right of the In Production field so it is at the 10½-inch mark. Save the form and return to Print Layout view.
2. Click the ID field and drag it to
the right of the Price control,
6. In the Field List pane, click and drag each Last Name, First Name, Specialty, and Hospital field to the form and to
the right placeholder of the original locations of the Field1, Field2, Field3, and Field4 label controls that you just deleted.
10. Click Finish. The Rooms Wizard report appears on
the screen, as shown in Figure 6-6.
4. On the Query Tools Design tab, in the Results group, click the lower half of the View button and then click Datasheet View. The query results display all records that end with
the string "Manager" in the Position field. (You may have to increase the width of the Position field to completely view the data within it
Set Data Validation Rules 2. Click the Validation Rule property box in the lower portion of
the table design grid to display the Expression Builder button ...) on the far right of the cell.
Allow Zero Length 2. Click the Allow Zero Length property box in the lower portion of
the table design grid to display the down arrow on the far right of the cell.
Allow Zero Length 1. In the Field Name column in the upper portion of
the table design grid, click in the Description cell.
3. CLOSE
the table.
6. SAVE
the table.
6. Type Photo Details as
the title of the form.
9. Type Rooms Wizard as
the title of the report.
4. Click the By Column button on
the toolbar to change it back to the original chart.
9. Click the Contents tab to view a list of
the types of objects within the database file
Set a Field Property in Datasheet View and Design View 7. In the Field Name column in
the upper portion of the table design grid, click in the Title cell.
10. Right-click the Delete Query document tab and
then choose Design View to display the query in Design view.
7. Right-click the Delete Query document tab and
then choose Design View to display the query in Design view.
Renaming a Table To rename a table or other database object, you must first close it. In the Navigation Pane, locate and right-click the object that you want to rename and
then click Rename on the shortcut menu that appears. Or, select the table in the Navigation Pane, press F2, type a new name, and then press Enter.
7. Click the File tab and
then click Save.
17. Select the Zip field and
then click the > button.
19. Select the Zip field and
then click the > button.
14. Select the Address field and
then click the > button. Press Enter.
Freeze/Unfreeze and Hide/Unhide Fields 11. Deselect the check boxes next to all the other field representations except Last Name and Business Phone and
then click the Close button in the Unhide Columns dialog box. Notice the only fields now displayed in Datasheet view are the Last Name and Business Phone fields.
9. On the Home tab, in the Records group, click the Delete button arrow and
then click the Delete command on the menu that appears.
To insert a new record, select any record in the table and
then click the New button on the Home tab
3. Scroll down in the Product Number box, select Avery USA 5160 and
then click the Next button. The second Label Wizard dialog box appears.
8. Click the File tab and
then click the Save As.
2. If necessary, click Show all tables link in the Field List pane and
then click the expand button next to Inventory to display the fields of the Inventory table.
7. In the Expression Elements section, click the Operators element, and
then doubleclick the * (asterisk) value in the Expression Values
7. Leave the Description field blank and
then press Tab again to move to the next field.
5. Click the Chart Type drop-down arrow in the toolbar and
then select 3D Area Chart from the menu. The chart changes to an area chart.
Find and Replace Data 7. Click the down arrow beside the Match menu and
then select Any Part of Field if it already is not selected to broaden the search
Freeze/Unfreeze and Hide/Unhide Fields 9. Select the Customer ID field. Click the More button and
then select Hide Fields. Notice the Customer ID field is now hidden from view
18. In the Sizing & Ordering group, click the Align button and
then select Right from the menu that appears. All the controls are now right-aligned as a group
18. In the Sizing & Ordering group, click the Align button and
then select Right from the menu that appears. All the controls are now right-aligned as a group.
4. In the Application Options section, click the Display Form down arrow and
then select Showroom Contact Form from the menu to display the form at startup.
5. Click the Type menu down arrow and
then select Sum if it isn't selected already.
9. Click on a blank space on the design grid, and
then select the In Production field control and label.
Create a Multi-Item Form 1. OPEN the Contoso database from the data files for
this lesson and then SAVE it as Contoso-final.
Define Input Masks for Fields 6. Click Next to accept the default settings in
this screen and display the final Input Mask Wizard screen.
12. Click the Close button on the Benefit_Providers tab to close the table. Troubleshooting If you don't close
this table, you will not be able to edit the data in the spreadsheet. You will get a "data is locked for editing" error message.
3. Double-click the ID field
to add it to the report.
6. Double-click the Price field
to add it to the report.
2. In the Show Table dialog box, double-click Inventory
to add the table's field list window to the design grid.
6. On the Home tab, in the Sort & Filter group, click the Toggle Filter button
to apply the filter. The records containing either the dimensions 30 X 40 or 12 X 28 are displayed
5. Click OK and then click Cancel
to close the Find dialog box.
Modify Table Properties 11. Click the Close button
to close the table.
19. On the Format tab, in the Control Formatting group, click the Change Shape button
to display the menu. Click the Oval option. The Button control's shape changes to an oval.
Create a Multivalued Lookup Field 8. Click Next to accept your selection and
to display the next screen in the Lookup Wizard.
Rename a Table 5. Right-click Comments in the Navigation Pane
to display the shortcut menu.
10. Click the Shutter Bar Open/Close button
to expand the Navigation Pane again.
13. On the Arrange tab, in the Move group, click Move Down twice
to move the In Stock control to the last cell row at the bottom of the layout.
12. In the empty cell below the Col1 header, type Yes. Continue typing values in the column as shown in using the down arrow key
to move to the next row. If you press Enter, the wizard advances to the next screen so be sure to use the down arrow key.)
8. On the Print Preview tab, click the Close Print Preview button
to return to Report Design view.
The Find and Replace commands in Access work very much like those in Word and other Office applications. You can use the Find command
to search for specific text in a table or to move quickly to a particular word or number in the table.
15. Double-click the Used Cars Sold table in the Navigation Pane to open it and scroll down
to see that the records from the Inventory table have been appended to the end,
12. Press the Tab key several times
to see the new tab order.
15. Change to Report view
to see the report
2. Click the Chart Area, the white background of the chart,
to select it. The Chart Area should be displayed in the Chart Objects list box in the upper-left corner of the toolbar.
Set a Field Property in Datasheet View and Design View 2. Click the Location column header
to select that field.
5. In the Import Objects dialog box, click Minute Employees,
to select the table you wish to import.
4. Press the Tab key to move
to the Data Type column.
6. Press the Tab key to move
to the Description field.
5. In the Available Fields box, double-click Sold By to move it
to the Selected Fields box and then click Next. The next screen appears, asking which values you want to use as column headings.
4. Click the Integral theme fourth item in the first row) to apply it
to the form. Notice how the form's text has changed (see Figure 5-11).
PAUSE. LEAVE the database open
to use in the next exercise
PAUSE. LEAVE the report open
to use in the next exercise.
6. Select the First Row Contains Column Headings check box
to use the first row of data as field names in the table.
6. Select the First Row Contains Field Names check box
to use the first row of data as field names in the table.
Defining and Modifying a Multifield Primary Key In some cases, you may want
to use two or more fields that, together, provide the primary key of a table.
12. Change to Report view
to view your changes.
3. Click Tables and Related Views. The default group in this category is All Tables, which appears in the menu at the
top of the Navigation Pane. Notice the Students table and all other objects related to it are displayed under the Students object tab.
3. In the same manner, reduce the width of the Price control
until its right side is at the horizontal ruler's 4" mark.
Set Data Validation Rules 1. In the Field Name column in the
upper portion of the table design grid, click the End Date cell.
Enter Captions 1. In the Field Name column in the
upper portion of the table design grid, click the Location cell.
2. Click the Show all tables link in the Field List pane, if necessary, to
view all the available fields in the Inventory table.
the Backstage View 10. Click the Print option to
view more options and commands.
the Backstage View 12. Click the Options option to
view more options and commands.
the Backstage View 5. Click the Save As option to
view more options and commands.
the Backstage View 4. Click the New option to
view the options and commands available.
the Backstage View 11. Click the Account option to
view the options and commands shown
15. Scroll through the Author Contact Information table. Notice that the Generated Unique ID field
was added as a primary key. Click the Next button. The Table Analyzer Wizard dialog box, screen 6, appears
An important principle to consider when planning a database is to try to record each piece of information only once. Duplicate information, or redundant data,
wastes space and increases the likelihood of errors. Relationships among database tables help ensure consistency and reduce repetitive data entry.
Once you have decided how the information will be used, the next step is to categorize the information by dividing it into subjects such as Products or Orders,
which become the tables in your database. Each table should only contain information that relates to that subject.
Take Note Your figures might look slightly different depending upon
which folder you have stored your files.
Document Format (PDF) or XML Paper Specification (XPS),
which helps you share data with others who may not have Access installed.
Access includes a quick and easy way for you to add fields to a table using the Add & Delete group on the Table Tools Fields contextual tab,
which includes a collection of fields with associated data types and built-in Quick Start fields that can save you considerable time.
Fields contextual tab,
which is shown in Figure 4-8. You can use these commands to add fields with associated data types, add Quick Start fields, insert lookup columns, and delete columns.
8. Scroll through each table. Notice that the Table2 box contains the Year,
which should be in Table1 with the sales information. Click the Year field to select it.
Defining a Primary Key You can define a primary key for a field in Design view by selecting the row that contains the field for
which you want to assign a primary key and then clicking the Primary Key button on the Design tab in the Tools group on the Ribbon.
11. Click the down arrow beside with A on top and then select
with Z on top from the menu. The field is sorted in descending order.
They must have the same data type, though. In this exercise, you use a table that already has a primary key field to create a relationship
with another table. You can create three types of relationships in Access tables: one-to-one, one-to-many, and many-to-many.
Apply a Filter 16. Select On or After 9#/4#/2016. The data is filtered to show only those records
with content in the Scheduled Order Date field that matches the filter selection.
This recognition of a nonexistent value actually represents a string. You enter a zero-length string by typing two double quotation marks
with no space between them ""). In this exercise, you modify the Allow Zero Length property for the Description field.
5. Click the ID field control until you see the orange border
with selection handles on the borders and corners.
2. On the File tab, click the Print button to display the Print menu,
with the options Quick Print, Print, and Print Preview.
4. In the Position contains box, type Marketing and then click OK. The records are filtered to show only those results containing the
word "Marketing" in the Position field. Adjust the fields to display the entire position name if necessary.
5. Click OK. Excel opens and the new
worksheet with exported data is displayed,
If you open a file in a multi-user environment such as a SharePoint drive, other users can read and
write to the database as well; therefore, you may want to open the database with other options.
Take Note To create a new database using external data, you would follow these same steps but in Step 1,
you
Take Note You only need to open the database in Exclusive mode if
you are going to set or unset a password. The database will be protected with the password in any mode.
Take Note You can't see the formulas in the fields but
you are using arithmetic operators to group this data.
Double-click the record in the Attachments field to display the Attachments dialog box where
you can add, remove, open, or save multiple attachments, such as images, documents, and spreadsheets, for a single record.
If the program that was used to create the attached file is installed on your computer,
you can open and edit the file using that program.
You can save attached files to your hard disk or network drive so that
you can save changes to documents there before saving them to the database.
Defining and Modifying Data Types for Fields Or, if you need to store large amounts of text,
you may need to use the Long Text data type(up to 64,000 characters) instead of the Short Text data type 255 characters).
Field Properties Some field properties are available in Datasheet view, but to access the complete list of field properties
you must use Design view. An example of field properties for a table in Design view is shown
Reviewing Database Fields Reviewing preexisting database fields can give you an idea of the type of information that you can store in a database.
you open a database that is further along in the process of being developed and view the fields in a form to see what a more advanced database looks like.
3. Click Run. A message appears asking if
you want to overwrite the existing table.
Remember these points when finding and replacing data in Access 2016
• Click the Match Case box to search for text with the same uppercase and/or lowercase capitalization of text.
Remember these points when finding and replacing data in Access 2016
• If you type a wildcard character in the Replace With box, Access will insert that character just as you typed it.
Remember these points when finding and replacing data in Access 2016
• In the Match menu, you can specify where you want Access to look in a field. Select Any Part of Field for the broadest search.
• Maximum: Finds the highest value in a column
• Minimum: Finds the lowest value in a column
Click the down arrow next to the Open button to view these options:
• Open: Opens with default access.
In your completed sort, Smith, Lauren, would be listed before Smith, Mark, in an A to Z ascending) sort. When designating the sort order
, however, you select the innermost field first and choose the type of sort you want from the shortcut menu.
8. In the Expression Values category, double-click the minus sign
-). The minus sign should appear in the expression and next to the AskingPrice field.
14. Click the Save button on the Quick Access Toolbar
. The Save As dialog box appears.
Defining Database Tables
4. CLOSE the Student List tab.
13. CLICK FINISH TO COMPLETE THE WZARD.
A LOOKUP WIZARD DIALOG BOX DISPLAYS.
8. On the Design tab, in the Show/Hide group, click the Totals button.
A new Totals row should appear below the Table row on the design grid.
16. Change to Form view to display the new arrangement of the controls and resize the
Access application window not the form window) to see the controls stretch and shrink in conjunction with the window size.
19. Yes, display a legend should be selected. If not, select it and then click the Finish button.
Access inserts your chart. Notice that Design view displays sample data and not the actual data from your chart.
Database Essentials
Access the
Data Type Example Description Yes/No Insurance: Yes Stores Boolean true or false) data.
Access uses 1 for all Yes values and 0 for all No values.
Access will update the Customer ID value in the Order Summary table if the Customer ID value in the Customers table is updated. This ability to update related fields automatically ensures consistent Customer ID values in the related tables. Similarly, if the cascade delete related records option is selected,
Access will delete all Customer ID records from the Order Summary table if the related Customer ID record is deleted from the Customers table; therefore, preventing orphaned records.
13. On the Design tab, in the Results group, click Run.
An alert message appears asking if you are sure you want to append the selected rows.
Referential integrity is an option that you can select in the Edit Relationships dialog box to prevent orphan records.
An orphan record is a record in one table that references records in another table that no longer exist.
15. CLOSE the
Applicants table
2. Click the Button control on the form. On the Format tab, in the Control Formatting group, click the Quick Styles button and then select Intense Effect -
Aqua, Accent 5 from the Theme Styles, as shown in Figure 8-24. Click the style to apply it; the Button control's style changes to the chosen Quick Style.
You can easily sort by one or more fields to achieve the order that you want. Access can sort text, numbers, or dates in ascending or descending order.
Ascending order sorts data from beginning to end, such as from A to Z, 1 to 10, and January to December.
10. Click the blank field in the Append To row under the
AskingPrice cell and then select Sales Price as the destination field
7. This expression flags the cars that cost more than $4000
AskingPrice] - TradeInValue]) and have a mileage of more than 20,000 miles Mileage]), and the cars that cost less than $4,000 and have less than 20,000 miles. Click OK.
8. Click the Zoom button in the Zoom group to view the report.
At this point, you could print the report or make any changes to the layout and then print it.
Database Essentials
Back button to
2. Click the Split Database button. The Create
Back-end Database dialog box appears,
the Backstage View 1. Click the File tab.
Backstage view opens.
Database Essentials
Browse
10. Scroll to the bottom of the report, if necessary, and place the pointer over the top of the Sales Person Last Name footer until it turns into a two-headed vertical pointer.
Click and drag the Sales Person Last Name Footer and place it below the Company Name and Contract Amount controls, This removes the excess blank space between company names in the Detail section.
The following is an overview of the options in the Backstage view:• Info: Default view. Use this option to view the current database file path and view and edit database properties.
Compact and repair the database and encrypt the database with a password to restrict access.
Open the Macro Builder to create a new stand-alone macro.
Correct In the Create Ribbon Tab in the Macros & Code Ribbon Group, you clicked the Macro button.
Run the OpenTuitionQuery macro.
Correct In the Design Ribbon Tab in the Tools Ribbon Group, you clicked the Run button.
Move the comment so it appears before the OpenQuery action in the macro.
Correct You clicked the t.
Delete the second OpenQuery action from this macro.
Correct You clicked the undefined t.
Take Note Fields can be rearranged in
Datasheet view by clicking on the field name headers and dragging them to where you want to move them.
Data Type Example Description
Grade: 95.5
4. Click the Add Existing Fields button in the Tools group. The Field List pane appears.
If necessary, click the Show all tables link.
13. Type Author Contact
Information and then click OK.
4. Use the File Open dialog box to locate the
Northwind database file and then click Open. The File Open dialog box closes.
Import Table Data from Another Access Database 1.
On the External Data tab, in the Import & Link group, click Access. The Get External Data - Access Database dialog box appears.
10. On the Print Preview tab, click the Close Print
Preview button to return to Report view.
10. Click Close on the
Property Sheet.
Click to access
Search online
13. CLICK NEXT TO OVE
TO THE NEXT STEP OF THE LOOKUP WIZARD
13. In the Navigation Pane, double-click the New Employees:
Table to open the new table with imported data,
7. In the Navigation Pane, double-click the Minute Employees:
Table to open the new table with imported data.
6. Select Sort Z to A.
The column is sorted.
3. Click Sort Smallest to Largest. The form is sorted by price from smallest to largest.
The record with the smallest price is displayed first.
5. On the Home tab, in the Sort & Filter group, click the Remove Sort button.
The records have resorted back to their original order.
Print Table Relationships 1. In the Tools group of the Relationship Tools Design tab, click the Relationship Report button.
The report is created and the Print Preview tab appears
6. On the Home tab, in the Views group, click the lower half of the View button and then select Print Preview from the menu.
The report is displayed in Print Preview. Notice the large area of blank space between company names in the detail section.
14. Click the Delete button on the right side of the Group On Company Name row in the Group, Sort, and Total pane.
The row is deleted, as is the Company Name header section.
27. Click Size/Space and then select Ruler again.
The rulers reappear.
5. Click the Next button.
The second Report Wizard dialog box appears.
17. Click the Next button.
The sixth Chart Wizard dialog box appears
You do not need to format your data by including blank rows or columns, as you might do in an Excel worksheet, because that just wastes space in your table.
The table merely contains your data. All visual presentation of that data will be done in the forms and reports that you design later.
8. Click the Next button.
The third Label Wizard dialog box appears.
• Field sizes: Field sizes provide another way to validate text. For example, if you create a field that stores first names, you can set it to accept a maximum of 15 characters.
This can prevent a malicious user from pasting large amounts of text into the field.
The Default Value property allows you to specify a value to automatically assign to a field when new records are added.
This is useful if you have data that consistently repeats. The Name & Caption property allows you to specify a new field name and the associated caption for that field.
This is a placeholder character that is part of the input mask field property for this field.
This means that when data is entered in this field, it must follow a certain pattern. In this case, MM/DD/YYYY.
9. Click Next to display the final screen. Keep the default name
Used Cars Sold_Crosstab)and selection View the query).
Filtering Data within a Table A filter is a set of rules for determining which records will be displayed.
When you apply a filter, Access displays only the records that meet your filter criteria; the other records are hidden from view.
Viewing and Printing Table Relationships You may want to print a table relationship to save for your records or to discuss with a colleague. The Relationship Report command makes this easy.
When you choose to print the relationship report, the Print Preview tab appears with options for viewing and printing the report.
17. Click Next. The Table Analyzer
Wizard dialog box, screen 7, appears
Take Note You should be cautious when deleting record data.
You cannot undo the deletion of a record.
15. Click the Object Designers
button on the left.16. Scroll to the bottom of the window to see the Error checking section.The Enable Error Checking options are turned on by default.
6. In the Tables/Queries drop-down list,
click Table: Industry Friends.
3. In the Tables/Queries drop-down list,
click Table: Used Cars Sold.
2. On the External Data tab, in the Export group,
click Text File. The Export - Text File dialog box appears
2. Right-click the Media field to display the shortcut menu,
click Text Filters, and then select Contains, as shown in Figure 5-12. Click Contains to display the Custom Filter dialog box.
Delete a Field 3. In the confirmation message that appears,
click Yes. The field is deleted.
6. Select the Region (Borough) field and then
click the > button to move it to the grouping levels box.
The Ribbon and dialog box launcher 5. On the Students Object tab,
click the ID column header in the table to select the ID column.
8. On the Design contextual tab, in the Controls group,
click the Text Box button. The mouse pointer changes to the move pointer with a plus sign (+) when you place it over the report.
8. On the Home tab, in the Sort & Filter group,
click the Toggle Filter button to apply the filter. Only 2010 vehicles are shown in the results.
13. On the Home tab, in the Sort & Filter group,
click the Toggle Filter button to remove the filter.
10. Click the down arrow in the Total cell of the TradeInValue
field and then click Sum. Your Total row should appear
9. Select the Sales Person Last Name
field header on the report.
Take Note Access reviews the first eight rows in each column to suggest the data type for the corresponding
field. If the column contains different types of values, the wizard suggests a data type that is compatible with
Take Note If you need to add or remove fields from the query, go to Design view and double-click to add
fields or select the fields that you don't want and then press Delete to remove them from the query.
6. Click Run. Notepad opens and the new
file with exported data is displayed.
12. In the Description box, type Import text
file with job applicant contact information
Database Essentials
files
8. Right-click the Bed Size header. A shortcut menu appears. Notice that the Equals "King"
filter and the other filters from the Selection menu are also available in the shortcut menu
Remove a Filter 2. Select Clear
filter from Pounds.
10. Click the Close button on the Visual Basic
for Applications window to return to Access.
Click to access
for templates
8. Click the Next button to display the final screen
in the Find Unmatched Query Wizard.
Take Note Once a field has been set to the Attachment data type,
it cannot be converted to another data type.
Before normalizing a database and defining fields for the efficient storage of data in tables,
it's important to know where your data will come from.
Defining Database Tables Tables are the most basic organizational element of a database. Not only is it important to plan the tables so they will hold the type of data you need,
it's important to plan how the tables and information will be connected. In this exercise, you view a visual representation of the relationship between two tables.
21. Click the Alternate Row Color
menu and select Automatic
Troubleshooting Type the passwords exactly as
printed throughout this exercise to avoid error messages.
6. Click the Close button on the Industry Friends Query tab to close the
query and then click Yes to save changes when prompted.
Take Note To use a newly created database template from within Access, click the Personal link on
the New menu after clicking New on the File tab. The newly created application part can be accessed on the Application Parts menu in the Templates group on the Create tab on the Ribbon.
10. Notice the Navigation Pane now includes objects imported from
the Northwind database The databases have been merged.
6. Click the Close button on
the Photo Exhibit form to close it.
8. Click the Save button on
the Quick Access Toolbar.
Define Table Relationships 2. Click the Show Table button in the Relationships group under
the Relationship Tools Design tab. The Show Table dialog box appears
If you want to replace the data, type the new data into
the Replace With box and then click Replace or Replace All.
FINDING AND REPLACING DATA A big advantage of using a computer database rather than paper and pencil for recordkeeping is
the ability to quickly search for and/or replace data. You can access these features from the Find and Replace dialog box.
You can also right-click a selected column and choose a Sort command from the shortcut menu.
the available sort commands in the shortcut menu vary depending on the type of data in the column.
4. Click the Or tab at
the bottom of the form.
6. On the next screen, keep the default settings to have
the button contain the Exit Doorway picture displayed in the dialog box. Click Next.
11. CLOSE
the database.
7. Click the OK button. A formatting rule for the In Stock field is added to the dialog box. Now, when the report is viewed in Report view,
the value for the In Stock field will appear bold and the control background color will appear red if the formatting rule applies.
7. Click Next to display the next screen where
the wizard prompts you to review the field properties.
11. Right-click the document tab for the query and
then choose Datasheet View to preview your changes.
17. In the Navigation Pane of Access, double-click Benefit_Providers
to open the linked table. Notice that the new row of data has been added,
Set a Field Property in Datasheet View and Design View 1. Double-click the Events table in the Navigation Pane
to open the table in Datasheet view, if it is not already open.
8. On the Home tab, in the Sort & Filter group, click the Toggle Filter button again
to remove the filter, click the Advanced button and then click Clear All Filters.
8. Press the Delete key
to remove the table.
10. Click Yes in the dialog box prompting you
to save design changes to the form.
4. Double-click the Doctors tab and then
type Doctors Form to rename the tab.
7. In the Save As dialog box,
type Image Info and then click OK.
9. Click in the text box at the top of this screen,
type Non-Stamford Employees to name your query, and then click the Finish button. The query is displayed
8. In the Save As dialog box,
type Photo Label and then click OK.
14. In the Save As dialog box,
type Sales Reps Split and then click OK.
Rename the field by right-clicking the column header, choosing Rename Field from the menu, and typing a new name. You can also
type information directly in a column. Access will try to automatically determine the field data type by the data entered.
3. In the Media contains box,
type print and then click OK.
6. In the blank area of the dialog box,
type the following: Markup: AskingPrice]
You can use the Replace command to automatically replace a word or number with something else. In the Find and Replace dialog box,
type the text or numbers that you want to search for into the Find What box and then click Find Next to locate the record containing the data.
all of the values in the column—usually the Short Text data type. Although you can choose a different data
type, values that are not compatible with the chosen data type will be ignored or converted incorrectly during the import process.
5. Click OK. Word opens and the new document
with exported data is displayed,
15. Change to Datasheet view. Your screen should resembleNotice the records in the Make field are grouped by Year and counted with the results appearing in a new column named CountOfMake
. Also notice the Year field is grouped and each year remains counted as applied from the aggregate function we created previously in Datasheet view.
Descending order sorts data from the end to the beginning, such as from Z to A, 10 to 1, and December to January
. In this exercise, you sort data using multiple fields, and then remove the sort.
HOW TO USE MULTIPLE COLUMNS IN A LOOKUP FIELD
1 OPEN THE TABLE IN DESIGN VIEW
Change Chart Options
1. Change to Design view.
Change Chart Types
1. Change to Design view.
Create a Subform on a Report
1. Change to Design view.
Filter Data with Common Filters USE the Graphic Art-final database that is open from the previous exercise.
1. Double-click the Photo Exhibit form in the Navigation Pane to open it in Form view.
Create a Report in Design View USE the AlpineSkiHouse-final database that is open from the previous exercise.
1. If necessary, click the Rooms table in the Navigation Pane to select it.
Define a Primary Key
1. In the Navigation Pane, double-click Order Summary: Table to open the table.
Sort Data within a Query
1. In the Navigation Pane, double-click the Industry Friends Query to open it.
Run Export Specifications
1. In the Navigation Pane, double-click the New Employees: Table to open it, if necessary.
Save a Table
12. Click the Save As button.
Defining Database Tables
2. CLOSE the Relationships tab.
Review Database Fields
3. Click Close to close the Student Details dialog box.
Create a Table Using the Application Parts Gallery and Quick Start
3. Click the Enable Content button on the Message Bar to trust the database if necessary.
Find and Replace Data
3. Click the Replace tab in the Find and Replace dialog box.
Define a Primary Key
3. Click the Row Selector box beside the Order ID row to select the row.
Enter, Edit, and Delete Records
4. Type 30 and then press Tab.
Use the Keyboard to Navigate among Records
8. Press the Right Arrow key to move to the Product Name field.
Define and Modify a Multifield Primary Key
9. CLOSE the table.
Find and Replace Data
9. Click the Replace button. Access replaces Elm with Little Elm.
The Ribbon and dialog box launcher
9. Double-click Home again to display the groups.
Set a Field Property in Datasheet View and Design View
9. Press Enter.
10. Click Finish to accept the default table name.
A Save Import Steps screen appears.
Rename a Table 3. OPEN the Comments table, right-click Comments in the Navigation Pane, and then select Rename from the shortcut menu.
A dialog box appears that states that you can't rename the database object 'Comments' while it's open. Close the dialog box.
Save a Table 4. Click the Create button.
A new blank database appears with the default table labeled Table1 displayed.
2. On the Create tab, in the Tables group, click the Table Design button.
A new blank table is created in Design view
2. On the Create tab, in the Forms group, click the Form button.
A new form containing all the fields from the Inventory table is created in Layout view. Notice the stacked control layout is the default.
Use Object Tabs 1. In the Navigation Pane, double-click Student Details.
A new object tab opens to display the form.
Use Object Tabs 2. In the Navigation Pane, double-click All Students.
A new object tab opens to displaythe All Students report tab along with the other two tabs, as shown
and then select New Record from the shortcut menu.
A new record is added to the end of the table.Select existing data to edit or delete it.
2. On the Create tab, in the Reports group, click the Blank Report button.
A new, blank report is created and the Field List pane is displayed. (If it isn't, click the Add Existing Fields button in the Tools group on the Design tab.)
This type of relationship is not common, because information related in this way is usually stored in the same table.
A one-to-many relationship is more common, because each record in the first table can have many records in the second table.
Defining and Modifying Data Types for Fields Sometimes you may also need to modify data types for preexisting fields.
Access provides 11 data types, each with its own purpose. Table 1-1 describes the types of data that each field can store.
9. On the Design tab, in the Results group, click Run.
An alert message appears asking if you are sure you want to create a new table with the selected records.
12. On the Design tab, in the Results group, click Run.
An alert message appears, asking if you are sure you want to delete the selected records.
6. Click the down arrow on the Open button and then select Open Exclusive from the menu, as shown in
Click Open Exclusive. The Fabrikam-final database file opens in exclusive mode.
11. Click the Room ID field to select it. Position the mouse pointer on the square handle in the middle of the right-side border.
Click and drag the field to the left to decrease the size by about one-quarter inch.
8. Click the File tab and then click New to display the New menu.
Click the PERSONAL link to the right of the FEATURED link. The newly created Photo Exhibit database template appears,
9. Notice that the Sales Person Last Name field is the active field in bold type.
Click the Priority up arrow to move the Sales Person Last Name field to the second level of grouping.
9. Click the Error Checking Options button next to the Report Header section title to view the Error Checking Options menu, Notice the menu text that states Report width is greater than page width.
Click the Remove Extra Report Space menu option. The report's width is automatically adjusted; however, the issue is not entirely corrected and the Error Checking Option button still appears.
9. Change to Form view and cycle through the records.
Click the Save & Close button on the form to close the form.
SOFTWARE ORIENTATION Navigation Pane
Double-click database object to open it
Commands Results
Down Arrow Moves cursor to the current field in the next record
Extended Price]
FROM [Order Details
7. In the Available fields box, double-click Last Name,
First Name, Position, and City to move them to the Selected fields box.
When defining table fields, it is important to define them as specifically as possible.
For example, if you are using a number, you should determine whether you need to use the Currency data type, the Calculated data type, or the Number data type.
Use the Print Preview tab to view and adjust data that appears on printed pages to create an effective print layout.
For example, you can adjust margins and change the page orientation before printing to ensure data appears appropriately
Filter Data within a Query The Industry
Friends Query should be open. 1. Click the Position header to select the field.
8. Name the query Duplicates for Industry
Friends and then click Finish. The query showing duplicate records in the table is displayed,
4. Click Industry Friends and then click the Add button. A second copy of the Industry
Friends table is added to the query, as indicated by the "1" in the title, as shown in
Data Type Example Description Calculated
FullName: John Derenzo Stores an expression based on two or more fields within the same table.
Commands Results
Home Moves cursor to the first field in the current record
Filter Data within a Report USE the AlpineSki
House-final database that is open from the previous exercise.
26. On the Arrange tab, in the Sizing & Ordering group, click the Size/Space button.
In the Grid category on the menu that appears, click the Ruler button. The rulers disappear.
Access displays a paper clip icon in the header row and in every record in the field along with a number in parentheses indicating the number of attached files in the field.
In this exercise, you create a new field and format it with the Attachment data type, and then remove the attachment from your database records
For example, if you are interested in viewing just a person's name and telephone number, you may decide to hide all fields except First Name, Last Name, and Phone Number.
In this exercise, you practice freezing and unfreezing fields, as well as hiding and unhiding them.
For example, you can format numbers to make them easier to read, or you can define a validation rule that must be satisfied for information to be entered in a field.
In this exercise, you set the Required field property in Datasheet view and both the Default Value and Field Size field properties in Design view.
CREATING A TABLE
It is easy to create a new table by using the Application Parts gallery and Quick Start.
7. CLOSE
Notepad.
10. Click
OK.
8. Click the Bed Size label. The border around the label changes to orange, indicating that it is selected.
Position the insertion point over the top of the border until the pointer changes to a four-sided arrow.
10. Click anywhere on a blank area of the design grid to deselect the field controls.
Press and hold the Shift key and then select the Price label and control.
SOFTWARE ORIENTATION
Print Preview Tab
10. Click the Close
Print Preview button.
22. Select File, Print, and then click
Print Preview. Change the layout to Landscape.
24. Change to
Report view
In this exercise, you save a database table, and then use the
Save Object As command to create a duplicate of the same table.
Remember these points when finding and replacing data in Access 2016 • Sometimes, Access selects the
Search Fields As Formatted check box. When it does, do not clear the check box, or your search probably will not return any results.
Database Essentials
SharePoint or
Troubleshooting If you click on the horizontal line near the 1 or the infinity symbol, you will get a different menu Show Table,
Show All, Save Layout), so be sure to click the center line to get the correct menu.
8. CLICK NEXT
TO MOVE TO THE NEXT STEP OF THE LOOKUP WIZARD
Freeze/Unfreeze and Hide/Unhide Fields 12. CLOSE the Customers table without saving the changes to the layout. PAUSE. LEAVE the database open to use in the next exercise.
Take Note You can save your table so it retains your formatting the next time you open it.
5. Click the down arrow in the Data Type column and then select AutoNumber from the menu.
The AutoNumber data type will automatically number your records starting at 1.
6. Click the Chart menu and then click Chart Type.
The Chart Type dialog box appears.
3. On the Format tab, in the Control Formatting group, click the Conditional Formatting button.
The Conditional Formatting Rules Manager dialog box appears.
10. Click the Next button to display the next screen.
The Detail option should be selected by default.
Modify Table Relationships 5. Double-click the center section of the relationship line.
The Edit Relationships dialog box appears again, listing the tables and the Customer ID fields on each side.
2. On the External Data tab, in the Export group, click Excel.
The Export - Excel Spreadsheet dialog box appears,
4. Click the Fill Effects button.
The Fill Effects dialog box appears.
2. Click Find Duplicates Query Wizard and then click OK.
The Find Duplicates Query Wizard appears.
2. Click Find Unmatched Query Wizard and then click OK.
The Find Unmatched Query, Wizard appears,
4. Click Open on the File tab.
The Open menu appears.
10. On the Design tab, in the Grouping & Totals group, click the Hide Details button.
The data in the Contract Amount field is displayed.
7. In the Navigation Pane, click the Photo Info form.
This is a form that you no longer need.
Use Object Tabs 4. Right-click the Student Details tab to display the shortcut menu.
This menu allows you to save the object, close the object, close all object tabs, or change the object view.
The following is a summary of the first three normal forms: Data can be brought into an Access database in a number of ways, including linking and importing.
When defining tables, you have to decide whether data should be linked to or imported from external sources.
8. Select the Major gridlines check box in the Category
X) axis section. Notice that gridlines are added to the preview,
12. Click
Yes.
13. Click
Yes.
You can change the category to Tables and Related Views, which groups the objects in a database by the tables to which they are related.
You can also change the category to one of several other views as shown
Once the filtered records are displayed, you can edit and navigate the records just as you would without a filter applied. Filters remain in effect until you close the object.
You can toggle between views, and the filter settings will stay in effect. To make the filter available the next time you open the object, save the object before closing it.
Saving a Table When you save a new table for the first time, give it a name that describes the information it contains.
You can use up to 64 characters (etters or numbers), including spaces.
Type command located on the More Fields menu inthe Add & Delete group on the Table Tools Fields tab.
You first need to select a field or combination of fields from the open table to execute this command.
When planning a database, the first step is to consider the purpose of your database. You need to design the database so that it accommodates all your data-processing and reporting needs.
You should gather and organize all the information that you want to include, starting with any existing forms or lists, and think about the reports and mailings you might want to create using the data.
18. CLOSE the Benefit
_Providers table
To create a new field in table Datasheet view, you can simply choose from commonly used fields in the Add & Delete group, or click the More Fields button to access a menu with
a greater variety of field types. To create a new field using Quick Start, click the More Fields button and then choose a Quick Start from the menu.
6. Click the down arrow beside the without a header section if the without
a header section option doesn't appear, click the More button) and then select with a header section from the drop-down menu.
3. On the Database Tools tab, in the Analyze group, click the Analyze Table button. The Table Analyzer Wizard dialog box, screen 1, appears. This first dialog box provides more information
about the types of problems the wizard will find. Click the two Show me an example buttons to read more about how duplicating information can lead to problems.
Backstage view • Account: Use this option to view and modify user account settings, change application background and theme,
add a service, manage your Office 365 account, update office, and learn more about Access.
Default Value Automatically assign a default value to a field when new records are added. Validation Rule Supply an expression that must be true whenever you
add or change the value in this field. Validation Text Enter text that appears when a value violates the Validation Rule. Required Require that data be entered in a field.
10. Click Finish to display the results of the crosstab query, a Take Note A crosstab query does not always populate
all the fields in the result set because the tables used do not always contain values for every possible data point.
15. CLOSE the Sales Reps Split form
and the Sales Reps table.
4. Click the down arrow in the Tables/Queries box
and then click Table: Doctors.
To sort text, numbers, dates, or other data types in a column, you first need to select the column,
and then click the Ascending or Descending button in the Sort & Filter group of the Home tab.
5. Click the Field cell in the first blank column (to the right of the TradeInValue field
and then click the Builder button in the Query Setup group to open the Expression Builder dialog box.
3. Click the Close button in the Relationships group on the Ribbon to close the Relationships window,
and then click the Close button on the Employees tab to close the Employees table.
7. Press and hold the Shift key
and then click the Description, In Stock, and Price field
7. Click the Group On Sales Person Last Name row again, click More,
and then click the with Contract Amount totaled down arrow.
17. SAVE the query
as Append Query.
9. Click the Button control on the form. On the Design tab, in the Tools group, click the View Code button. The Microsoft Visual Basic for Applications window appears,
as shown in . If you're fluent in VBA, you can use this window to add VBA code to customize the function of this control.
6. In the blank area of the dialog box, type the following or use the Expression Elements to create the following formula,
as shown in Figure 12-35: Flag: AskingPrice] TradeInValue] <4000 And Mileage] <20000) Or AskingPrice]- TradeInValue] >4000 And Mileage] >20000)
2. On the Database Tools tab, in the Relationships group, click the Object Dependencies button. The Object Dependencies task pane displays dependency information for the selected table,
as shown in Figure 15-15. Widen the pane if necessary. Notice that the Objects that depend on me option button is selected.
3. Click the Print Preview command. The Print Preview screen appears with a preview of the form,
as shown in Notice the form's field borders exceed the width of the right margin.
16. In the Save As dialog box, type Subquery
as the query name and then click OK.
4. In the same manner, reduce the size of the Investment control until its right side is
at the horizontal ruler's 2 1/2" mark and its bottom is at the vertical ruler's 1 1/2" mark.
15. Click and drag the ID field from the Field List pane to any
blank spot on the design grid above the Description field.
Take Note You can customize the Ribbon to have greater control over the commands that appear on it
by turning off tabs and groups you rarely use, moving and/or duplicating groups from one tab to another, creating custom groups, and even creating custom tabs
Take Note Data type templates can be created when a table is open
by using the Save Selection as New Data
Deleting a Field Before you delete a column from a datasheet, remember that doing so deletes all the data in the column and that the action
cannot be undone. For that reason, you should back up the table before you delete the column.
Take Note The process for changing a control's default label name for a report or a form is the same. Once shown how to
change a label name for a report control, you can change a label name for a form control in the same manner.
14. On the Print Options tab,
change the Left margin from 0.333 to 0.25
Use Application Parts to Create Blank Forms GET READY. USE the Contoso-final database that is open from the previous exercise.1. On the Create tab, in the Templates group,
click the Application Parts button and then select 1 Right. A new form object named SingleOneColumnRightLabels appears in the Navigation Pane in the Unrelated Objects category.
11. On the Format tab, in the Number group,
click the Apply Currency Format command.
Sort Data within a Table 6. Select the Last Name field. On the Home tab, in the Sort & Filter group,
click the Ascending button. The data in the Last Name field is sorted in ascending order.
2. On the Design tab, in the Controls group,
click the Button button a rectangle with xxx in it)
8. On the Design contextual tab, in the Header/Footer group,
click the Date and Time button. The Date and Time dialog box appears,
7. On the Design tab, in the Query Setup group,
click the Delete Columns button. The Model column is deleted.
7. On the File tab, on the Info menu,
click the Encrypt with Password button. The Set Database Password dialog box appears.
5. On the Home tab, in the Sort & Filter group,
click the Toggle Filter button to remove the filter.
10. Scroll down to the Split Form Orientation property,
click the down arrow and then select Datasheet on Top
Change Views 3. On the Design tab, in the Views group,
click the down arrow on the View button and then click Datasheet View.
Change Views 1. On the Home tab, in the Views group,
click the down arrow on the View button.
These pieces of information will eventually become your fields columns), and each record row) will then
contain complete information about each student.
The maximum length for the Validation Rule property is 2,048 characters. For example, if the field
contains a date, you can require that the date entered in the field be later than June 4, 1977.
Create a Multivalued Lookup Field 3. Click Next to accept the default settings and display the next screen in the Lookup Wizard. The Available Fields scroll box
contains all the fields of the Employees table, two of which you will select since they contain the values you want to eventually look up.
Take Note Charts and graphs are terms used synonymously in Microsoft Access. For example, the Chart
control inserts a chart into reports or forms; however, a chart is referred to as a graph in Visual Basic for Applications VBA) code.
2. Click the In Stock
control to select it.
5. Click the Description
control to select it.
14. Type the following
data in a new row
Database Essentials
database
4. Click the Save button to accept the generated filename and save location. You could also navigate to an external drive to save the
database to an external location to increase its physical security. You can recover the data by simply opening the database backup file you just created
3. Click the Next Record navigation button to
display the next record in the Form view.
12. Click the Preview Chart button. The Sample Preview dialog box appears,
displaying a sample of your chart.
An input mask is helpful because it can prevent users from entering invalid data such as a phone number in a date field). In addition, input masks can ensure that users
enter data in a consistent way. In this exercise, you specify that the dates in the Start Date field be entered in Medium Date format, following the required pattern, 27-Sept-69.
11. Type the following expression in the Zoom dialog box, , to have the query eventually return all car makes that have an asking price greater or
equal to the average asking price for all car makes: >=(SELECT Avg(AskingPrice) FROM Inventory WHERE Make = Inventory.Make)
Take Note The part of the expression that reads [Inventory]![TradeInValue] specifies that the TradeInValue field originates from the Inventory table; however,
even though Access automatically formats it this way, this expression format is not required since you're already referencing the Inventory table in the Table row of the design grid.
Modify Table Properties 7. Type Most popular
events for 2016.
5. In the Field List pane,
expand the Doctors table.
6. Right-click the document tab (titled Query1
for the open query and then choose Design View on the shortcut menu.
Data Type Description OLE Object Photo Stores images, documents, graphs, and other objects
from Office and Windows-based programs.
4. Click the Save As button. The Create New Template
from This Database dialog box appears,
The Ribbon and dialog box launcher 8. Double-click the Home tab. Notice the groups are hidden to
give you more screen space to work with your database.
3. Position the mouse pointer on the design
grid and drag down and to the right to create
4. Move the insertion point above the Trim field on the design
grid until it turns into a bold down arrow. Click to select and highlight the Trim column.
8. Click the Total On menu down arrow, select Contract Amount, and then click the Show subtotal in
group footer box. The settings are applied, and the subtotals are now shown in each group's footer.
14. With the controls and labels still selected, on the Arrange tab, in the Sizing & Ordering
group, click the Align button and then select Right from the menu that appears. The labels are right-aligned to the controls.
1. On the Design tab, in the Controls
group, click the Text Box button.
13. On the Design tab, in the Results group, click the lower
half of the View button and then click SQL View to see the entire expression,
When you want to remove a sort order, click the Remove Sort button from the Sort & Filter group on the Home tab. T
his removes the sorting commands from all the fields in the table. In a table with more than one sorted field, you cannot remove just one sort.
Take Note To select more than one field to freeze or hide,
hold down the Shift key while selecting adjacent fields.
stating that referential integrity is being violated and will not permit the operation. You can also choose one or both types of cascade options—cascade update related fields or cascade delete related records—
in the Edit Relationships dialog box once referential integrity has been selected. For example, if the cascade update related fields option is selected,
Create a Subform 1. On the Create tab,
in the Forms group, click Form Wizard.
11. Click the More button next to Group
in the Group, Sort, and Total pane.
Save Import Specifications 1. On the External Data tab,
in the Import & Link group, click Text File to open the Get External Data - Text File dialog box.
6. Type Britta Simon
in the Manager box.
5. Type Photo Exhibit
in the Name box.
2. Double-click the Income & Expenses report
in the Navigation Pane to open it.
Insert a Total Row 1. Double-click the Book Sales table
in the Navigation Pane to open it.
Arrange Control Layout 1. Click the Inventory table
in the Navigation Pane to select it.
5. In the Copies section of the Print dialog box, click the up arrow
in the Number of Copies menu to change the number of copies to 2.
8. Type $Fabrikam09fc
in the Password box.
Create a Join 1. On the Create tab,
in the Queries group, click Query Design.
21. On the Create tab,
in the Reports group, click Report.
11. On the Design tab,
in the Results group, click Run.
8. On the Design tab,
in the Results group, click Run.
Modify Table Relationships 3. Select the Customer ID field in the first table. Drag the mouse to the Customer ID field
in the second table and release the mouse button. The Edit Relationships dialog box appears.
2. Press the Tab key several times to see the order
in which the controls are selected each
Create Fields 6. In the Quick Start category, scroll down if necessary and click Status. A new Quick Start field named Status,
in which you now have options to indicate the status of an event, appears to the left of the On-site staff? field.
Create a Table Using the Application Parts Gallery and Quick Start 2. In the Quick Start section of the gallery, click Comments. If you are prompted to close all open objects before
instantiating this application part, click Yes in the dialog box that appears. Notice the Comments table appears as a new object in the Navigation Pane.
3. Click the In Production field in the Field List pane. Drag it to the grid and place it in any
location on the design grid to the right of the four controls.
UNDERSTANDING TABLE RELATIONSHIPS As you have already learned,
most databases have more than one table. Creating relationships
12. Click the Bottom option button to
move the legend to the bottom of the chart.
6. Double-click Company and Referred By to
move them to the Additional query fields box
4. Scroll down to view the
multiple records on the form.
Data Type Description Hyperlink Web addresses Stores links to websites, sites or files on an intranet
or Local Area Network (LAN), and sites or files on your computer.
Setting Data Validation Rules Validation rules help ensure that your database users enter the proper types
or amounts of data. A validation rule is an expression that limits the values that can be entered in the field.
6. Click OK to print two copies of the report,
or click Cancel if you choose not to Print).
Create Fields 1. On the Home tab, in the Views group, click the bottom half of the View button and then click Datasheet View. Save the table, if required. If you get a message about data integrity, click Yes. Take Note Whenever you add
or modify field validation rules for fields that contain data, the data may violate these new rules. You can allow Access to test the data against the rules and inform you if there are any violations.
Database Essentials
or removable
Take Note As part of the Access 2016 security model, when you open a database outside of a trusted location
or the database contains active content (content that can be misused by an unscrupulous person to cause harm to your computer),
Take Note To add more than one field at a time after adding at least one
other field, press Ctrl and click several fields, and then drag them all onto the form at once.
Data Type Example Description Attachment Any supported type of file You can attach images, spreadsheet files, documents, charts, and
other types of supported files up to 2 GB per record) to the records in your database, much like you attach files to email messages.
15. Click and drag the ID field from the Field List
pane to any blank spot on the design grid above the Description field.
11. In the Criteria row of the City field, type City?]. This will create the
parameter and require you to enter a city name when the query is run.
4. In the Datasheet view on the bottom,
place the insertion point in the Mobile Phone field for Nancy Buchanan. Notice that the same field is selected in the Form view at the top.
For example, say you need to track orders placed by a distributor. To do that, you add a table named Distributor Contacts to a sales database. To take advantage of the
power that a relational database can provide—to search for the orders placed by a given contact, for example— you must create a relationship between the new table and any tables that contain the order data.
Navigating Using the Keyboard Access users who prefer using the keyboard to navigate records can
press keys and key combinations to move among records in Datasheet view.
OrderAmount: Quantity] * Price] Creates a field called OrderAmount, and then displays the
product of the values in the Quantity and Price fields.
Set a Field Property in Datasheet View and Design View 6. In the Default Value row in the lower portion of the table design grid, click in the
property box and then type To Be Announced to specify a value that will be automatically entered in this field for new records.
5. Click and drag, resizing the column to
remove excess white space.
The following is a summary of the first three normal forms: • First Normal Form 1NF): This form divides each field according to its smallest meaningful value,
removes repeating groups of data, and creates a separate table for each set of related data.
Set Data Validation Rules 4. Select the number 1900 and
replace it by typing 2016.
17. Type Corporate Sales by Region/Salesperson as the title of your Report,
replacing the default title of Corporate Sales1.
7. CLOSE the
report and table.
Take Note A subreport can also be added to a report in a similar way and serves a similar function as a subform. The determination to add a subform or subreport to a
report depends mainly on what form or report objects you already have in your database and what additional data you want to communicate.
2. Double-click the Toy Summary
report in the Navigation Pane.
11. CLOSE the
report.
7. Click the Auto Order button. The order of the fields automatically
resets based on the order that they appear on the form or report.
7. Change to Design View, and on the Doctors Form Tab Layout View,
resize the columns in the Samples Given section so they all fit mostly in the window
the Backstage View 13. Click the Cancel button to close the Access Options dialog box and
return to the Access workspace.
7. Select the In Production control,
right-click and then select Delete from the menu.
7. Select the In Production control that you just added,
right-click it and then select Delete from the shortcut menu.
4. Click the down arrow in the Domestic Sales column of the Total
row and then select Sum from the menu, as shown in Click Sum to apply it.
8. Type <#3/31/2016# in the Criteria
row of the Date Sold field
8. Type 2012 in the Criteria
row of the Year field.
• Sqlstatement: A SELECT statement, following the
same format and rules as any other SELECT statement. It must be enclosed in parentheses
9. When the Save Import Steps
screen appears, click Close.
8. Click Next to accept the default settings and display the next
screen where you can define a primary key. Leave the default selection that lets Access add the primary key.
9. Select the Green color
second row, fourth from the left)
As you create each table, keep in mind how the data in the tables are related to each other. Enter test data and then add fields to tables or create new tables as necessary to refine the database. The last step is to apply data normalization rules to
see if your tables are structured correctly and make adjustments as needed. Normalization is the process of applying rules to your database design to ensure that you have divided your information items into the appropriate tables.
6. Change to Form view to
see the changes you made.
11. Click the down arrow on the first Grouping intervals menu to
see the choices available. Select Normal from the menu and then click OK.
Set a Field Property in Datasheet View and Design View 8. In the Field Size row in the lower portion of the table design grid,
select 150 in the property box and then type 175 to change the maximum number of characters you can enter in the Title field.
13. Click the down arrow on the Sort menu and then
select Company Name to sort in ascending order by Company Name.
7. Name the query Employees Contact Query and then
select Open the query to view information if it is not already selected.
11. Click the down arrow on the As menu,
select Report, and then click OK.
Rename a Table 2. In the Create Relationship dialog box that appears,
select There is no relationship and then click Create.
4. Click the Font name: down arrow and scroll down to
select Times New Roman. Notice the preview sample displays the new font.
4. Select the Export data with formatting and layout check box and then
select the Open the destination file after the export operation is complete check box.
Creating a Table from Another Table 3. In the Paste Options section,
select the Structure Only option button to paste only the table's structure.
Create a Table Using the Application Parts Gallery and Quick Start 6. In the Create Relationship dialog box that appears,
select the There is no relationship option button and then click Create. A new table is created along with supporting forms and report objects, as shown
2. In the first row, in the Income column,
select the data, type 9004523, and then press Enter].
You can modify a primary key by deleting it from one field and adding it to another field. To remove a primary key in Design view,
select the row and then click the Primary Key button on the Design tab in the Tools group on the Ribbon to remove it.
7. Press and hold the Shift key and then click each of the four controls to
select them all. Be sure you select the controls and not the labels,
3. Click the down arrow beside with a header section and then
select without a header section from the drop down menu,
4. In the Tables/Queries drop-down list, Table: Employees should be
selected by default. If it is not, select it.
Creates a field called Address2 that displays the values in the City, Region, and PostalCode fields,
separated by spaces. "&" is called the concatenation operator and is used to put strings together.)
Apply a Filter 12. Click OK. Access filters the records to
show only those containing the number 25 in the Pounds field.
2. Click the Show all tables link in the Field List pane to
show the Photo Exhibit table name, if necessary.
You can trust the databases downloaded from the book companion website,
so click Enable Content on the Message Bar for all databases in this book
Take Note Some object definitions can be several pages long,
so it is a good idea to check the length of the report before printing.
7. Select Room Name from the fields menu to
sort in ascending order and then click the Next button.
CategoryID value matches the CategoryID value
specified in an open New Products form.
Database Essentials
start screen
9. On the Save Export Steps screen, select the Save export
steps check box to display the specification details.
15. Click OK. Make sure your cursor is
still in the Markup in Euros cell.
10. Click the Next button. Access has
suggested titles for the forms Doctors and Samplesm Given Subform). Keep the default selection to open the form.
Remember these points when finding and replacing data in Access 2016 Type a question mark ?) to represent a single character—for example, typing b?
t will find bat, bet, bit, and but. Type an asterisk *) to represent astring of characters—for example, m*t will find mat, moment, or even medium format.
Take Note If the relationship is one-to-many, Access displays a "1" above the join line to show which table is on
the "one" side and an infinity symbol ∞) to show which table is on the "many" side.
Create a Multivalued Lookup Field 4. In the Available Fields list, select Last Name and then click
the > button to move it to the Selected Fields box.
Create a Multivalued Lookup Field 5. In the Available Fields list, select First Name and then click
the > button to move it to the Selected Fields box. The results are shown
16. Select the State field and then click
the > button. Press the Spacebar.
15. Select the City field and then click
the > button. Type, a comma) and then press the Spacebar.
5. Click Stamford Employees and then click
the Add button. The table is added to the query.
9. Click OK. Access automatically adds the names of the destination fields that match the source field names to
the Append To row in the design grid. Because the Asking Price field doesn't have a match, Access leaves that field blank.
13. On the Format tab, in the Number group, click
the Apply Currency Format command.
Take Note In Layout view, you can also use the tools on
the Arrange tab to arrange controls within a layout for both forms and reports.
9. Click the down arrow in the Total cell of
the AskingPrice field and then click Maximum.
13. Type Darien in
the City? box.
2. In the Navigation Pane, double-click
the Corporate Sales table to open it
2. In the Navigation Pane, double-click
the Doctors table to open it.
4. Double-click Last Name, First Name, and E-mail Address to move them to
the Duplicate-value fields box. These are the fields that you think may include duplicate information.
9. In the Navigation Pane, double-click the Non-Stamford Employees Query under
the Employees section to open it.
Result Type Specify the data type to be used to store the value generated by
the Expression field property for the Calculated data type.
2. Change to Layout view and close
the Field List pane if it opens.
9. Move the insertion point above the Model field on the design grid until it turns into a bold down arrow. Click to select and highlight the Model column. Click and hold the mouse button down and drag
the Model field to the left until the black vertical placeholder bar is positioned between the Make and AskingPrice fields and then release the mouse button
1. Double-click the Photo Label form in
the Navigation Pane to open it in Form view.
1. Double-click the Rooms report in
the Navigation Pane to open it.
17. Double-click the Toys By Quantity In Stock report in
the Navigation pane and change to Design view.
You can display the Print Preview tab by choosing an object you want to print from the Navigation Pane, and then selecting
the Print Preview option on the File tab's Print menu. A report already has a Print Preview option on its View menu, so you can simply select that option to take you to the Print Preview tab.
18. In the Report Header section, click
the Print Report Button control to select it.
8. Press F4 to display
the Property Sheet.
6. Click the Close button in
the Show Table dialog box.
6. Click the Next button. The second screen in
the Simple Query Wizard appears.
5. CLOSE
the database.
4. Select the Open the destination file after
the export operation is complete check box.
7. Go back to Access. A message confirms that
the export operation was successful.
5. Click the Next button to move to
the final page in the Form Wizard.
2. Add another record with
the following information:
13. CLOSE
the form
Note: "Forms.[New Products].[Category ID]" references the New Products form and the CategoryID field on
the form in case a New Products table and field also exist. The "." character is used to substantiate these form and field relationships.
4. In the Page Layout group, click the Landscape button to display
the form in landscape view. Notice some of the form's field borders still exceed the width of the right margin.
10. Click the Investment control that contains
the formula you entered in an earlier exercise.
11. Select all the controls in the stacked layout
the labels and controls for the Description, In Stock, Price, and In Production controls).
Enter Captions 2. Click the Caption property box in
the lower portion of the table design grid.
Set Data Validation Rules 6. Click the Validation Text property box in
the lower portion of the table design grid.
11. Change to Print Preview view. Notice the report layout still appears on two pages. The Remove Extra Report Space didn't entirely fix the issue. The page number control is exceeding the width of the page in Design view as evident by
the page numbering text appearing in the footer section of the second page. On the Print Preview tab, in the Page Layout group, click the Landscape button. The report is displayed in landscape orientation and the formatting appears correctly.
8. Click the Next record button on
the record navigator at the bottom of the form. Continue clicking to see the three photos that use print media and are less than $500.
15. CLOSE
the report
16. SAVE
the report
6. SAVE
the report
4. Click the Income & Expenses report tab. Notice that the numbers in
the report data and the numbers in the chart have not changed.
13. CLOSE
the report.
14. CLOSE
the report.
14. SAVE
the report.
16. CLOSE
the report.
20. SAVE
the report.
27. SAVE
the report.
6. SAVE
the report.
7. SAVE
the report.
9. Click and drag the label to position it about one-half inch to
the right of the Room ID field and release the mouse button. The field is moved along with the label.
2. Right-click the Referred By field and then select Sort A to Z from
the shortcut menu that is displayed. The field is sorted in alphabetical order from A to Z.
2. Click the file path and edit it by changing
the source file name to march
15. SAVE and CLOSE
the spreadsheet.
Freeze/Unfreeze and Hide/Unhide Fields 6. Click the More button again and
then select Unfreeze All Fields. Notice how the Last Name field remains in the table's first field position.
4. Select the Export data with formatting and layout check box and
then select the Open the destination file after the export operation is complete check box.
13. Click the with Z on top down arrow and
then select with A on top.
3. If you want to specify a different destination, click Browse
to open the File Save dialog box. Choose a folder and then click Save.
15. Click the In Stock field control and increase its height by positioning the pointer over the top-right corner of the control
until a diagonal two-sided arrow appears, and then clicking and dragging upward until the text is no longer obstructed.
Click to access
web app database
Take Note The arrow points up or down depending on
whether the data is sorted in ascending or descending order.
8. Click the Close button on Photo Details to close the form. PAUSE. LEAVE the database open to use in the next exercise. Take Note To include fields from more than one table on your form, do not click Next or Finish after
you select the fields from the first table on the first page of the Form Wizard. Instead, repeat the steps to select another table and then click any additional fields that you want to include on the form before continuing.
Troubleshooting If the table with the name you specified already exists, you are asked if
you want to overwrite the existing file. Click Yes if you want to overwrite the file; click No to specify a different filename.
To set field properties in Design view, open the table in Design view. In the upper portion of the table design grid, click the field for which
you want to set properties. The properties for this field are displayed in the lower portion of the table design grid.
7. Select the Income & Expenses Summary table as
your data source and then click the Next button. The second Chart Wizard dialog box appears.
Take Note All the list items in the Expression Categories on
your screen may not match exactly to the figure shown.
The following additional aggregated functions are available in Design view:
• Expression: Groups data based on an expression you can specify
The following additional aggregated functions are available in Design view:
• First: Finds the first value in a field
The following additional aggregated functions are available in Design view:
• Last: Finds the last value in a field
8. On the Save Export
Steps screen, click Close.
For example, if you want to sort a contact list so that each employee's last name is sorted primarily and first name is sorted secondarily,
Last Name would be the outermost field and First Name would be the innermost field.
Fields inserted in
Layout view
Commands Results
Up Arrow Moves cursor to the current field in the previous record
7. Go back
to Access.
8. Go back
to Access.
5. Change back
to Layout view.
4. Double-click Dimensions
to add it to the form.
5. Double-click the Price field
to add it to the form.
Enter, Edit, and Delete Records
9. Press Tab. Type 08212016 and then press Tab.
Use the Keyboard to Navigate among Records
9. Press the Tab key to move to the next cell.
STOP.
EXIT Access.
• Expression: An expression for which the result
set of the subquery is searched
17. SAVE the form as Modified Inventory
Form and then CLOSE it.
9. Select Clear Filter from Bed Size
from the menu. The filter is cleared.
8. Click OK and then click Close to close
the Manage Data Tasks dialog box.
Database Essentials
recently
7. Right-click the Inventory Query document tab and
then choose Design View to display the query in Design view.
8. Click the Sort cell in the second column, click the down arrow and
then click Ascending on the drop-down menu.
Allow Zero Length
4. Click Yes
18. CLOSE the
Append Query query.
3. Click
Close.
5.CLICK THE
OK BUTTON.
2. CLICK
THE LOOKUP TABE
Extended] WHERE
[ExtendedPrice] > 1000;
2. In the first screen on the Form Wizard, click the down arrow in
the Tables/Queries box and then click Table: Samples Given.
2. Select the Rooms table in
the Tables/Queries menu.
GET READY. USE the Wingtip Inventory-final database and
the Toy Summary report that are open from the previous exercise.
12. Click the Close button to close
the Used Cars Sold QueryFilter1 query.
5. Click Next again to accept the default Detail and
then click Finish to display a simple select query.
12. SAVE
the report
8. Right-click in the white area of the Navigation Pane, click View By and
then click List in the shortcut menu to display the database objects in a list again.
Create a Simple Query 1. OPEN the Northwind file from the data files for
this lesson and then SAVE the database as Northwind-final.
Create a Chart 1. OPEN Blue Yonder Airlines from the data files for
this lesson and then SAVE the database as Blue Yonder Airlines-final.
1. In the Navigation Pane, double-click
Students to display the table in the Access work area.
Open an Existing Database
The Access startup screen should be on the screen from the previous exercise.
For example, entering a rule such as .1 and ,100 in the Validation Rule property forces users to enter values between 1 and 100. Entering text such as
"Enter values between 1 and 100" in the Validation Text property tells users when they have made a mistake and how to fix the error.
7. On the Home tab, in the Sort & Filter group, click the Selection button, and then select Equals
"King" from the menu. The data is filtered to show only the rooms with Kingsized beds.
Save a Table
10. Click Save As to display the Save As screen.
Database Essentials
Create a
Active tab Dialog box launcher
The Ribbon and dialog box launcher
3. Click the Format menu and then click Selected Chart Area.
The Format Chart Area dialog box appears.
8. Choose Format Data Series from the shortcut menu.
The Format Data Series dialog box appears.
Use Navigation Buttons 5. Press Enter.
The selection moves to the next occurrence of the word
20. Click the
chart to select it.
5. Right-click the Room Name header.
The shortcut menu appears.
To delete a field in Datasheet view, select the column, right-click, and then click Delete Field from the shortcut menu. Or, on the Table Tools Fields contextual tab in the Add
& Delete group, click the Delete button. You will see a confirmation message asking if you are sure you want to delete the column and all the data.
Example using the concatenation operator
&): First: John Last: Derenzo FullName stored as First]&" "&Last]
FullName: FirstName] & " " & LastName] Creates a field called FullName that displays the values in the FirstName and LastName fields, separated by a space.
("&" is called the concatenation operator and is used to put strings together. Address2: City] & " " & Region] & " " & PostalCode]
7. CLOSE the Doctors Multiple
form and the Doctors table. PAUSE. LEAVE the database open to use in the next exercise.
19. SAVE the
form as Inventory.
10. SAVE the
form design.
4. Click the Save button. Notice the filename and
format change is displayed in the title bar,
11. Enter the remaining fields, as shown in
formatting each with the Currency data type and 0 decimal places.
the Backstage View 6. Select Access Database
*.accdb) if it's not already selected.
2. In the New Query dialog box
, click Simple Query Wizard and then click OK.
Create Conditional Formatting
1. Double-click the Toy Summary report in the Navigation pane to open it, if necessary, and then change to Design view.
HOW TO: EDIT PROPERTIES OF A LOOKUP FIELD
1. CLICK TO SELECT THE DESIRED FIELD
Review and Modify Data Types for Fields
1. CLOSE the Student List form.
Change Grouping Options
1. Change to Layout view.
Create Aggregate Fields
1. Change to Layout view.
Use the Label Wizard
1. Select the Clients table in the Navigation Pane.
Use the Keyboard to Navigate among Records
12. Press Ctrl+Down Arrow to move to the first field of the last record
Attach and Detach Documents
13. CLOSE the Order Summary table.
Attach and Detach Documents
10. Click the Close button to close the invoice document and return to Access.
Find and Replace Data
10. Click the Find Next button. Access finds Elm in the new text that was just inserted.
Enter, Edit, and Delete Records
10. Click the Record Selector box to the left of the Product ID field of the first record, 20051.
Apply a Filter
10. On the Home tab, in the Sort & Filter group, click the Filter button.
Use the Keyboard to Navigate among Records
2. Click the File tab and then click the Save As option on the left side of the Backstage view.
Sort Data within a Table
2. Click the header row of the Customer ID field to select it.
5. Click the Location field to select it and then click the
> button to add it as a grouping level
7. Select the Contract Amount field and then click the
> button to move it to the grouping levels box.
8. Select the Sales Person Last Name field and then click the
> button to move it to the grouping levels box.
2. SELECT THE TEMPLATE YOU WISH TO USE
A NEW DIALOG BOX DISPLAYS WHERE YOU CAN ENETR INFORMATION ABOUT THE DATABASE NAME AND LOCATION.
7. Click Next to display
the next screen.
5. SELECT THE RELATED TABLE
AND CLICK NEXT
5. SWELECT THE RELATED TABLE FROM THE "ONE" SIDE OF THE RELATIONSHIP
AND CLICK NEXT TO MOVE TO THE NEXT STAEP OF THE LOOKUP WIZARD.
3.CLICK THE DATA TYPE PROPERTY BOX, CLICK THE DOWN ARROW TO DISPLAY THE LIST OF DATA TYPES
AND THEN SELECT THE LOOKUP WIZARD DIALOG BOX OPENS
When you create a new database,
Access creates a primary key field named "ID" by default and sets the data type for the field to AutoNumber.
Creating Fields Fields can be created in different ways. You can add fields to a table in Design view, or add fields in Datasheet view using the Click to Add column or
Add & Delete group. Sometimes it is easier to choose from a predefined list of fields than to manually create a field.
10. On the Home tab, in the Sort & Filter group, click the
Advanced button and then click Save As Query. The Save As Query dialog box appears.
Deleting a Table Deleting an entire table is not a complex process; however, remember that when you delete an entire table you might break the functionality of your database.
Although you will be asked to confirm the deletion of a table, you can always undo the action. In this exercise, you delete a table.
8. CLOSE the
Applicants table
28. SAVE the form as
Arranged Inventory Form.
13. LAUNCH Excel and then OPEN the
Benefit_Providers spreadsheet.
Database Essentials
Browse PC
The Edit Relationships dialog box appears to Define Table Relationships 6. Select the Enforce Referential Integrity, Cascade Update Related Fields, and
Cascade Delete Related Records check boxes.
Data Type Example Description The most common data type for fields.
Can store up to 255 characters of text, and numbers or a combination of both).
New Values Set whether an AutoNumber field is incremented or assigned a random number. Input Mask Display editing characters to guide data entry.
Caption Set the text displayed by default as the column name in tables and labels for forms, reports, and queries.
Delete a Table 2. A confirmation message appears.
Click Yes to delete the table.
Apply a Filter 13.
Click the Toggle Filter button.
Rename a Table 4. CLOSE the
Comments table.
The selected field, Terms, is a lookup field. Modify the lookup field properties so data entry is limited to items on the list.
Correct In the Access Table, you clicked the Lookup tab, clicked the Limit To List property box, clicked the Drop-Down Button, and clicked the Yes Item. Add the OpenQuery action to this macro. It is located in the Filter/Query/Search folder. Set Tuition as the query to open when the macro is run.
Click to access
Create a new blank
Click to access
Create a new custom
The following is a summary of the first three normal forms: • Second Normal Form 2NF): With this form, each non-key column should be fully dependent on the entire primary key.
Create new tables for data that applies to more than one record in a table and add a related field to the table.
10. Click OK in the Format
Data Series dialog box.
6. Click Close to close the Manage
Data Tasks dialog box.
The Ribbon and dialog box launcher 3. Click External Data and then click
Database Tools to see the commands available on those tabs.
SOFTWARE ORIENTATION Add & Delete Group When creating fields in table
Datasheet view, you use the Add & Delete group on the Table Tools
Create a Multivalued Lookup Field 1. Place the insertion point in the first cell of the table. Click the More Fields button in the Add &
Delete group on the Table Tools Fields contextual tab and then click the Lookup & Relationship button. The Lookup Wizard appears.
7. CLOSE the Object
Dependencies task pane.
Delete a Field 1. Click the column header for the Attachment field, located between the
Description field and the Status field.
SELECT [CategoryID], Count ProductID]) AS CountOfProductIDc FROM Products] GROUP BY CategoryID] HAVING Count ProductID]) > 10;
Displays the total number of products for categories with more than 10 products in a field named CountOfProductID
LECT statement Result SELECT FirstName], LastName] FROM Employees] WHERE [LastName] = "Cooper";
Displays the values in the FirstName and LastName fields for employees whose last name is Cooper.
6. In the Save As dialog box, type
Doctors Multiple and then click OK.
9. SAVE and CLOSE the
Doctors Navigation Form.
9. Under Available Fields, double-click Position and
E-mail Address to move them to the Selected Fields box.
If you find yourself adding extra information, create a new table. In a database table, data is stored in rows and columns—similar in appearance to a spreadsheet. Each row in a table is called a record.
Each column in a table is called a field. For example, if a table is named "Student List," each record row) contains information about a different student and each field (column) contains a different type of information about a student, such as last name or email address.
SELECT Avg
ExtendedPrice]) AS [Average
8. SELECT ANY SORT OPTION
FOR THE LOOKUP FIELD.
4. Click Close on the
Field List pane.
• Data types: When you design a database table, you define a data type for each field in the table, and that data type restricts what users can enter
For example, a Date/Time field accepts only dates and times, a Currency field accepts only monetary values, and so on.
In a simple database, you might have only one table. Most databases, however, will have more than just one table. The tables you include in a database will be based on the data available.
For example, a database of students might have a table for contact information, a table for grades, and a table for tuition and fees.
Lastly, you can even save some objects as other compatible object types.
For example, you can save a table as a new report which will, by default, include all table fields.
Create a Table from Another Table
GET READY. USE the BlankDatabase database that is still open from the previous exercise.
4. SELECT I WANT THE LOOKUP FIELD TO GET THE VALUES FROM ANOTHER TABLE OR QUERY RADIO BUTTON
IF NESSARY AND CLICK MOVE TO THE NEXT STEPOF THE LOOKUP WIZARD.
11. CHANGE THE FIELD LABEL AS DESIRED. THE LABLE DEFAULTS TO THE ORIGINAL FIELD NAME OF THE FOREIGN KEY FIELD
IF YOU CHANGE THE LABLE CONTENTS, YOU ALSO CHANGE THE NAME OIF THE FIELD IN THE TABLE.
4.SELECT TEXT BOX
IN THE DISPLAY B-CONTROL PROPERTY.
7.ENABLE CONTENT
IN THE DISPLAY.
4. TYPE THE FILE NAME
IN THE FILE NAME AREA.
7. MOVE THE ADDITIONAL FIELDS INTO THE SELECTED FIELDS WINDOW
IN THE ORDER YOUY WISH TO DISPLAy the FIELDS.
11. Change to Form view and then click the newly created Close Form button to close the form.
If a dialog box appears asking you if you want to save the changes to the form, click Yes
11. Click the Close button on the Industry Friends Query tab to close the query.
If a message asks you if you want to save the changes, click Yes.
8. In the Table Name box, type Sales Team Backup.
If it isn't already selected, click Current Database and then click OK.
13. Click Save
Import.
9. In the Expression Elements category, click the expand button next to
Importers-final. accdb. Tables, Queries, Forms, and Reports should appear under Importers-final.accdb.
18. Change to Form view, scroll through the records, and modify the
In Production field by selecting an item in the combo box for one or more records.
7. Double-click the Toys By Quantity
In Stock report to open it.
Access uses the field property settings when you view and edit data. For example, the Format, Input Mask, and Caption properties affect how your information appears in table and query datasheets.
In addition, any controls on new forms and reports that are based on the fields in the table inherit these same property settings by default.
7. On the Standard Types tab, click Pie in the Chart type list.
In the Chart sub-type section, click Pie with a 3D visual effect, the second icon on the first row
8. In the Layout section, click the Outline button.
In the Orientation section, click the Landscape button
For each table, you will choose a primary key. A primary key is a column that uniquely identifies each row, such as ID Number.
In the case of our List table, the primary key ID Number) uniquely identifies each.
Delete a Field 5. Right-click in the column to display the shortcut menu and then click Delete Field.
In the message box that appears, click Yes to delete the Status field.
Every field also has an associated group of settings called properties that define the appearance or behavior of the field.
In this section, you learn how to create fields and modify field properties.
7. Click anywhere in the
Industry Friends_1 field list.
ENTERING, EDITING, AND DELETING RECORDS You can delete an entire record or several records at once from a database.
Just select the row or rows using the Record Selector box, and then press the Delete key or click the Delete button on the Home tab in the Records group.
9. Click the Close button on Image Info to close the form. PAUSE.
LEAVE the database open to use in the next exercise.
Subdatasheet Name Specify whether a subdatasheet should appear in Datasheet view, and, if so, which table or query should supply the rows in the subdatasheet.
Link Child Fields List the fields in the table or query used for the subdatasheet that match this table's primary key field s).
Data Type Example Description
Long Text Comments: Student will make monthly payments on the 15th of each month of $247.
Take Note Because you are already working inside the Inventory table, the reference in the expression to the Inventory table preceding the
Make field can be omitted and the expression can also be constructed as follows: >= SELECT Avg AskingPrice) FROM Inventory WHERE Make = Make)
4. In the Inventory field list, double-click Year,
Make, Model, Mileage, AskingPrice, and TradeInValue to add them to the design grid.
5. In the Used Cars Sold field list, double-click Year,
Make, Model, and Sales Price..
Move selected field
Move all fields
11. Type Filter Query in the Query
Name box and then click OK.
3. Type ID in the Field
Name column.
2. Click the Room
Name header.
6. In the Save As dialog box, type Doctors
Navigation Form and then click OK.
5. In the Navigation section, notice that the Display
Navigation Pane is turned on by default.
8. Click anywhere on a blank area of the design grid to deselect the field controls, select the In Production field control and then click Select Row in the Rows & Columns group.
Notice the In Production label and field control are both outlined in orange. In the Move group on the Ribbon, click the Move Up button three times to move it under the ID label and field control,
7. CLICK NEXT TO MOVE TO THE NEXT STEP
OF THE LOOK UP WIZARD.
5. Click Simple Query Wizard and then click
OK to display the Simple Query Wizard.
12. Click
OK.
19. Click
OK.
2. Click
OK.
4. Click
OK.
5. Click
OK.
8. Click
OK.
9. Click
OK.
3. CLICK THE BROWSE BUTTON TO BROWSE TO ANOTHER LOCATION
ON YOUR COMPUTER OR USB DRIVE TO SAVE THE DATABASE.
Database Essentials
One Drive
• Open Exclusive Read-Only:
Opens with only viewing ability and not editing ability. Others can only view and not edit the database.
• Open Read-Only:
Opens with only viewing ability and not editing ability. Others can still
9. On the Home tab, in the Sort & Filter group, click the Advanced Filter
Options button and then click Clear All Filters
17. Press and hold the Shift key and then click both the labels and controls for all the controls. In the Sizing &
Ordering group, click the Align button, and then select Left from the menu that appears. All the controls are left-aligned as a group.
Take Note To change an existing caption, simply follow the same steps as you would for entering a caption.
Overwrite the existing caption with the new caption.
2. In the New Query dialog box, click Simple
Query Wizard and then click OK.
Create a Find Duplicates Query 1. On the Create tab, in the Queries group, click the
Query Wizard button. The New Query dialog box appears.
19. SAVE the query as Calculated
Query and then CLOSE the query.
24. SAVE the report as Calculated
Query and then CLOSE the report.
SOFTWARE ORIENTATION accessed by right-clicking on the Record Selector box), shownUse the commands in the
Records group and the Record shortcut menu, as well as the Record Selector box a blank square to the left of a record), to assist you in entering record data and inserting and deleting records.
2. In the Navigation Pane, click the
Rooms table to select it. This is your record source.
14. CLOSE the Inventory query and then
SAVE the changes when prompted.
15. CLOSE the Used Cars Sold table and Delete Query.
SAVE the changes when prompted.
10. CLOSE and
SAVE the report.
12. The report opens.
SAVE the report.
18. CLICK YES TO SAVE THE CHANGES TO THE TABLE
SO THAT ACCESS CAN CREATE THE RELATIONSHIP
14. CLICK YES TO CHANGES TO THE TABLE
SO THAT ACCESS CAN CREATE THE RELATIONSHIP.
Defining Database Tables
STOP. CLOSE the database and Access
Print Table Relationships 5. CLOSE the Relationships tab.
STOP. CLOSE the database and then EXIT Access
14. SAVE the query as Mileage Query and then CLOSE the query.
STOP. CLOSE the database and then EXIT Access.
24. CLOSE Print Preview and then CLOSE the report.
STOP. CLOSE the database and then EXIT Access.
11. Click
Save Export.
Commands Results
Shift+Tab or Left Arrow Moves cursor to the previous field
SOFTWARE ORIENTATION Navigation Pane
Shutter Bar
Microsoft\Templates\Access\. Others can share these files with you and you can put them in this folder to use them from within Access.
Similarly, you can share your template files with others by providing them with copies from this folder.
11. CLOSE the Used Cars
Sold_Crosstab query
12. Click Finish and when the Save Import
Steps prompt appears, click Close.
15. CLICK NEXT TO MOVE THE LAST STEP OF THE LOOKUP WIZARD
THE LABLEDEFAULTS TO THE ORIGINAL NAME YOU ENTER, YOU CAN CHANGE THE FIELD NAME IF YOU LIKE.
10. CLICK NEXT TO MOVE TO THE NEXT STEP OF THE LOOKUP WIZARD
THE LIST FROM THE CURRENT DATA VALUES FROM THE MATCHING TABLE DIS[PLAYS
9. CLICK NEXT TO MOVE TO THE NEXT STEP OF THE LOOKUP WIZARD
THE LIST OF CURRENT DATA VALUES FROM THE MATCHING LIST DISPLAYS.
6. CLICK THE CREATE BUTTON
THE NEW DATABASE OPENS.
3. SELECT LOOKUP WIZARD IN THE DATA TYPE PROPERTY BOX
TO LAUNCH THE LOOKUP WIZARD
10. ADJUST THE WIDTH OF THE COLUMN AS DESIRED AND CLICK NEXT
TO MOVE TO THE LOOKUP WIZARD
Data Type Example Description AutoNumber Student ID: 56 Unique values created by Access when you create a new record.
Tables often contain an AutoNumber field used as the primary key.
Apply a Filter 14. In the second row of the Scheduled Order Date field, highlight 9/4/2016 by clicking and dragging the mouse.
Take Note Notice a '#' appears after the '9' and the '4' in the month and day parts of the selected date.
6. Right-click in the control and select Delete from the menu.
The control and label are removed from the design grid.
13. Type $Fabrikam09fc and then click OK.
The database opens.
Take Note The Navigation Pane divides your database objects into categories, and those categories contain groups.
The default category is Object Type, which groups database objects by their type—tables, forms, reports, and so on.
The Field List pane appears. Click the Show all tables link.
The fields for the Inventory table appear.
20. Click Next.
The fifth Label Wizard dialog box appears.
2. On the Create tab, in the Reports group, click the Labels button.
The first Label Wizard dialog box appears,
7. Click the arrow on the Font Size menu and select 12 from the menu.
The font size becomes 12.
9. Click Yes on the dialog box asking you if you want to permanently delete the Photo Info form.
The form is now permanently deleted from the database.
5. Click OK to accept the Photo Exhibit form name suggested by Access.
The form name appears in the Navigation Pane.
3. Click and drag the Doctors Report object from the Navigation Pane to the [Add New] tab near the top of the form.
The form tab has been renamed Doctors Report and all the Doctors report controls appear. A new Add New] tab appears next to the Doctors Report tab
Take Note The arithmetic operators were mentioned earlier.
The logical operators are And, Eqv (equivalent),
Create a Subquery 1. On the Create tab, in the Queries group, click Query Design.
The query design grid opens, and the Show Table dialog box appears
For example, the human resources department of a large company could keep a photo, a resume, and employee evaluation documents with each employee record.
These attached files can also be easily detached, if necessary. The Attachments dialog box allows you to manage the documents attached to records.
2. In the Navigation Pane, click the Photo Exhibit table.
This is the table for which you will create a form.
DEFINING DATA NEEDS AND TYPES
To create a database that achieves your goals and provides you with up-to-date, accurate information, you need to spend time planning and designing it.
19. Click the down arrow on the second row of the Correction field and then select the
Tomic correction from the menu, as shown in , to replace the incorrect record. Click to select it.
11. Click the In Stock field control to select it. On the Arrange tab, in the Rows & Columns group, click the Insert Below button twice.
Two empty cells are added under the In Stock control. The In Stock field control should still be selected.
Create a Table Using the Application Parts Gallery and Quick Start GET READY.
USE the BlankDatabase database that is still open from the previous exercise.
Backstage view • Close:
Use this option to close the open database but keep the Access application open.
Backstage view • New:
Use this option to create a new database from scratch or from available templates.
Backstage view • Open:
Use this option to open an existing database and view a list of recently accessed databases.
Backstage view • Save:
Use this option to return to the open database window where objects can be saved.
2. Click the In Stock control. Position the mouse pointer over the resize handle on the right border. The mouse pointer changes to a double-sided arrow.
Using the horizontal ruler at the top of the report as a guide, drag to the left to resize the control until its right side is at the 1 3/4" mark.
5. On the Design tab, in the Results group, click Run.
Verify that the query returned the records that you want to append
2. On the Ribbon, in the Views group, click the bottom half of the
View button and then select Layout View from the menu.
3. CLICK TO SELECT THE PROPERTY YOU
WISH TO EDIT.
14. Click OK in the Format
Walls dialog box.
Modify Table Properties 1. OPEN the Events database from the data files for this lesson and then SAVE the database as Events-final. Take Note Don't forget to click Enable Content here.
We stop indicating it as a separate step from this point on but always click Enable Content when prompted.
3. Type King in the Find
What drop-down box.
As you learned in Lesson 1, a relational database stores information in separate tables that are connected or linked by a defined relationship that ties the data together.
When you add a new table to an existing database, that new table stands alone until you relate it to your existing tables.
14. Click
Yes.
20. Click Next. The final Table Analyzer
Wizard dialog box appears.
For example, numbers are sorted largest to smallest or smallest to largest, text is sorted alphabetically A to Z or Z to A,
Yes/No is sorted by cleared or selected, and dates are sorted newest to oldest or oldest to newest.
17. Click the Close Print Preview button.
You are back in Design view.
Attaching and Detaching Documents Before you can start attaching documents, you must create a field in a table and format it with the Attachment data type.
You can add the field in Datasheet view or in Design view.
Remember these points when finding and replacing data in Access 2016• You can use wildcard characters such as
a question mark or asterisk to find words or phrases that contain specific letters or combinations of letters.
Print Table Relationships 2. Click the Print button. The Print dialog box appears,
allowing you to select the printer you want to use.
Take Note You can use the record navigation buttons at the
bottom of a form to navigate among the form's records, just as you used them to navigate among records
23. Click OK on the dialog
box about report width.
4. Click the Show all tables link and then the plus (+)
box beside Rooms to display the fields in the table
5. In the Edit the rule description section, keep Field Value Is in the first menu. Click the drop-down arrow to the right of
between and scroll to the bottom of the list to select less than or equal to. Click in the empty text box and then type 10. Click the Bold button in the Preview section.
Review the available options
but keep the default options.
Database Essentials
button
6. Click the down arrow on the Background Color
button and select Red from the menu that appears.
2. On the Home tab, in the Views group,
click the lower half of the View button and then click Design View. The query appears in Design view
8. Click the Close button to
close the Field List.
11. Click the Close button to
close the Property Sheet.
7. Click the Close button to
close the Rooms report.
Define Input Masks for Fields 4. Click Yes to
close the message box and display the Input Mask Wizard
15. Click the Close button on the Non-Stamford Employees tab to
close the query. When prompted to save, click Yes.
14. Click the button beside Store that value in this field,
click the down arrow to display the menu, and then select In Production.
11. Right-click the gray grid background of the chart,
called the Walls, and choose Format Walls from the shortcut menu.
In this exercise, you create a multivalued lookup field using the Lookup Wizard in Datasheet view. Use a multivalued lookup field when you want to store multiple selections from a list of choices that are relatively small. This is useful if you want to limit the choices a user
can select to store in a table field to prevent erroneous data from being entered. For example, you can create a multivalued lookup field to assign only those employees who work in your department to a specific task.
Modify Table Relationships 4. Click the Create button. A line appears,
creating the relationship.
3. Notice the Save Object As option is already highlighted in the Save the
current database object category. Click the Save As button. The Save As dialog box appears.
Troubleshooting If you create a join by mistake, for example, a join between two fields that have dissimilar
data types, you can delete it. In the query design grid, click the join you want to remove and then press Delete
5. Right-click in the white area of the Navigation Pane to
display a shortcut menu. Click View By and then click Details.
In this exercise, you delete the relationship you previously created, and then recreate and
edit the relationship to enforce referential integrity.
Entering data in Datasheet view is very similar to entering data into an Excel worksheet,
except that data must be entered in related rows and columns, starting at the upper-left corner of the datasheet.
Take Note You cannot apply a case-specific filter. For example,
filtering a table for "D" will find all occurrences of the word "decaf" including "Decaf", "decaf", "deCaf", etc.
4. Click the >> button to move all the fields
from the Available Fields to the Selected Fields box.
18. Click the Number tab, select Currency
from the Category section, and then click OK.
Take Note You cannot drag table objects
from the Navigation Pane to the Add New] tab.
9. Click the File tab and then click Close. STOP. CLOSE the database and then EXIT Access. Take Note If you want a field value to operate as a filter that is
independent of other field values, you must enter that value on the Look for tab and each Or tab. In other words, the Look for tab and each Or tab represents an alternate set of filter values.
. USE the Graphic Art-final database that
is open from the previous exercise.
Scale Specify the maximum number of digits that can be stored to the right of the decimal separator (when decimal Field Size property
is set using Number data type). Expression Specify the expression used for the Calculated data type to generate a value.
10. Click Colored Lines in the Chart type:
list and then click OK.
4. Click the down arrow next to All Tables at the top of the Navigation Pane to display the
menu again and then click Object Type to return to the original view.
Apply a Filter 17. In the seventh row
of the Pounds field, select 30.
7. Type Record Number and then
press Tab again to move to the next blank field row.
11. Change to Report view. The total number of
records appears at the bottom of the report.
10. Change to Report view and scroll through the
records to see the conditional formatting at work.
• Field properties: You can also use field properties, such as the Validation Rule property to
require specific values, and the Validation Text property to alert your users to any mistakes.
the Backstage View 7. Click the Save As button in the right pane under the Save Database as area to
save the Data file as StudentData-final. Click Yes in the dialog box that appears informing you that the objects will be closed.
Modify Table Properties 10. Click the File tab and then click Save to
save the design changes you have made to the table.
Take Note Database templates, applications parts, and data type template files that you create are
saved to the
4. On the Design tab, in the Tools group, click the Tab Order button. The Tab Order dialog box appears, displaying the tab order in the Custom Order list. Notice that the In Production combo box field is referred to as Combo 10
since Access stores this name to use as a coding reference in Visual Basic for Applications. You may have a different number, depending on how many controls you have had and removed previously on the form.)
Click to access
template options
Click to access
templates
Delete a Table 1. Right-click the Event Comments table in
the Navigation Pane and then click Delete.
13. Click Close on
the Property Sheet.
22. On the Home tab, in the Views group, click the lower half of
the View button and the select Print Preview from the menu.
9. Click Next to display
the final screen.
Save a Table 3. If you want to save the file in a location other than
the one shown beneath the File Name box, click the folder icon and browse to a different location.
2. Double-click the SingleOneColumnRightLabels form
to open it. The form is displayed in Form view,
5. In the Field Name column,
type Area and then press the Tab key.
Take Note If you want to use the Find and Replace dialog box to search for characters that are
used as wildcards, such as a question mark, you must enclose that character in brackets, for example ?].
IME Mode Specify an Input Method Editor, a tool for using English versions of Windows. IME Sentence Mode Specify the type of data you can enter by
using an Input Method Editor. Append Only Retain a history of all data changes (by setting to Yes) of a Long Text field.
Displays the average extended price of orders for which the
value in ExtendedPrice field is more than 1,000 in a field named Average Extended Price.
• Variance: Measures the statistical
variance of all values in a column
Sometimes you may see an additional confirmation message warning you about potential issues
when deleting fields. You should always be cautious when deleting fields from a table.
Take Note If you regularly repeat this saved operation, you can create an Outlook task that reminds you
when it is time to perform this operation by clicking the Create Outlook Task check box in the import wizard.
5. Select the text in the Author box and then type [Your Name
where Your Name is your name).
11. Click Next. The final screen appears, which is where you decide
where to import the table. In the Import to Table box, type New Employees
7. Click Next to display the next screen
where you can specify field information.
Before you can delete a primary key or a lookup field, you must first delete the relationships for those fields. In this exercise,
you learn how to use the shortcut commands to delete two fields from an Access 2016 table.
10. Click OK. If you get another message box informing
you that an option will be ignored, click OK. The database is now encrypted with a password.
The following additional aggregated functions are available in Design view:
• Where: Groups data based on criteria you can specify
Review and Modify Data Types for Fields
3. Click the Date of Birth field header.
Validation text specifies the text in the error message that appears when a user violates a validation rule. For example, the error message could say
"Please enter a date that is later than June 4, 1977." The maximum length for the Validation Text property is 255 characters.
15. In the Layout section, click the Block option button,
. Keep the default orientation as Portrait and keep the selection so all fields fit on one page
14. Click the Description field control to select it. On the Arrange tab in the Position group, click the Anchoring button and then select Stretch Down and Across from the Anchoring menu that appears
. The Description field control, as well as all the others, will now automatically resize to display all their contained text, if necessary, when the Access window is made larger.
3. Click the Undo button on the Quick Access Toolbar
. The field row reappears.
Change Views 2. Click Design View. The table is displayed in Design View, as shown
.Notice that the Design tab is now displayed on the Ribbon
Add a Bound Control to a Report
1. On the Design contextual tab, in the Tools group, click the Add Existing Fields button.
Link to an External Data Source
1. On the External Data tab, in the Import & Link group, click Excel to open the Get External Data - Excel Spreadsheet dialog box.
Save as a Previous Version
1. Click the File tab and then click Save As. The Save As menu appears.
Use Navigation Buttons
1. Click the First record button. The selection moves to the first record.
Arrange Control Alignment, Size, and Position
1. Click the Inventory table in the Navigation Pane to select it.
Review Database Fields
2. Click the Guardian Information tab and then click the Emergency Information tab. Each field on each tab is an example of the type of information that could be contained in a database table.
Use Navigation Buttons
2. Click the Next record button. The selection moves to the next record.
Attach and Detach Documents
2. Click the header row of the Due Date field to select it.
Review and Modify Data Types for Fields
2. In the Navigation Pane, in the Students group, double-click Students: Table to open it.
Define a Primary Key
2. On the Home tab, in the Views group, click the bottom half of the View button, and from the menu that appears, select Design View.
16. Click the down arrow in the Chart Fields menu and then select
<No Field> again, since you don't want to display a chart for each record in the data source.
15. Click the down arrow in the Report Fields menu and then select
<No Field> since you don't want to display a chart for each record in the data source.
13. Select the Contact Last Name field and then click the
> button. Press Enter. Take Note Remember that you can also double-click the field to add it to the Prototype label section.
12. On the Design tab, in the Show/Hide group, click the Totals button.
A new Totals row should appear below the Table row on the design grid.
Use a Template to Create a Database 2. In the list of Personal templates that appear in the middle of the startup screen results pane, click Home inventory.
A preview screen of the selected template appears in the center of the startup screen, as shown . Close the Home inventory template preview screen.
Troubleshooting Always close the file you want to import before beginning the import operation. Keeping the source file open can result in data conversion errors.
After an import operation, you should review the contents and structure of the table to ensure that everything looks correct before you start using the table. If you see the message
After you make any changes to the layout of the report, click the Print button to start printing.
After printing the report, you can choose to save it. In this exercise, you view and print table relationships without saving the relationship report.
You can also right-click and select Delete Record from the shortcut menu.
After you delete a record, you cannot undo it.
7. Click the additional object tab for queries, forms, and reports, and then click the Select
All button as the contents for each tab are displayed to select all the objects in the Northwind database.
6. Click the Select
All button on the Tables tab.
18. In the Sizing & Ordering group, click the Align button and then select Right from the menu that appears.
All the controls are now right-aligned as a group
From Design view, change the data type for the ResidenceAssignment field to use a lookup list. The values in the list should include ResidenceName from the ResidenceHalls table. Items in the list should sort alphabetically by the ResidenceName field. Allow Access to hide the key column. Enforce data integrity, and do not allow deletions that would violate the integrity of records. Save the table.
Available Fields list, you selected Capacity, double-clicked the ResidenceName list item. Inside the Lookup Wizard dialog, you clicked the Next button. Inside the Lookup Wizard dialog in the 1 drop-down, you selected ResidenceName. Inside the Lookup Wizard dialog, you clicked the Next button, clicked the Next button, checked the Enable Data Integrity check box, and clicked the Finish button. Inside the Microsoft Access dialog, you clicked the Yes button.
10. Click Yes. A new table named Sales Team
Backup appears in the Navigation Pane.
11. In the Navigation Pane, notice the linked Excel icon next to Benefit_Providers. Double click
Benefit_Providers to open the new linked table Notice that there is not an Add New Field column because of the structure of a linked table.
Application parts vary from a single table to a collection of database objects like tables, forms, and reports. The Application Parts gallery consists of two categories:
Blank Forms and Quick Start. The Blank Forms category contains a collection of form parts that allows you to add predefined forms to a database.
6. On the Format tab, in the Font group, click the
Bold button. The Description control is bolded.
9. Drag the selected field to the Table1 box, positioning the horizontal black line below the
Book Title field, as shown in , and releasing the mouse button to place the Year field in its new location
When you create a new blank database, Access opens a database that contains a table where you can enter data, but it creates no other database objects.
By default, Access creates a primary key field named "ID" for all new datasheets and sets the data type for the field to AutoNumber. In this exercise, you create a new blank database.
7. Under Available Fields, double-click Last Name, First Name, and Referred
By to move them to the Selected Fields box.
7. SAVE and then
CLOSE the form
24. SAVE and then
CLOSE the report
13. SAVE and
CLOSE the report.
4. SAVE and
CLOSE the report.
3. SAVE and
CLOSE the table.
Remove a Filter 4. SAVE and
CLOSE the table.
Getting Started and Access Help windows may appear, which contain helpful videos and links about using the Asset tracking database.
CLOSE these windows, if necessary, to return to the Assets database with the Asset List form active.
19. Click the File menu and then click Save.
Change to Report view to view your changes.
5. Click Trim and then the < button to move it back to the Available Fields box.
Click Color and then the < button to move it back to the Available Fields box. Click Next.
Apply a Filter 8. Right-click the word to display the shortcut menu and then select Does Not Contain "
Click Does Not Contain "Decaf". Notice that the records are filtered to show only those that do not contain the word
10. In the Expression Elements category, click the expand button next to Tables to expand it. The available table names appear.
Click Inventory. The available fields from the Inventory table should appear in the Expression Categories box.
23. Click the Print button. The Print dialog box appears.
Click OK to print or click Cancel to close the dialog box.
2. In the Navigation Pane, double-click the
Corporate Sales table to open it.
Commands Results
Ctrl+Down Arrow Moves cursor to the current field in the last record
Commands Results
Ctrl+Home Moves cursor to the first field in the first record
Data Type Example Description
Date/Time Birthday: September 5, 1972 Stores date and/or time data.
4. Press and hold the Shift key and then select the ID field control. Still holding the Shift key, select the
Description, In Stock, and Price field controls so that all five In Production, ID, Description, In Stock, and Price) are selected.
5. Click the Showroom Contact link to display it in
Design view where you could make any necessary changes 6. CLOSE the Showroom, Contact table.
3. Change to
Design view.
In Design view, click the resize handles in the center of the vertical borders and drag to increase or decrease the size until the text looks right.
Drag the resize handles on the horizontal borders to change the height. Then, change back to Report view to see the results.
6. Click and hold the row selector. The mouse pointer changes to a move pointer with an empty rectangle. Drag up a row and notice the black horizontal line moves with you.
Drag up until the black horizontal line is in place at the top of the Description field; release the mouse button. The Combo10 field should be the first item in the list
• Table properties: Table properties provide very specific types of validation.
For example, you can use the Order By property to select one or more fields to specify the default sort order of rows in Datasheet view.
Take Note A database has to be saved first before any objects like tables, queries, forms or reports can be added to it.
For example, you cannot create a table first and then create the database to hold it. opens an empty table named Table1 in Datasheet view.
Take Note The Number data type should be used only if the numbers will be used in mathematical calculations.
For numbers such as phone numbers, use the Short Text data type.
Save a Table 2. In the Blank desktop database screen's File Name box, the default name should be Database1.
If not, replace the default name by typing Database1.
Entering Captions The Caption property field specifies the text displayed by default as column names in tables and in labels for forms, reports, and queries. The maximum length for the Caption property is 255 characters.
If you do not specify a caption to be displayed, the field name is used as the label. In this exercise, you set the Caption property for the Location field.
Other table properties are more advanced and used less often.
In this exercise, you modify the description property for a table.
You can then reapply the filter the next time you open the object by clicking the Toggle Filter button in the Sort & Filter group on the Ribbon.
In this exercise, you practice creating filters in several different ways.
Take Note If Access doesn't have enough information from what you enter to detect the data type, the data type is set to Short Text.
In this exercise, you practice reviewing and modifying data types.
13. On the Print Preview tab, in the Page Layout group, click the Page Setup button. The Page Setup dialog box appears.
Notice it contains many of the same options that areavailable in the Page Layout group but also includes additional options and details that can be modified.
3. Click the Objects that I depend on option button.
Notice the changes in the Reports section.
3. Change to Layout view and then press Shift while clicking on the label control placeholders titled Field1, Field2, Field3, and Field4 to select them all.
Press the Delete key on the keyboard to delete the label controls. Also delete the label control placeholder that contains the red asterisk, which could be used to denote an important field, like a key field.
To remove a table relationship, you must delete the relationship line. You can select the line by pointing to it and clicking it. When the relationship line is selected, it appears thicker.
Press the Delete key to delete the line and remove the relationship or right-click the line to display the shortcut menu.
10. Click Yes on the dialog box asking you if you want to permanently delete the
Reservations report. The report is now permanently deleted from the database.
5. On the Design tab, in the
Results group, click Run.
6. Type This template contains a standard photo exhibit table with standardized fields and a related form in the Description box
Review the other boxes and options in the Create New Template from This Database dialog box.
SOFTWARE ORIENTATION Navigation Pane
Search bar
10. On the Arrange tab, in the Table group, click the
Stacked button to change it back to a stacked layout,
20. Click the In
Stock Header.
Table Property Use This Table Property To Subdatasheet Expanded Specify whether to expand all subdatasheets when you open the table.
Subdatasheet Height Specify whether to expand to show all available subdatasheet rows default) when opened or to set the height of the subdatasheet window to show when opened.
17. CLOSE the
Subquery query.
7. CLOSE the
Summary Query.
Refresh Data in a Chart 1. Double-click the Income & Expenses
Summary table in the Navigation Pane to open it.
11. Click and drag the Income field button to the upper left of the chart and drop on the
SumofExpenses data list. Both the SumofExpenses and SumofIncome fields should be listed
2. CLICK TO SELECT
THE FOREIGN KEY FIELD
6. SELECT THE MATCHING FIELD FROM THE LIST OF MATCHING AVAILABLE FIELDS NAD CLICK THE MOVE SINGLE FIELD BUTTON
TO MOVE RTHE FIELD IN THE SELECTED FIELDS WINDOW REMEBER THAT THE MATCHING FIELD IS THE PRIMARY KEY IN THE TABLE.
20. Click the File menu and then select Save.
The Microsoft Graph software closes and the report is changed back to Design view.
4. In the Office section, click the Integral design.
The format is applied to the report.
6. Point to the Show Grand Total box, Click the Show Grand Total check box.
The menu disappears and the grand total appears in the Contract Amount column at the bottom of the report.
the Backstage View 8. Select the location to save the file and then click Save.
The new database file opens automatically.
3. Click Table: Industry Friends and then click Next.
The next screen in the Find Duplicates Query Wizard appears.
Rename a Table 7. Type Event Comments and then press Enter.
The table has been renamed.
6. With all five controls still selected, click the Control Margins button on the Arrange tab in the Position group and then select Narrow from the menu.
The text within the field controls and labels is formatted by the Narrow option.
8. Click the >> button to move all the fields to the Fields for Chart box and then click the Next button.
The third Chart Wizard dialog box appears, which shows you the chart type options.
5. Click the Next button. The Table Analyzer
Wizard dialog box, screen 3, appears
4. Double-click the Sales by Salesperson report in the Navigation pane and then change to Design view.
You want to remove the subform from the report and keep only the sales related to each salesperson.
Likewise, a business can repeat both order identification number field data and customer identification number field data for multiple customers;
however, these numbers become unique when combined resulting in a composite key for each customer.
7. The Yes, let the wizard decide button should be selected;
if it is not, select it. Click the Next button. The Table Analyzer Wizard dialog box, screen 5, appears.
9. Click Form in the drop-down list at the top of the Property Sheet,
if necessary, and then click the Format tab.
6. On the Home tab, in the Records group, click the Totals button. Scroll down,
if necessary, to see the Total row at the bottom of the result set.
Validation Text Enter text that appears when a record violates the Validation Rule expression. Filter Define criteria to display only matching rows
in Datasheet view. Order By Select one or more fields to specify the default sort order of rows in Datasheet view.
You could also hide or freeze certain fields. For example,
in a table that has several fields, you can hide or freeze fields to help you concentrate on certain data
11. SAVE the report as Stacked Report and
leave it open for use in the next exercise Add, Move, and Remove a Control from a Layout 1. Change to Report Design view, if necessary.
For example, it may be helpful to freeze First Name and Last Name fields so you can keep them fixed on the screen and then horizontally scroll and view other pertinent fields,
like E-mail or Telephone Number, to get a better view of your data. You can also hide those fields that may distract you from getting a better view of the data.
For example, using Quick Start fields, you can choose from a variety of fields including "Status" to quickly add a field named Status with built-in options
like Not Started, In Progress, and so on, or you can choose the "Address" Quick Start to quickly include related fields like City, State, and Zip Code.
8. In the Tables/Queries drop-down
list, click Table: Employees.
14. Click OK. The records for non-Stamford employees who
live in Darien are displayed in the results,
3. Use the File Open dialog box to
locate the applicants text file and then click Open.
8. On the Design tab, click the More arrow in the Controls group, and then locate the Use Control Wizards command and
make sure it is turned on The image to the left of the command should be highlighted.
The caption is what appears as column names in tables, and as labels in queries, forms, and reports. Keep in mind that Access will show field names as the column names and labels when
no caption property value is specified. The field name is what Access uses to reference the field behind the scenes and when you view the field names in Design view.
The following is a summary of the first three normal forms: • Third Normal Form (3NF): Use this form to remove fields that do
not relate to, nor provide a fact about, the primary key.
Use Object Tabs 3. Click the Close button
on the report tab to close it.
3. On the menu, click Text Filters and then click Contains
on the submenu. A Custom Filter dialog box appears.
3. Click the Properties button. The Properties dialog box appears. The Properties dialog box differs depending
on the type of printer you have. You can change the quality, paper, printing, and orientation options available for your printer in this dialog box. Click the Cancel button.
the Backstage View 3. Click File again. Backstage view now displays a menu of
options down the left side of the window and information about the currently opened database.
10. Type ATTN: and then
press the Spacebar.
The Navigation Pane enables you to open, copy, and delete tables and other database objects. It also lists all the objects in your database, including: tables the most basic database object that stores data in categories),
queries allow you to search and retrieve the data you have stored), forms (control data entry and data views and provide visual cues that make data easier to work with), and reports present your information in ways that are most useful to you).
Creating a Blank Database If you have existing data, you may decide that it is easier to create a blank database
rather than using a template because it would require a lot of work to adapt your existing data to the template's defined data structure.
the Backstage View 9. Select File again to
re-open Backstage view.
Modify Table Relationships 1. Right-click the center section of the
relationship line connecting the two tables. A shortcut menu appears
Attach and Detach Documents6. Click the Add button. Navigate to the data files for this lesson and
select invoice100. docx. Click Open. The document appears in the Attachments dialog box.
You can also access these commands on the shortcut menu by right-clicking the value. Take Note If you need to apply a filter that is not in
the common filters list, you can write an advanced filter using the Advanced Filter/Sort command on the Advanced menu.
9. CLOSE
the database
Take Note You can also
uncheck Select All, and then check 25.
The Ribbon and dialog box launcher
7. Click Cancel to close the dialog box.
9. Click the Close button on the Employees
Contact Query window to close the query.
8. In the Navigation Pane, click the
Reservations report to select it.
Apply a Filter
2. Select the Product Name field.
4. Click the Find Next button. Access highlights the first occurrence of 'King' in the report. Continue clicking the
Find Next button until Access reports that it has finished searching the records.
17. Click
Finish
11. Change to
Form view.
11. DESLECT THE
HIDE KEY COLUMN CHECK BOX
9. On the Design tab, in the Controls group, locate and then click the Combo Box button.
Hover your mouse pointer over each control button to view its ScreenTip until you find the Combo Box button.)
You can use templates as-is or you can customize them to better suit your needs.
In this exercise, you use one of the many available templates to create a database.
Using Navigation Buttons Access users who prefer to use the mouse can use the navigation buttons at the bottom of Datasheet view to move among records.
In this exercise, you use these buttons to navigate among records.
9. Click the Close button on the Duplicates for
Industry Friends tab to close the query.
14. In the query name box, type Employees Contact
Info Query to rename it and then click Enter. The query has been renamed.
Use the Navigation Pane
Information database that is still open from the previous
Define Input Masks for Fields 5. Click Medium Date to select the DD-MON-YR date format and then click Next. The next screen in the
Input Mask Wizard appears. If you wanted to change the input mask, you would do so here. You can also test entering the values in the Try It text area.
9. CLOSE
Notepad.
Data Type Example Description
Street: 1234 Landau Ave.
• Field sizes: It could also prevent an inexperienced user from mistakenly entering a first, middle, and last name in
a field designed only to hold a first name.
5. Click Finish to display
a simple select query.
18. SAVE the report design
and go back to Print Preview.
4. Select Table: Stamford Employees to select the table that is related to the Employees
table and contains the records you don't want to display. Click the Next button to display the next screen in the Find Unmatched Query Wizard.
Take Note You can import a variety of Access database objects into another Access database. Use the objects
tabs Tables, Queries, Forms, Reports, Macros, and Modules) at the top of the Import Objects dialog box.
4. Click Yes. A message appears confirming
that all objects were successfully imported.
• Comparison: An expression and a comparison operator
that compares the expression with the results of the subquery
Find Data within a Report USE the AlpineSkiHouse-final database
that is open from the previous exercise.
When you import data, Access creates a copy of the data or objects in the destination database without altering the source. Linking lets you connect to data from another source without importing it, so
that you can view and modify the latest data in both the source and destination databases without creating and maintaining two copies of the same data thereby reducing redundant data.
7. Click the Close button on
the Field List pane.
10. Click in the Decimal Places row in
the Field Properties section. Click the down arrow and then select 0 from the menu.
To set a field property in Datasheet view, open the table in Datasheet view. Click in the field for which you want to set the property. In the Field Validation group on
the Table Tools Fields contextual tab, select the Unique check box to require the values in the field to be unique for all
18. CLOSE
the database.
Allow Zero Length Allow entry (by setting to Yes) of a zero-length string "") in a Short Text, Long Text, or Hyperlink field. Indexed Speed up access to data in
this field by creating and using an index. Unicode Compression Compress text stored in this field when a large amount of text is stored.
5. Navigate to the data files for
this lesson and then select Fabrikam-final.
When you enforce referential integrity between tables, the line connecting the tables becomes thicker. The number 1 is also displayed on the line on the one side of the relationship and an infinity symbol
∞) appears on the other side to represent the "many" field values that can be included in this side of the relationship.
