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:
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
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
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.
Quality Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account.
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. We will always strive to deliver on time.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
If you are looking for exceptional academic writing services, then you are in the right place. Studyacer offers customised academic assignments and academic papers for students at all levels.
Writing an essay can be a challenge. However, we know what every student needs. And we purpose to deliver. Here at StudyAcer, we do all we can to help with academic essays and assignments. We have a dedicated team of professional essay writers.
We understand students need satisfactory results. Our cheap assignment writing service helps and never leaves any doubt. We always strive to ensure the ultimate and best results. It is our joy to offer a cheap reliable essay writing service.
Have you been asking yourself, where can get a pre written research papers for sale? Worry no more, the fact that you have a term paper that is due tonight and you haven’t touched it. At Study Acer it is our responsibility to get your paper on time.