Access Modules 1-3 SAM Training - ISDS 1100

Create a new select query in Query Design view using the Clients and Managers tables. Include the ClientName and Phone fields from the Clients table. Include the LastName field from the Managers table, in that order. Run the query and enter ManagerPhones as the query name.

1. Click Create. 2. Click Query Design. 3. Click ADD. 4. Click Managers. 5. Click ADD. 6. Click Close. 7. Drag the ClientName field from the Clients field list to the first column of the query grid. 8. Do the same with Phone and LastName into the second and third column. 9. Run (!) 10. Save --> ManagerPhones. 11. OK.

Add the Clients table to the Relationships window.

1. Click Database Tools. 2. Relationships. 3. Click the Show Table button. 4. Click Clients. 5. Click Add. 6. Click close.

Back up the Rockland Clients.accdb database to the default folder location, using the default file name.

1. Click File 2. Click Save As 3. Click Back up Database (under Advanced) 4. Click Save As 5. Click Save

Close the Rockland Clients.accdb database, but leave Access open.

1. Click File 2. Click close

Compact and repair the Rockland Clients.accdb database.

1. Click File 2. Click the Compact and Repair Database button.

Toggle the filter to display all of the records.

1. Click Toggle Filter button by ribbon.

With the ClientOrders report in Layout view, switch to Report view.

1. Click View drop arrow. 2. Select Report View

Create a new table using Table Datasheet view with the following fields and data types, and in the following order: ID (AutoNumber which is automatically added by Access), CommentDate (Date & Time), and Comments (Long Text). Save the table with "ClientComments" as the table name.

1. Click create --> Table 2. "Click to add" --> Date & Time --> type "CommentDate" --> Press ENTER 3. "Click to add" --> Long Text --> type "Comments" --> press ENTER 4. Press save button - enter "ClientComments" in text box 5. Click OK

In Datasheet View of the Managers table, add a new field named "Notes" with the Long Text data type.

1. Click the "Click to Add" column heading. 2. Select Long Text. 3. Type "Notes" then press ENTER.

In Datasheet View of the Clients table, enter "Address" as the Caption property for the Street field.

1. Click the "Street" field 2. Click the Fields tab on the ribbon. 3. Under "Properties" select name and caption 4. Select Caption text box --> type Address then click OK

Set the Decimal Places Property to 0 (zero) for the Bonus field.

1. Click the Bonus field name. 2. Click the decimal Placed property 3. At the right of the DP text box click the arrow. 4. Click 0 --> ENTER

Enter Value must be <=5000 as the Validation Text property for the BonusAmount field.

1. Click the BonusAmount field. 2. Click the Validation Text Property (at bottom). 3. Type--> Value must be <= 5000 --> in the field and press ENTER

Create a new table in Design View with the following field names and data types: "ManagerID" Short Text; "Rating" Number. Save the table with the name ManagerRatings and do not set a primary key field at this time.

1. Click the Create tab on the ribbon. 2. In the Tables group, click the Table Design button. 3. Type "ManagerID" as the first field name and press ENTER. 4. Click the field name cell in the second row. 5. Type "Rating" and press ENTER --> Data Type arrow and click "Number" 6. Click the SAVE button 7. Type "ManagerRatings" in the Table Name text box. --> Click OK 8. When prompted, click NO

Create a new select query using the Simple Query Wizard based on the Client ID, Client Name, and Discount fields of the Clients table, in that order. Show Details, enter "ClientDiscounts" as the query name, and open the new query in Datasheet View.

1. Click the Create tab. 2. Click the Query Wizard button. 3. With "Simple Query Wizard" selected, click OK 4. Click the Tables/Queries list arrow, then click Table: Clients 5. Select ClientID --> Click the Select Single Field button. --> ClientName is selected --> then click the (>) button 6. In the Available Fields list, click Discount --> Click the Single Field Button (>) (you should have ClientID, ClientName, & Discount) 7. Click Next. Accept the default option. 8. Click Next again. 9. Click in the text box and enter "ClientDiscounts" for the query title 10. Accept default setting --> click Finish

Add a criterion to the YearsAsClient query to select only those records where the value is greater than 6 for the Years field. Then, run the query to display it in Datasheet view.

1. Click the Criteria cell for the Years field. 2. Type >6 in the cell. 3. Run (!)

In the Relationships window, create a one-to-many relationship between the States and Clients tables using the StateAbbreviation field in the States table and the State field in the Clients table. Enforce referential integrity on the relationship. Note that you will need to add the States table to the Relationships window as a first step.

1. Click the Database Tools tab on the ribbon. 2. Click Relationships. 3. Click Show Table. 4. Double-click States 5. Click Close. 6. Drag the StateAbbreviation field to the State field in the Clients table 7. Click the Enforce Referential Integrity box. 8. Click Create.

Import the States table from the USAStates.accdb database into the Rockland Financials.accdb database.

1. Click the External Data tab on the ribbon. 2. Click the New Data Source button. 3. Click From Database. 4. Click the Access button. 5. Click Browse 6. Click the USA database. 7. Click Open. 8. Click OK. 9. Click States 10. Click OK. 11. Click Close

In Design View of the Clients table, enter "Address" as the new Field Name for the Street field.

1. Click the Field Name cell for the Street field. 2. Type "Address" and press ENTER.

In Query Design view of the ClientsByState query, delete the FirstName field. (Delete a field from a query in Design view.)

1. Click the FirstName field. 2. On the design tab, click the Query Setup button then the "Delete Columns" button.

Export the ManagerList query to Excel. Do not change the default folder location, Excel workbook name, or export steps.

1. Click the ManagerList query (Left pane). 2. Click the External Data tab on the ribbon. 3. Click Excel. 4. Click the Export data with formatting and Layout option. 5. Click OK. 6. CLOSE

Create a new form based on the Managers table using the Split Form tool. Save the form, using ManagerSplitForm as the form name.

1. Click the Managers table. 2. Click Create tab. 3. Click the "More Forms" button. 4. Click Split Form. 5. Click the SAVE button. 6. Type ManagerSplitForm in the text box. 7. Click OK.

Set the Default Value property to 1000 for the MinOrder field.

1. Click the MinOrder field. 2. Click the Default Value Property name (0). 3. Type 1000 and Press Enter.

Move to the next record in the Managers data sheet.

1. Click the Next record button, in the navigation buttons in the lower-left corner of the data sheet. >

In the Documents folder, open the Rockland Clients.accdb database, which is located in the Databases subfolder.

1. Click the Open Other Files link. 2. Click Browse. 3. Click the Databases folder. 4. Click Open. 5. Click the Rockland Database. 6. Click Open

Use the Find Unmatched Query Wizard to find all records in the Clients table that do not have matching records in the ClientOrders table. The Tables are joined by the ClientID field in the Clients table and the ClientID field in the ClientOrders table. Select all the fields In the Clients table for the query results, accept the default query name, and view the results.

1. Click the Query Wizard button. 2. Click the Find Unmatched Query Wizard option. 3. Click OK. 4. Click Table:Clients. 5. Click Next twice. 6. Click <=> 7. Click Next. 8. Click >> 9. Click Next. 10. Click Finish

In the Table Datasheet view, delete the Client ID 113 SC Logistics record from the Clients table.

1. Click the Record sleeved for Client ID 113 SC Logistics (first little block on the row) 2. Press DELETE (Just press cut and that also works) 3. When prompted click YES

In the first record (ClientID 101), select West in the Region lookup field.

1. Click the Region field to display the lookup field arrow. 2. Click the arrow. 3. Click West.

In Query Design view of the MinOrders query, add a Total row to the query grid.

1. Click the Show/Hide group --> Totals.

In Query Design view, add an ascending sort order to the LastName field of the ManagerList query, then run the query. (Add a single field sort to a query in Design view. )

1. Click the Sort cell for the LastName field. 2. Click the Sort cell arrow. Click Ascending 4. Click Run (!)

In Query Design view of the ClientsByState query, add an ascending (A-Z) sort order to the StateName and ClientName fields, then run the query to display it in Datasheet view.

1. Click the Sort cell for the StateName field. 2. Click the drop arrow. 3. Click Ascending. 4. Click the Sort cell for the ClientName field. 5. Click the drop arrow. 6. Click Ascending 7. Run (!)

In Table Design view of the Clients table, change the Field Size to "2" for the State field. Then, save the table, and click YES when prompted.

1. Click the State field. 2. In the "Field Properties" pane (below), click the Field Size property box 3. Type "2" to replace the value, then press ENTER 4. Click the SAVE button 5. Click YES when prompted

In Query Design view for the ClientsByState query, add ST as a caption for the State field.

1. Click the State field.] 2. In the Show/Hide group, click the Property Sheet button. 3. Click the caption text box. 4. Type ST, then Enter.

In the ClientList query, edit the first record, Client ID 109, so that 6200 Ontario Ave is the new Street field value.

1. Click the Street field for the Client ID 109 record (the first record). 2. Change the number 621 --> 6200 then press ENTER.

In the ClientList query, switch to Query Design view.

1. Click the View button (top left)

With the Clients table in Datasheet view, sort the records in ascending (A to Z) order based on the values in the Client Name field.

1. Click the arrow at the right of the Client Name field. 2. Click the Sort A to Z option.

In Datasheet view of the ClientsOrderQuery, sort the records in Ascending (A to Z) order based on the values in the Client Name field.

1. Click the arrow next to the Client Name field. 2. Click sort A to Z

Add OR criteria to the ClientsByState query to select only those records where the ManagerID field value is M-01 OR the State field value is TX. Run the query to display it in Datasheet view.

1. Click the cell on the Criteria row for the ManagerID field. 2. Type M-01. 3. Click the cell on the "or" row for the State field. 4. Type TX in the cell. 5. Click the adjacent cell to the right of the grid. 6. Run (!)

Add AND criteria to the ClientsByState query to select only those records where the ManagerID field value is M-04 and the State field is IL. Run the query to display it in Datasheet view.

1. Click the cell on the Criteria row for the ManagerID field. 2. Type M-04 in the cell. 3. Click the cell on the Criteria row for the State field. 4. Type IL in the cell. 5. Click the adjacent cell to the right. 6. Run (!)

Display the subdatasheet for John Vang's record, which has Manager ID field value of M-03.

1. Click the expand button to the left of the 3rd record with the Manager ID field of M-03.

Add criteria to the ManagerData query to select only those records where the HireDate field value is greater than 1/1/2008. Then, run the query to display it in Datasheet view.

1. Click the first Criteria cell for the HireDate field. 2. Type >1/1/2008 in the cell. 3. Run (!)

Add criteria to the MinOrders query to select only those records where the MinOrder field is >=2000 (greater than or equal to $20,000). Then, run the query to display it in Datasheet view.

1. Click the first Criteria cell for the MinOrder field. 2. Type >=20000 in the cell. 3. Run (!)

Add criteria to the ClientsByState query to select only those records where the State field value is TX. Then, run the query to display it in Datasheet view. (Add a criterion to a Short Text field to a query in Design view.)

1. Click the first Criteria cell for the State field. 2. Type "TX" in the cell. 3. Run (!) - you don't need to press ENTER

In Design view of the ClientList query, add a criterion using a wildcard character to the ClientName field to select values of any length that start with the letter "M." Then, run the query to display it in Datasheet view.

1. Click the first criteria cell for the ClientName field. 2. Type "M*" in the cell (without quotations//not case sensitive) 3. Run (!)

Use filter by selection to select all records with a State value equal to IL.

1. Click the first occurrence of IL in the State field. 2. Click the Selection button by ribbon. 3. Click Equals "IL"

With the Clients table in Datasheet View, change the Field Size of the State field to "2" and click YES when warned about some data being lost. Save the table.

1. Click the first value "TX" in the state field. 2. Fields tab on ribbon --> Properties --> "Field Size" to 2 3. Click YES when prompt appears 4. Click SAVE button

In Datasheet View of the Clients table, change the Date Acquired field to Data/Time data type.

1. Click the first value in the "Date Acquired" field. 2. Click the "Table Tools - Fields" tab on the ribbon. 3. Formatting --> Data Type arrow --> Select Date/Time

With the Managers table open in Datasheet view, change the Format property of the DateOfHire field to Medium Date

1. Click the first value in the DateOfHire field. 2. Click the "Fields" tab on the ribbon --> select Formatting 3. Click "Medium Date" on the list. (it changes the whole field automatically)

With the Managers table open in Datasheet view, use the Find and Replace too to find Bryan in the FirstName field and replace it with Brian as the new text.

1. Click the first value in the FirstName field. 2. Click the Find tab --> Replace button 3. Find what: Bryan 4. Press tab. 5. Replace with: Brian. 6. Click Find Next. 7. Click Replace. 8. Click Cancel.

Using ClientEntry form, delete the Client ID 101 record. (EnergyPro)

1. Click the record selector at the left of Record 101. 2. Press the DELETE key. 3. Click YES.

In Query Design view of the ManagerList query, move the ManagerID field to the fourth column of the query grid.

1. Click to select the field selector for the ManagerID field. 2. Drag the field selector to the new location as indicated by the back indicator line, then release the mouse button to complete the move. (to the right)

Create a Relationship report for the Rockland Clients.accdb database.

1. Database Tools 2. Click Relationships 3. Click Relationship Report.

Enforce referential integrity on the relationship between the States and Clients tables.

1. Database Tools tab. 2. Relationships button. 3. Click Edit Relationships 4. Click the Table/Query button. 5. Click States. 6. Click the Enforce Referential Integrity box. 7. Click Ok.

Open the ClientEntry form in Form view.

1. Double click then ClientEntry form.

In Query Design view of the ClientList query, add the Zip field from the Clients table as the last field in the query. (Add a field to a query in Query Design view.)

1. Double-click the Zip field.

Resize the Email field so that all the field values are fully visible.

1. Double-click the filename separator line to the right of the Email field.

In Query Design view of the ClientsByState query, create an outer join between the tables so that all records from the Managers table are selected in the query.

1. Double-click the link line between both tables. 2. Click option 2. 3. Click OK.

In Query Design view of the ManagersByState query, link the two fields lists using the common ManagerID field.

1. Drag the ManagerID field from the Managers table to the ManagerID field of the Clients table.

With the Managers table in Datasheet view, enter a new record with "M-13" as the Manager ID, "Shin" as the First Name, "Li" as the Last Name, and "[email protected]" as the email address.

1. In Datasheet view, in the "Records" group on the Home tab, click the "New" button. 2. Type "M-13" and Press TAB 3. Type "Shin" and press TAB 4. Type "Li" and press TAB 5. Then type in the email and press TAB

In Design View of the Clients table, add a new field names "Notes" with a Long Text data type.

1. In Design view of the Clients table, click the empty Field Name cell below the Zip field. 2. Type "Notes" and press ENTER 3. Click the data type arrow --> Click Long Text

In Form view of the ClientEntry form, change the City from Houston to Austin for the first record (Client ID 101)

1. In Form view, Click City. 2. Type Austin, click TAB.

With the ClientList query in Design view, create a parameter query with the parameter criterion [Enter state abbreviation] in the State field. Run the query and type "tx" in the parameter prompt dialog box.

1. In Query Design view, click the Criteria cell for the State field. 2. Type [Enter state abbreviation] in the cell. 3. Click the adjacent cell in the query grid. 4. Run (!) 5. Dialog opens --> enter "tx" (without quotations) in the box. 6. Click OK.

With the ClientsByState query open in Query Design view, resize the Clients field list so that all fields are visible.

1. In Query Design view, drag the bottom border of the Clients field list downward until all fields are visible.

In Table Design view, set the ClientID field as the primary key field for the Clients table.

1. In Table Design view, click the ClientID field. 2. In the Table Tools Design tab, click the Primary Key button.

With the Managers table in Design view, change the Hire Date field to a Date/Time data type.

1. In Table Design view, click the Data Type cells for the Hire Date field. "Short Text" 2. Click the arrow for the field, click Date/Time on the list.

With the ClientEntry form in Form view, use the Find tool to find the first record with Detroit in the City field.

1. In the ClientEntry form, click the City text box. 2. In the Find group, click the Find button. 3. Search Detroit. 4. Click Find Next. 5. Cancel.

Add the ClientList query to the custom ClientGroup group.

1. In the Navigation Pane, click the arrow (by "All Access"). 2. Click Custom. 3. Right-click the ClientList query. 4. Point to Add to Group. 5. Click ClientGroup.

View the Managers table in Design view.

1. In the Navigation pane, right-click the Managers table. 2. Click "Design View"

Copy all of the records from the NewClients table and paste them to the end of the Clients table.

1. In the NewClients table click the Select All button (little white box). 2. Click Copy. 3. Click the tab for Clients data sheet. 4. Click the New Record button (*). 5. Click Paste. 6. When prompted, click YES.

With the ClientMinOrder query open in Design view, set the Format property for the MinOrder field to Currency.

1. In the Query Design grid, click the MinOrder field. 2. On the Query Tools Design tab on the ribbon, in the Show/Hide group, click the Property Sheet button. 3. Click the Format property --> drop down arrow. 4. Click Currency.

With the Revenues query in Design view, create and run a Top Values query to select the top 5 records.

1. In the Query Setup group, (top right) click the Return drop down arrow. 2. Click 5. 3. Run (!)

Add a Total row to the Clients data sheet.

1. In the Records group on the ribbon, click the Totals button.

With the ClientOrders report in Report view, switch to Report Design view.

1. In the Views group on the ribbon, click the View arrow. 2. Click Design View

With the ClientEntry form open in Form view, navigate to the previous record.

1. In the lower-left corner of the ClientEntry form, in the navigation buttons, click the Previous record button. <

Move to the first record in the ClientEntry form.

1. In the lower-left corner of the form, click the First record button in the navigation buttons. [<

With the ClientEntry form open in Layout view, switch to Form view.

1. On the Form Layout Tools Design tab on the ribbon, in the Views group, click the View button.

Use the Form tool to create a form based on the Clients table.

1. On the Navigation pane, click the Clients table. 2. In the Create tab, click FORM

View the ClientOrders in Print Preview form.

1. Right - click ClientOrders Report. (Under reports) 2. Click Print Preview.

Rename the ManagerEmailList report using ManagerEmails as the new name.

1. Right-click on the name. 2. Click rename. 3. Type ManagerEmails then press ENTER.

In Datasheet View of the Managers table, rename the field name of the Manager Email Address field using "Email" as the new name.

1. Right-click the column heading, "Manager Email Address" 2. Click Rename Field 3. Type "Email" and press ENTER

With the MinOrder in Design view, use the Zoom dialog box to create a calculated field that adds 1000 to the existing MinOrder field as the last field in the query. Use NewMinOrder as the name of the calculated field and run the query.

1. Right-click the first blank field cell next to "MinOrder." 2. Click Zoom. 3. Type--> NewMinOrder:[MinOrder]+1000 4. Click OK. 5. Run (!)

With the ClientEntry form in Form View, use the Current Record box to display the 8th record.

1. Select 1 in the Current Record Box (at the bottom of the page). 2. Type 8 then press ENTER.

Set the font size of the data sheet to 12.

1. Text formatting --> Font size arrow. 2. Click 12

In the ClientsByState Query in data sheet view, use the Find tool to find the first record with Houston in the City field.

1. Under City, click the first value. 2. Click Find button --> Click Find again. 3. Find what: Houston 4. Click Find Next. 5. Click Cancel

With the ClientEntry form in Form view, navigate to the last record.

1. Click >I button

Use Filter by Form to select all records with a Date Acquired value greater than or equal to 1/1/2014.

1. Click Advanced by the ribbon. 2. Click Filter By Form. 3. Click the Date Acquired cell. 4. Type >=1/1/2014 in the cell. 5. Click Toggle Filter.

Create a new report using the Report Wizard based on all of the fields in the ClientOrdersQuery query. Do not apply any additional grouping, sorting, or summary information. Use a Columnar layout and Portrait orientation, enter MinimumOrders as the title, and preview the new report in Print Preview.

1. Click Create 2. Click the "Report Wizard" button. 3. Click the drop arrow; click ClientOrdersQuery 4. Click the Select All button (>>) 5. Click Next three times. 6. Change to Columnar layout 7. Click Next 8. Edit the title to read MinimumOrders 9. Click FINISH

Create a new select query in Query Design View using the Clients table, including the ClientName, City, and State fields in that order. Run the query, and save it with ClientLocations as the name.

1. Click Create 2. Click the Query Design Button 3. Click add in the Show Table box 4. Click Close 5. Drag the ClientName field from the Clients field list to the first column of the query grid. 6. Drag the City field to the second column of the query grid. 7. Drag the State field to the third column. 8. On the top tab, click Run (!) 9. Click SAVE 10. Type ClientLocations in the text box. 11. Click OK.