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 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 Open the Locations table and make the following changes:
   1. Add an input mask to the LocationID field to require three letters followed by one required number and two optional Use the _ character as the data entry placeholder character.
   2. Modify the lookup field properties for the LocationType field to limit data entry to values in the lookup
   3. Modify the lookup field properties for the LocationType field to allow multiple
   4. Hide the PatientAvg field so it is hidden in Datasheet
   5. Save and close the
4. The Shipments table tracks shipments made to the organization’s locations around the Open the

Shipments table and make the following changes.

1. 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]

1. Display the Total row with the Sum for both the QuantityShipped and QuantityReceived
2. Add a field validation rule to the QuantityShipped field to require the entry to be greater than or equal to 12.
3. Enter the following validation text: New shipments must contain at least 12 units.(Hint: Allow data violations in existing )
4. Add a record validation rule to require the date in the DateReceived field to be after the date in the

DateShipped field.

1. Enter the following validation text: Date received must be after date
2. Save and close the

5. Create an update query to update the Comments field for all patients who have not had a flu
   1. Add all fields from the Diagnosis table and use the criteria false for the FluShot
   2. Update the Comments field to: Call patient to make appointment for flu shot.
   3. Run the query. The query should update 211
   4. 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
   1. From the Diagnosis table include the fields DiagnosisID, PatientID, FluShot, and Diagnosis in the
   2. Use the criteria Negative for the Diagnosis
   3. Name the new table: ResultsNegative
   4. Run the The query should copy 65 records to the new table.
   5. 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
   1. Add all fields from the Diagnosis table to the
   2. Use the criteria Negative for the Diagnosis
   3. Run the query. The query should delete 65
   4. Save the query as DeleteNegativeResultsRecords and close the query.

8. Create a query to summarize the number of patients for each diagnosis per
   1. From the Patients table, add the State From the Diagnosis table, add the Diagnosis and

Flushot fields.

1. Display the Total row, and then under the FluShot field, select Count.
2. Run the query. There should be 15 records in the
3. Save the query as FluShotsByState and then close the query.
4. Modify the Under18 query in SQL
   18. Open the query Under18. Switch to SQL Modify the SQL code to display only records for patients under 18. Use this code:

WHERE Patients.Age <18

1. Run the query. The results should include 18
2. Save and then close the
3. Close the database and exit

11. Upload and save the project

12. Submit project for

The post consistent data entry and prevent data errors. appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms