USING MICROSOFT ACCESS 2013 Independent Project 3-6 - Courtyard Medical Plaza (Full answer in 2025)

USING MICROSOFT ACCESS 2013 Independent Project 3-6

Independent Project 3-6 Courtyard Medical Plaza wants to create a query to provide details about its tenants and their rental payment history. The starting database file is provided for you. Use Design view to create the query. Edit the query to add calculated fields. After saving and testing the query, edit the query to add a parameter.

Skills Covered in This Project

 Create a query in Design view.

 Edit a query in Design view.

 Add compound criteria to a query.

 Execute a query.

 Save a query.

 Sort query results.

 Add a parameter to a query.

1. Open the CourtyardMedicalPlaza-03.accdb 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, and save it.

3. If needed, enable content in the security warning.

4. Create a new query in Design view. The query should display the rental invoice history.

a. Add both tables into the Query Design window.

b. Increase the size of the table objects to display all of the fields.

c. Add the following fields into the query: LastName, InvoiceID, DueDate, DateReceived,

AmountDue, and Discount.

d. Run the query to make sure it works. The datasheet should display 54 records.

5. Save the query as InvoiceHistory.

6. Edit the query in Design View to add calculated fields.

a. Insert a column to the left of InvoiceID.

b. In the inserted column, add a calculated field to concatenate the FirstName and LastName

fields using the following formula: Doctor: [FirstName] & " " & [LastName].

c. Don’t show the LastName field. In the next step you will use that for query criteria.

d. Insert a column to the left of Discount.

e. In the inserted column, add a calculated field to determine the number of days early the rent

was paid using the following formula: DaysPaidEarly: [DueDate] - [DateReceived].

f. To the right of the Discount field enter a calculated field to determine the net amount of rent due

with the following formula: NetRent: [AmountDue] - [Discount].

7. Save your changes and run the query. The datasheet should look similar to Figure 3-107 and display a

total of 54 records.

Step 1

Download start file

Access 2013 Chapter 3 Creating and Using Queries Last Updated: 2/27/15 Page 2

USING MICROSOFT ACCESS 2013 Independent Project 3-6

8. Edit the query to add parameters to search either by last name or by a date range.

a. In the LastName field, type [Enter the last name or leave blank to select all doctors] as the

criteria.

b. In the DueDate field, type Between [Enter the Starting Date] And [Enter the Ending Date] on the Or row.

9. Sort the results in ascending order by LastName and DueDate.

10. Save the query.

11. Run the query.

a. In the Enter the Last Name Enter Parameter Value dialog box, type Flores and click OK.

b. In the Enter the Starting Date Enter Parameter Value dialog box, don’t enter anything

and click OK.

c. In the Enter the Ending Date Enter Parameter Value dialog box, don’t enter anything and

click OK.

d. The datasheet should show the six payments received from Dr. Flores (Figure 3-108). Dr. Flores

paid his rent early three months this year.

12. Switch back to Design view and run the query again.

a. In the Enter the Last Name Enter Parameter Value dialog box, don’t enter anything and click OK.

b. In the Enter the Starting Date Enter Parameter Value dialog box, type 6/1/2014.

c. In the Enter the Ending Date Enter Parameter Value dialog box, type 7/1/2014. d. The datasheet should show the 18 invoices with a due date between 6/1/2014 and 7/1/2014

(Figure 3-109).

13. Close the query.

14. Reopen the query in Design

view. Notice that Access has

moved the LastName field to

the right side of the Design grid

and also entered a copy of the

DueDate field with the Show

row check box deselected. This

is due to the way Access

processes the query (Figure 3-110).

15. Save and close the database.

16. Upload and save your file.

17. Submit project for grading.

Step 2

Upload & Save

Step 3

Grade my Project

#SIMNet
#MicrosoftAccess
#IndependentProject
#Access
#Microsoft