Challenge Yourself 5.4
In this project, you will work with the database for a worldwide health organization. You will modify tables to ensure consistent data entry and prevent data errors. You will add a calculated field and Total row to a table. You will create action queries to modify the Diagnosis table to assist with patient management. Finally, you will modify the SQL code for a query.
Skills needed to complete this project:
• Creating a Custom Input Mask
• Modifying Lookup Field Properties
• Hiding and Show Fields in a Table
• Adding a Calculated Field to a Table
• Adding a Total Row to a Table
• Creating Field Validation Rules
• Creating Record Validation Rules
• Understanding Action Queries
• Updating Records through a Query
• Creating a New Table through a Query
• Deleting Records through a Query
• Adding Totals to a Query
• Modifying a Query in SQL View
1. Open the start file AC2016-ChallengeYourself-5-4.
2. If necessary, enable active content by clicking the Enable Content button in the Message Bar. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
3. The Locations table lists the organization’s locations around the world. Open the Locations table and make the following changes:
a. Add an input mask to the LocationID field to require three letters followed by one required number and two optional numbers. Use the _ character as the data entry placeholder character.
b. Modify the lookup field properties for the LocationType field to limit data entry to values in the lookup list.
c. Modify the lookup field properties for the LocationType field to allow multiple values.
d. Hide the PatientAvg field so it is hidden in Datasheet view.
e. Save and close the table.
4. The Shipments table tracks shipments made to the organization’s locations around the world. Open the
Shipments table and make the following changes.
a. Add a calculated field named Loss to the far right side of the Shipments table to calculate the number of units lost for each shipment. Use the expression:
[QuantityShipped]-[QuantityReceived]
b. Display the Total row with the Sum for both the QuantityShipped and QuantityReceived fields.
c. Add a field validation rule to the QuantityShipped field to require the entry to be greater than or equal to 12.
d. Enter the following validation text: New shipments must contain at least 12 units.(Hint: Allow data violations in existing data.)
e. Add a record validation rule to require the date in the DateReceived field to be after the date in the
DateShipped field.
f. Enter the following validation text: Date received must be after date shipped.
g. Save and close the table.
5. Create an update query to update the Comments field for all patients who have not had a flu shot.
a. Add all fields from the Diagnosis table and use the criteria false for the FluShot field.
b. Update the Comments field to: Call patient to make appointment for flu shot.
c. Run the query. The query should update 211 records.
d. Save the query as UpdateFluShotComments and close the query.
6. Create a make table query from the Diagnosis table to add all records for patients with a negative diagnosis to a new table.
a. From the Diagnosis table include the fields DiagnosisID, PatientID, FluShot, and Diagnosis in the query.
b. Use the criteria Negative for the Diagnosis field.
c. Name the new table: ResultsNegative
d. Run the query. The query should copy 65 records to the new table.
e. Save the query as MakeTableResultsNegative and close the query.
7. Create a delete query to delete all records for patients with a negative diagnosis from the Diagnosis table.
a. Add all fields from the Diagnosis table to the query.
b. Use the criteria Negative for the Diagnosis field.
c. Run the query. The query should delete 65 records.
d. Save the query as DeleteNegativeResultsRecords and close the query.
8. Create a query to summarize the number of patients for each diagnosis per state.
a. From the Patients table, add the State field. From the Diagnosis table, add the Diagnosis and
Flushot fields.
b. Display the Total row, and then under the FluShot field, select Count.
c. Run the query. There should be 15 records in the results.
d. Save the query as FluShotsByState and then close the query.
9. Modify the Under18 query in SQL view.
a. Open the query Under18. Switch to SQL view. Modify the SQL code to display only records for patients under 18. Use this code:
WHERE Patients.Age <18
b. Run the query. The results should include 18 records.
c. Save and then close the query.
10. Close the database and exit Access.
11. Upload and save the project file.
12. Submit project for grading.
