GC Database & Courtyard Medical Plaza Exercise

Description

Open the

CourtyardMedicalPlaza-04

database start file.

The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

Enable content in the database.

Create a form using the

Blank Form

button.

Add all the fields from the

Physician

table in the following order: DoctorID, FirstName, LastName, PrimarySpecialty, OfficeNumber, and MoveInDate.

Close the

Field List

.

Save the form as AddNewPhysician.

Edit the form in

Layout

view, add a new column and new rows to the control layout, and modify their size.

Add two rows below the

Tenant Since

row.

Add one column to the right of the existing columns.

Adjust the

Width

of each column to 1.6″.

Adjust the

Height

of the last row to .4″.

Add command buttons, a title, and the date to the form.

Add a button into the bottom row of the left-most column to save a record [

Record Operations Category]

.

Change the wording to Save Physician Record.

Name the button AddPhysicianFormSaveButton.

Add a second button into the bottom row of the middle column to add a new record [

Record Operations Category]

.

Change the wording to Add New Physician.

Name the button AddPhysicianFormAddButton.

Add a third button into the bottom row of the right most column to close the form [

Form Operations Category

].

Use the existing wording for the button.

Name the button AddPhysicianFormCloseButton.

Add a title to the form header. Edit the title to include spaces between the words.

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.

Enter 1.1″ in the

Width

property and 4″ in the

Left

property for the

Auto_Date

control.

Save the changes to the form. The revised form should look similar to Figure 4-154.

Figure 4-154

AddNewPhysician

form

Restrict the form to data entry.

Open the

Property Sheet

for the form if necessary and select the Data tab.

Click the drop-down arrow in the

Selection

box of the

Property Sheet

and select Form.

Select Yes in the

Data Entry

property box.

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

Save the form.

Close the form.

Create a report using the

Report Wizard

.

Launch the

Report Wizard

.

Add all the fields from the

RentInvoices

table in the following order: InvoiceID, FKDoctorID, InvoiceDate, Description, DueDate, DateReceived, AmountDue, and Discount.

Accept the suggested grouping by

FKDoctorID

on the second page of the

Report Wizard

.

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).

Figure 4-155

Summary Options

dialog box

Choose a Stepped layout and Landscape orientation and deselect the

Adjust the field width so all fields fit on a page

check box.

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

.

Figure 4-156

RentInvoices

report created using the

Report Wizard

Close

Print Preview

.

Edit the report using

Layout

view so that your report matches Figure 4-157.

Switch to

Layout

view and open the

Property Sheet

if necessary.

Select both the

FKDoctorID

label and text box and change the

Width

to .6″.

Change the

Width

of the

InvoiceID

label and text box to .75″ and change the

Left

property to 1″.

Change the

Width

of the

Invoice

Date

label and text box to 1″ and change the

Left

property to 1.9″.

Change the

Width

of the

Description

label and text box to 2.8″ and change the

Left

property to 3″.

Change the

Width

of the

Due Date

label and text box to 1″ and change the

Left

property to 6″.

Change the

Width

of the

Date Received

label and text box to 1″ and change the

Left

property to 7.1″.

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.

Change the Width of the

Discount

label and text box to .7″ and change the

Left

property to 9.4″.

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”.

Remove the “FK” from the

FKDoctorID

label and insert a space between “Doctor” and “ID”. The updated labels should match Figure 4-157.

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″.

Click to select a

Summary for ‘FKDoctorID’

text box and change the

Width

to 6″ and the

Height

to .2″.

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″.

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″.

Save the report.

Switch to

Report

view. Scroll to the top of the report if necessary. The report should look similar to Figure 4-157.

Figure 4-157

RentInvoices

report after editing the layout

Switch back to

Layout

view.

Add an

oldest to newest

sort on the

InvoiceDate

field in the

Group, Sort, and Total

pane.

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).

Save your changes.

View the report in

Print Preview

.

Click One Page [

Print Preview

tab,

Zoom

group]. The report should look similar to Figure 4-158.

Figure 4-158 Completed

RentInvoices

report

Close

Print Preview

.

Close the report.

Close the database.

Upload and save your project file.

Submit project for grading.