• Home
  • Blog
  • GC Database & Courtyard Medical Plaza Exercise

GC Database & Courtyard Medical Plaza Exercise

Daniel Kevins

0 comments

  1. Open the CourtyardMedicalPlaza-04 database start file.
  2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
  3. Enable content in the database.
  4. Create a form using the Blank Form button.
    1. Add all the fields from the Physician table in the following order: DoctorID, FirstName, LastName, PrimarySpecialty, OfficeNumber, and MoveInDate.
    2. Close the Field List.
    3. Save the form as AddNewPhysician.
  5. Edit the form in Layout view, add a new column and new rows to the control layout, and modify their size.
    1. Add two rows below the Tenant Since row.
    2. Add one column to the right of the existing columns.
    3. Adjust the Width of each column to 1.6″.
    4. Adjust the Height of the last row to .4″.
  6. Add command buttons, a title, and the date to the form.
    1. Add a button into the bottom row of the left-most column to save a record [Record Operations Category].
    2. Change the wording to Save Physician Record.
    3. Name the button AddPhysicianFormSaveButton.
    4. Add a second button into the bottom row of the middle column to add a new record [Record Operations Category].
    5. Change the wording to Add New Physician.
    6. Name the button AddPhysicianFormAddButton.
    7. Add a third button into the bottom row of the right most column to close the form [Form Operations Category].
    8. Use the existing wording for the button.
    9. Name the button AddPhysicianFormCloseButton.
    10. Add a title to the form header. Edit the title to include spaces between the words.
    11. Add the date to the form. Use the DD-Mon-YY format. If necessary, deselect the Include Time check box so that only the date is added.
    12. Enter 1.1″ in the Width property and 4″ in the Left property for the Auto_Date control.
    13. Save the changes to the form. The revised form should look similar to Figure 4-154.Layout view of form displays header and a control layout with six fields and three buttons.Figure 4-154 AddNewPhysician form
  7. Restrict the form to data entry.
    1. Open the Property Sheet for the form if necessary and select the Data tab.
    2. Click the drop-down arrow in the Selection box of the Property Sheet and select Form.
    3. Select Yes in the Data Entry property box.
    4. Select Yes in the Allow Additions property box. Recall that when both the Data Entry and Allow Additions properties are set to yes, the values in the Allow Deletions and Allow Edits properties do not have an impact
    5. Save the form.
    6. Close the form.
  8. Create a report using the Report Wizard.
    1. Launch the Report Wizard.
    2. Add all the fields from the RentInvoices table in the following order: InvoiceID, FKDoctorID, InvoiceDate, Description, DueDate, DateReceived, AmountDue, and Discount.
    3. Accept the suggested grouping by FKDoctorID on the second page of the Report Wizard.
    4. Add summary options on the third page of the Report Wizard. In the Summary Options dialog box, select the Sum of the AmountDue field and select the Detail and Summary radio button for the Show option if necessary (Figure 4-155).Summary Options dialog box contains clickable OK and Cancel buttons.Figure 4-155 Summary Options dialog box
    5. Choose a Stepped layout and Landscape orientation and deselect the Adjust the field width so all fields fit on a page check box.
    6. Click the Finish button without making any changes to the suggested title. The preview of the report should look similar to Figure 4-156. Don’t worry if not all fields are visible, or if some of your fields overlap or display the # symbols indicating that the column width is too narrow. You resize the columns in step 9. The wording for the summary line was automatically added using the totals created by the Report Wizard.Report view of report excerpt displays header and grouped records with summary line total.Figure 4-156 RentInvoices report created using the Report Wizard
    7. Close Print Preview.
  9. Edit the report using Layout view so that your report matches Figure 4-157.
    1. Switch to Layout view and open the Property Sheet if necessary.
    2. Select both the FKDoctorID label and text box and change the Width to .6″.
    3. Change the Width of the InvoiceID label and text box to .75″ and change the Left property to 1″.
    4. Change the Width of the Invoice Date label and text box to 1″ and change the Left property to 1.9″.
    5. Change the Width of the Description label and text box to 2.8″ and change the Left property to 3″.
    6. Change the Width of the Due Date label and text box to 1″ and change the Left property to 6″.
    7. Change the Width of the Date Received label and text box to 1″ and change the Left property to 7.1″.
    8. Change the Width of the Amount Due label and text box to 1″ and change the Left property to 8.3″. Don’t worry that you still don’t see the entire value. It will be corrected when you change the next column.
    9. Change the Width of the Discount label and text box to .7″ and change the Left property to 9.4″.
    10. Edit the title label and the InvoiceID and DateReceived field labels by inserting a space between “Rent” and “Invoices”, “Invoice” and “ID”, and “Date” and “Received”.
    11. Remove the “FK” from the FKDoctorID label and insert a space between “Doctor” and “ID”. The updated labels should match Figure 4-157.
    12. Change the Width of the text box that displays the sum of the Amount Due for each doctor to 1″ and change the Left property to 8.3″.
    13. Click to select a Summary for ‘FKDoctorID’ text box and change the Width to 6″ and the Height to .2″.
    14. Scroll to the bottom of the report. Change the Width of the text box that displays the grand total of the Amount Due to 1″ and change the Left property to 8.3″.
    15. Scroll to the lower-right corner of the report to find the text box with the page number. Click to select the page number and change the Width to 1″ and change the Left property to 9.4″.
    16. Save the report.
    17. Switch to Report view. Scroll to the top of the report if necessary. The report should look similar to Figure 4-157.Report view of updated report excerpt.Figure 4-157 RentInvoices report after editing the layout
    18. Switch back to Layout view.
  10. Add an oldest to newest sort on the InvoiceDate field in the Group, Sort, and Total pane.
  11. Add conditional formatting to the report so that any Discount values that are greater than or equal to $20 display in a bold, green font. Don’t include the $ symbol when entering the criteria. Choose the Green 5 font from the Standard Colors (seventh column, sixth row).
  12. Save your changes.
  13. View the report in Print Preview.
    1. Click One Page [Print Preview tab, Zoom group]. The report should look similar to Figure 4-158.Print Preview of report excerpt displays header, records grouped by DoctorID, and date and page number in footer.Figure 4-158 Completed RentInvoices report
    2. Close Print Preview.
  14. Close the report.
  15. Close the database.
  16. Upload and save your project file.
  17. Submit project for grading.
Author Image

About the Author

Daniel Kevins

Follow me

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}