Excel Project

AddNewPhysidan \..._ ______________ L _______ x--1 Add New Physician 09-Dec-17

Doctor ID

First Name

Last Name

Specialty

Office

Tenant Since

11 c·· ···· :=IR=ob=e=rt======I: : : : : : : : : : :

!Beyer I: . : : .... ~lca===,d=,o=v=a=sc=u=,a=,=o=,s=e=as=e=al: : : : : : : : : : · · · · · ·

:=110=1======1: ..... ::::::::::··· !2/s/2012 1: : : : · ~~-~~---- ~- -.. - .~ . .. · ................ .

Record: I~ 1 of 16 • ti • · I ~ No Filter I Search I

4-149 AddNewPhysician form

USING MICROSOFT ACCESS 2016 Independent Project 4-6

Step 1: Download start file

Independent Project 4-6 Courtyard Medical Plaza wants to add a form and a report to its database. To ensure consistency, the starting file is provided for you. Create a blank form and modify the form in Layout view. Edit properties to restrict the form to data entry and add buttons. Next, create a report using the Report Wizard. In Layout view, modify the layout, add sorting, and add conditional formatting. Finally, preview the results. This project has been modified for use in SIMnet®.

Skills Covered in This Project • Create a form using the Form button. • Create a report using the Report Wizard. • Edit a form in Layout view. • Edit a report in Layout view. • Add the current date to a form. • Add grouping and sorting to a report. • Move fields within the control layout. • Add totals to a report. • Restrict a form to data entry. • Add conditional formatting to a report. • Test the functionality of a form. • Preview a report.

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 security warning.

4. Create a form using the Blank Form button. a. Add all the fields from the Physician table in the following order: DoctorID,

FirstName,LastName, PrimarySpecialty, OfficeNumber, and MoveInDate. b. Close the Field List. c. 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. a. Add two rows below the Tenant Since row. b. Add one column to the right of the existing columns. c. Adjust the Width of each column to 1.6". d. Adjust the Height of the last row to .4".

6. Add command buttons, a title, and the date to the form. a. Add a button into the bottom row of the left-most column to save a record [Record

Operations Category]. b. Change the wording to Save Physician Record. c. Name the button AddPhysicianFormSaveButton. d. Add a second button into the bottom row of the middle column to add a new record

[Record Operations Category]. e. Change the wording to Add New

Physician. f. Name the button

AddPhysicianFormAddButton. g. Add a third button into the bottom

row of the right most column to close the form [Form Operations Category].

h. Use the existing wording for the button.

i. Name the button AddPhysicianFormCloseButton.

Access 2016 Chapter 4 Creating and Using Forms and Reports Last Updated: 1/3/18 Page 1

Summary Options

What sumiary values would you like cala.etedl

Field SUm Avg fYi'1 Max

0 DD D Disco~t DD DD

OK

Cancel

- Show

@ ~ta· and Summary Q~maryOnly

D Calculate !;!ercent of total for SLITlS

4-150 Summary Options dialog box

USING MICROSOFT ACCESS 2016 Independent Project 4-6

j. Add a title to the form header. Edit the title to include spaces between the words. k. 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. l. Enter 1.1" in the Width property and 4" in the Left property for the Date control. m. Save the changes to the form. The revised form should look similar to Figure 4-149.

7. Restrict the form to data entry. a. Open the Property Sheet for the form if necessary and select the Data tab. b. Click the drop-down arrow in the Selection box of the Property Sheet and select Form. c. Select Yes in the Data Entry property box. d. 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

e. Save the form. f. Close the form.

8. Create a report using the Report Wizard. a. Launch the Report Wizard. b. Add all the fields from the

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

c. Accept the suggested grouping by FKDoctorID on the second page of the Report Wizard.

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

e. Choose a Stepped layout and Landscape orientation. f. Click the Finish button without making any changes to the suggested title. The preview of the

report should look similar to Figure 4-151. Don’t worry 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.

FKOOCtO'"ID 1nvorce.10 rnvOfce Date O@sc:riptlon Due Dateoatef'ecelvedmoont Due Dtsoount

14-Apr-17 May 2017Rent (Beyer & Oltrander) Ol-May-17 4/30/2017 ;1.250.00

46 15-SeJ,17 Octoba- 2017 Rent ~Beyer- & Ostrander) Ol-Ocl-17 9/30/2011 a.2so.oo 19 16-Jun-J7 July 201 7Ren!(Beyer & Ostrander) OJ-Jul-17 6/30/2017 ,1.2 50.00 28 14-AJH7 August 2017 Rent (Beyer & Ostrander) 01-Aug-17 B/1/2017 ;1.250.00

10 15-May-17 June 2017Rent [~yer& OS1.rander) 01-Jun-17 5/29/2017 ;1,250.00 $25.00

37 17-Aug-17 September 2017 Rent (Beyer & Ostrand 01-Sep-l 7 9/1/2011 a.2so.oo Summarv for_,'fJ(l)octorlD~=-..... l...i6 de:tsd records~ Sum !1.soo.00 1

38 17-Aug-17 Septemb.er 2017 Rent {Scher & Dyer} 01-Sep-17

4-151 Rentlnvoices report created using the Report Wizard g. Close Print Preview.

Access 2016 Chapter 4 Creating and Using Forms and Reports Last Updated: 1/3/18 Page 2

Invoices Doctor ID Invoice ID

l

1

46

19

28

10

37

Invoice Date Desuiption

14-Apr-17 May 2017 Rent (Beyer & Ostrander)

l.>-Sep--17 October 2017 Rent (Beyer & Ostra nder)

16-Jun-17 July 2017 Rent (Beyer & Ostrander)

14-Jul-17 Augu st 2017 Rent (Beyer & Ostrander)

15-May-17 June 2017 Rent (Beye r & Ostran der)

17-Aug-17 September 2017 Rent (Beyer & Ostrander)

Summa for 'FKDoctorlD' = 1 6 detail records

Due Date Date Received

01-May-17 4/30/2017

01-0ct-17 9/30/2017

01-Jul-17 6/30/2017

Ol-Aug-17 8/1/2017

Ol-Jun-17 5/29/2017

Ol-Sep-17 9/1/2017

Amount Due Discount

$1,250.00

$1,250.00

$1,250.00

$1,250.00

$1,250.00

$1,250.00

$25.00

Sum $7,500.00 '.

2

38

2

17-Aug-17 September 2017 Rent (Scher & Dyer)

14-Apr-17 May 2017 Rent (Scher & Dyer)

01-Sep-17

01-May-17

9/1/2017

S/1/2017

$1,100.00

$1,100.00

4-152 Rentlnvoices report after editing the layout

USING MICROSOFT ACCESS 2016 Independent Project 4-6

9. Edit the report using Layout view so that your report matches Figure 4-152. a. Switch to Layout view and open the Property Sheet if necessary. b. Edit the title label and the FKDoctorID, InvoiceID and DateReceived field labels to match

Figure 4-152.

c. Select both the FKDoctorID text box and the Doctor ID label and change the Width to .6". d. Change the Width of the Invoice ID label and text box to .75" and change the Left

property to 1". e. Change the Width of the Invoice Date label and text box to 1" and change the Left

property to 1.9". f. Change the Width of the Description label and text box to 2.8" and change the Left

property to 3". g. Change the Width of the Due Date label and text box to 1" and change the Left property

to 6". h. Change the Width of the Date Received label and text box to 1" and change the Left

property to 7". i. Change the Width of the Amount Due column 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.

j. Change the Width of the Discount column to .7" and change the Left property to 9.4". k. Change the Width of the text box that displays the sum of the Amount Due to 1". l. 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". m. Save the report. n. Switch to Report view. Scroll to the top of the report if necessary. The report should look

similar to Figure 4-152. o. 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. Choose the Green 5 font from the Standard Colors.

12. Save your changes.

13. View the report in Print Preview. a. Adjust the zoom to One Page. The report should look similar to Figure 4-153. b. Close Print Preview.

Access 2016 Chapter 4 Creating and Using Forms and Reports Last Updated: 1/3/18 Page 3

Invoices Doctor ID lrwoicelD Invoice Date Description

1

1 14-Apr-17 May 2017 Rent (Beyer & Ostrander)

10 15-May-17 June 2017 Rent (Beyer & Ostrander)

19 16-Jun-17 July 2017 Rent (Beyer & Ostrander)

28 14-J1.H7 A~ust 2017 Rent (Beyer & Ostrander)

37 17-Aug-17 September 2017 Rent (Beyer & Ostrander)

46 15-Sep-17 October 2017 Ren t (Beyer & Ostrander)

ummarvJoc..'fJ<DoctorJD' = Lt6_detai1Jecords Sum

4

13

2.2

Saturda December 9 201 7

14-Apr-17 May 2017 Rent (Flores & Ala mar)

15-May-17 June 2017 Rent (Flores & Alamarl 16-Jun-17 July 201 7 Rent (Flores & Alamar)

Due Date

01-May-17

01-Jun-17

01-Jul-17

01-Aug-17

01-Sep-17

01-0ct-17

01 -May-17

01-Jun-1 7

01-Jul-17

Date Recei""d

4/30/2017

5/29/2017

6/30/2017

8/1/2017

9/1/2017

9/30/2017

4/29/201 7

5/28/2017

6/29/2017

Amount Due

$1,250.00

$1,250.00

$1.250.00

$1,250.00

$1,250.00

$1,250.00

$1,350.00

$1,350.00

$1,350.00

mscount

$25.00

$13.50

$27.00

$13.50

?a 1 of 3

4-153 Completed Rentlnvoices report

USING MICROSOFT ACCESS 2016 Independent Project 4-6

Step 2 Upload & Save

Step 3 Grade my Project

14. Close the report.

15. Close the database.

16. Upload and save your project file.

17. Submit project for grading.

Access 2016 Chapter 4 Creating and Using Forms and Reports Last Updated: 1/3/18 Page 4