Exp19_Access_Ch07_HOEAssessment – Technical Services 1.0

Project Description:

Yellowstone County Technical Services is a small company that provides technical support for a number of businesses in Yellowstone County, Montana. You have been tasked with updating the customer tracking data-base to expand the input and output capabilities of the system. In your experience with the company, you have seen some of the common errors users make when performing data entry and have also seen what is effective and what is not effective in forms. In addition, you have seen which reports users utilize and have heard suggestions about changes they would like made.

Start   Access. Open the file named Exp19_Access_Ch07_HOEAssessment_Technical_Services.accdb. Grader has automatically added   your last name to the beginning of the filename.

You have decided to use the Form   tool to create a Customers form. This form will enable users to look up   customer information. You will make this read-only so users do not   accidentally make errors when looking up information.
 

  Select the Customers table in the Navigation Pane. Click the Create tab and   click Form in the Forms group. Access creates a new form based on the   Customers table. The form opens in Layout view, ready to edit. Click anywhere   in the subform at the bottom of the window, click the border of the subform,   and then press Delete to delete the subform. The subform is removed. Click   Property Sheet on the Design tab in the Tools group if it is not already   displayed. The Property Sheet displays on the right side of your screen.
 

  Click the Data tab in the Property Sheet pane. Click the Allow Edits box,   which currently displays Yes, and click the arrow at the right. Change the   value to No. Repeat that to change the Allow Additions and Allow Deletions   property values to No. Close the Property Sheet.
 

  Change the title of the form to View Customer Data (No Edits). Switch to Form view. Attempt   to type in the CompanyName box. You should not be able to change the field   value.
 

  Attempt to click the New and Delete buttons in the Records group on the Home   tab.
  You should not be able to add or delete a record. Click Save in the Quick Access   Toolbar, and save the form as View Customers. Close the form.

You will use the Form tool to   create an Access form to help manage customer call data. This form will   enable you to record customer data. You will implement this using a menu.
 

  Select the Calls table in the Navigation Pane. Click the Create tab, and   click Form in the Forms group. Right-click the CustomerSatisfaction text box,   point to the Change To option, and then select Combo Box from the shortcut   menu. The CustomerSatisfaction text box changes to a combo box with an arrow   on the right side of the box.
 

  Click Property Sheet in the Tools group on the Design tab if it is not   already displayed. Click the Row Source property on the Data tab of the   Property Sheet, click the arrow at the right of the Row Source box, and then   select Satisfaction Results. Click the Limit To List property, and change the   value to Yes. Switch to Form view. Click the CustomerSatisfaction field.   Notice an arrow now appears on the right of the box. Type the value 6 for the CustomerSatisfaction   field, and press TAB. Access will display an error message that the text you   entered is not an item in the list.
 

  Click OK. Change the value for the first record’s CustomerSatisfaction field   to 2, and press TAB. You will not receive   an error message because the value is in range. Save the form as Calls   Drop-Down, and   close the form.

The users of the current Edit   Customers report have reported problems with the tab order. You will fix the   tab order. You will also fix an old form so that the tabs appear in the   correct order and remove a tab stop.
 

  Open the Edit Customers form in Form view. Press TAB. When you press TAB, the   State field becomes active, rather than the CompanyName. Press TAB five more   times, noticing the order in which the fields appear. The fields are not   displayed in a logical order.
 

  Switch to Design view, and click Tab Order in the Tools group on the Design   tab. Ensure Detail is selected, and click Auto Order. Click OK. Notice State   is no longer the second field on the list. The order now reflects the order   of the fields, top to bottom. Because this is a Stacked Layout form, Access   changes the tab order so it moves down one field at a time. Switch to Form   view. Press TAB six times, and verify that the tab order progresses in a   logical order. Save and close the form.
 

  Open the Customers Old form in Form view. Press TAB. The Phone field becomes   active. You will switch the tab order so Phone is the last field to become   active. Switch to Design view. Click Tab Order in the Tools group on the   Design tab. Ensure Detail is selected, and click Auto Order. Notice Phone is   displayed at the top of the list, which would make it the first field   displayed. This is not the logical order.
 

  Click the record selector to the left of the Phone field. Drag the Phone   field beneath the Zip field. Click OK. Click the CustomerID field. Display the   Property Sheet, if it is not already displayed. Click the Other tab in the   Property Sheet. Locate the Tab Stop property, and change the property setting   to No. Pressing TAB is no longer necessary as the CustomerID field value is   set by the AutoNumber. Switch to Form view. TAB through the fields. The   default field is now the CompanyName, and pressing TAB will bring you through   Address, City, State, Zip, and Phone, in that order. Note pressing TAB does   not bring you to the CustomerID field. Save and close the form.

The Edit Customers form does not   display the related call information for each customer. You will modify it so   the subform containing the information appears.
 

  Open the Edit Customers form in Design view. Click the Subform/Subreport   control in the Controls group on the Design tab. Draw a box in the Detail   area of the form under the Phone field. The size does not matter, as it will   be resized later. The SubForm Wizard dialog box displays. Click Next to   accept the default Use existing Tables and Queries option. Click the   Tables/Queries arrow, and select Table: Calls. Double-click the HoursLogged,   OpenedDate, and ClosedDate fields. Click Next. Click Next to accept the   default relationship. Accept the default Calls subform name, and click Finish.   Notice the Calls subform appears at the bottom of the screen. Resize the   subform so that the right edge is at the 4.0” mark on the horizontal ruler   and the bottom edge is at the 6.0” inch mark on the vertical ruler.
 

  Save and close the form.

You have decided to modify   reports to add calculations, and to modify the different header and footer   sections of forms and reports to improve the print and on-screen readability.   You will be making changes to an existing form and a calculated control to   determine if a call is Open or Closed.
 

  Open the Calls Drop-Down form in Design view. Drag the end of the Detail   section (appearing right above the form footer) to about 4” on the vertical   ruler. Click Text Box in the Controls group on the Design tab. Click beneath   the last control in the form (CustomerSatisfaction) to insert the control.
 

  Display the Property Sheet, if it is not displayed. Click the Data tab on the   Property Sheet, click the Control Source box, and then click the ellipses   next to the Control Source property to display the Expression Builder.
 

  Type IIf(IsNull([ClosedDate]),”Open”,”Closed”) and click OK. This expression   will display Open when the   ClosedDate is null (in other words, when no value exists in the ClosedDate   field) and Closed otherwise. Click   the label for the new control (the word Text followed by a number), and press   Delete. Switch to Form view. Ensure the first few records display Closed.
 

  Click the Last record Navigation button, and ensure the last record in the   table has a value of Open. As the open calls are going to be the most recent,   most older calls will be closed, whereas most new calls will be open. Save   and close the form.

You will make changes to an   existing report to display the number of days each call has been open.
 

  Open the Days Open report in Design view. Click the Text Box control in the   Controls group on the Design tab. Click to the right of the ClosedDate text   box in the Detail section of the report to add a new field. Click Tabular in   the Table group on the Arrange tab. The new field lines up after the final   column in the report. Click the Label control for the new column.   Double-click to select the existing text. Press DELETE to remove the existing   text, and type Days Open. Click the Text Box control for the new column   (which currently displays the word Unbound).
 

  Display the Property Sheet, if it is not displayed. Click the Data tab on the   Property Sheet, and click Control Source. Click the ellipses to launch the   Expression Builder. Type =[ClosedDate]-[OpenedDate] in the Expression box. Click   OK. Switch to Report view. Verify the calculation correctly displays the   number of days each call was open. Save and close the report.

You will adjust an existing form by adding a logo to the form header   and adding a page header.
 

  Open the Calls Drop-Down form in Design view. Click the form logo (to the   left of the word Calls) in the Form Header section, and press DELETE. The   form logo disappears. Click the Image control in the Controls group on the   Design tab. Click to the right of the word Calls in the form header. Browse   to the location of your data files, and select a07p1Logo.jpg. Click OK. A   logo for Yellowstone appears in the form header.
 

  Click the gray background of the Form Header section. Click the Shape Fill   arrow in the Control Formatting group on the Format tab. Select Blue, Accent   1 (first row, fifth column). Recall you can see the names for each color by   pointing to a color and waiting for the ScreenTip to appear. Click the Calls   label. Click the Font Color arrow in the Font group on the Format tab. Select   White, Background 1 (first row, first column). Click the Font Size arrow in   the Font group on the Format tab. Change the size to 22. Right-click a blank   area, and select Page Header/Footer from the shortcut menu.
 

  A Page Header section displays above the Detail section, and a Page Footer   section displays below the Detail section. Click the Label control in the   Controls group on the Design tab. Click the left side of the Page Footer   section. Type Created by First Last, replacing First with your first name and   Last with your last name.
 

  The page footer is not displayed because it will only appear when printed.   Click the File tab, and click Print. Click Print Preview. Navigate to the   second page. The footer should appear on each page. You will notice the form   is too wide to fit on one page left to right. When printed, this might lead   to extra pages. Right-click the Calls Drop-Down tab, and select Design View.   Click the CallID text box, and change the width to 5.5″ on the   horizontal ruler. All other controls adjust as well.
 

  Point to the right edge of the Detail section, and drag it to about 7.5″   on the horizontal ruler. Click the File tab, and click Print. Click Print   Preview. As a printed page in Portrait is 8.5″ wide, this report will   now fit on one page left-to-right. Click Close Print Preview, and save and   close the form.

You will create a new report based on the Calls table and use the   group headers and footers to summarize the data.
 

  Click the Calls table. Click the Report tool in the Reports group on the   Create tab. Delete the report icon (which appears to the left of the word   Calls) from the report header. Click Group & Sort in the Grouping &   Totals group on the Design tab to display the Group, Sort, and Total pane at   the bottom of the screen, if it is not already displayed. The Group, Sort,   and Total pane displays at the bottom of the report. Click Add a group in the   Group, Sort, and Total pane, and select CallTypeID. The report will be   grouped by the type of call. Resize the RepID field so it takes up only as   much room as necessary. The report should fit on one page, left to right.
 

  Switch to Design view. Notice a CallTypeID Header appears between the Page   Header and Detail sections. Click More in the Group, Sort, and Total pane.   Click the without a footer section arrow and select with a footer section. A   CallTypeID footer appears between the Detail and Page Footer sections. Click   the HoursLogged text box. Click Totals in the Grouping & Totals group on   the Design tab, and select Sum.
 

  A Sum function appears in both the CallTypeID Footer and the report footer.   Resize the CallTypeID footer and report footer to be about 1″ tall. You   can drag the border down until you see a 1 on the vertical ruler. Resize the   text boxes for the new sum fields to be about double the current height.   Click the Insert Page Break control in   the Controls group on the Design tab. Click the bottom of the CallTypeID   footer. Six small dots appear on the left of the CallTypeID footer. Display   the report in Print Preview.
 

  All calls with a CallTypeID appear on page 1, with a total of 188 hours appearing on page 2. Due to   the number of calls, your totals for CallTypeID 1 will appear on a page by   itself. Scroll forward and notice a total of 208 hours for all calls with a CallTypeID of 2.
 

  Save the report as Calls By Type, and close the report.