In this project, you are tracking animals at the local shelter. You will modify the database to reduce data entry errors, add data validation rules, and set default values, You will create a parameter query, use advanced Expression Builder functions, and use date arithmetic to manipulate the data.
Step 1
Start Access. Open the downloaded Access file named Exp19_Access_Ch05_HOEAssessment_Animal_Shelter.accdb.Grader has automatically added your last name to the beginning of the filename.Click Enable Content on the Security Warning message bar.
Step 2
You decide to review the Animals table and require the AnimalName field, as you do not want animals added to the database without names. You will also set the default value for the AnimalType field to Cat, as cats are the most common animal in the shelter.
Open the Animals table in Design view. In the Field Properties pane, set the AnimalName field as a required field. An entry in the AnimalName field is now required for each animal. Now, set the Default Value for the AnimalType field to Cat. Because most of the animals in the shelter are cats, you set the default value to Cat. You now will test the changes you made to the table design.
Save the table and accept the warning about testing the data integrity rules.
Step 3
Switch to the Datasheet view of the Animals table and add a new record. Ensure the AnimalType is displayed as Cat by default. Enter the following information into the new record:
Gender: Male
Age: 3 months
DateFound: 11/7/2021
Notes: Shy. Brown and White
Weight: 5
AdoptionFee: 30
The AnimalName, Spayed/Neutered, Adopted, OwnerName, OwnerPhone, and DateOfAdoption fields should be left blank.
To test data validation rules, you have left a required field blank. Press TAB until you get an error message. An error message will appear indicating that you must enter a value in the AnimalName field. This is because you made the AnimalName field a required field, so every record must contain a value in that field. Click OK then add Popper as the animal name in the AnimalName field. Click another record to save the new record. Notice you no longer see an error.
Step 4
One issue Jimmy, the animal shelter office manager, reported was that volunteers have accidentally charged too much in adoption fees. The shelter keeps the fees low (under $50) to encourage adoption. You add a validation rule and validation text to enforce this rule.
Switch to Design view of the Animals table. For the AdoptionFee field, add <=50 as a Validation Rule in the Field Properties pane.
The maximum adoption fee is $50. You have added a rule that notifies the user when a value that is not less than or equal to $50 (that is, greater than $50) is entered in the AdoptionFee field.
Now, add The maximum adoption fee is $50. Please double-check the adoption fee. (include the period) as Validation Text. When a user enters an adoption fee that is too high, a message appears telling the user to modify the entry. Save the table and accept the warning about testing the data integrity rules.
Step 5
Switch to the Datasheet view of the Animals table. Replace the current value of the AdoptionFee field in the first record to 51. The validation text you entered earlier appears. After clicking OK to dismiss the error message, change the adoption fee in the first record to 20. Because $20 is an acceptable value, you do not receive an error message.
Step 6
You decide to add an input mask to the owner's phone number field so that all users follow a consistent data entry format.
Switch to Design view of the Animals table. For the OwnerPhone field, click the ellipsis (...) on the right side of the Input Mask property box. Selecting Try It within the Input Mask dialog box will display (___) ___-____. Position the insertion point at the first character and enter 5556667777 to verify that the mask displays the phone numbers as you would like them. Click Next twice and accept the mask with the default placeholder character.
Toggle the With the symbols in the mask, like this option, click Next then Finish. The data will be stored in the Animals table with the symbols (the parentheses and the dash). This is important to note if you plan on querying the data later.
Save the table. Switch to the Datasheet view of the Animals table. The phone numbers display in the preset format with parentheses and hyphens. Type your phone number into the first record, replacing the existing phone number, to test the input mask. Press ESC to return the record to its original state.
Step 7
Rather than typing in the name of an option (from a list of options) and risk the possibility of a misspelled or invalid animal type, you decide to create a lookup field that enables the volunteers to choose from a list.
Switch to Design view of the Animals table. For the AnimalType field, select Lookup Wizard from the list of Data Types. The Lookup Wizard launches. Verify that the I want the lookup field to get the values from another table or query option is selected. Click Next. Select Table: Types of Animals and click Next. Move the AnimalType field to the Selected Fields box. Sort by AnimalType and in the next window, accept the default column width. The column width should be fine here, but you could adjust to your preference.
When you click Finish, you will save the table and accept the warning about the potential for data being lost. The Lookup Field has now been established.
Step 8
Switch to the Datasheet view of the Animals table and add a new record with the information listed below. Note that once you enter an AnimalName, you are given an ID automatically:
AnimalName: Marcinni
AnimalType: (leave blank)
Gender: Female
Age: 1 year
DateFound: 10/1/2022
Spayed/Neutered: Yes
Notes: Parakeet. Green and yellow
Weight: 0.31
AdoptionFee: 50
The AnimalType, Adopted, OwnerName, OwnerPhone, and DateOfAdoption fields should be left blank. Select Bird as the AnimalType for Marcinni. The lookup field gives you a menu of animal types. Close the Animals table.
Step 9
After a few days of testing, you decide to modify the table containing the lookup values for the lookup field. Volunteers have pointed out the shelter does not have the facilities to care for snakes, but they can care for rabbits, which is not listed as an option.
Open the Types of Animals table in Datasheet view. Delete the Snake record and add a new record with Rabbit as the AnimalType. Close the Types of Animals table.
Open the Animals table in Datasheet view and add a new record with the following information, leaving any fields not mentioned blank:
AnimalName: Hoppy
AnimalType: Rabbit
Gender: Female
Age: 7 months
DateFound: 1/5/2022
Spayed/Neutered: No
Notes: Extremely friendly. Grey
Weight: 1.25
AdoptionFee: 35
When you select AnimalType, you should notice that Snake is no longer an option. Close the Animals table.
Step 10
After applying data validation rules to the tables, you will perform some advanced queries against the database. You will create a parameter query, use advanced Expression Builder functions, and use date arithmetic to manipulate the data.
Jimmy is hoping to create a query that will enable the volunteers to input the animal type and display all animals matching the type. He is hoping to also create a report based on that query to be viewed on the computer. In addition, he would like to create a query to display all animals dropped off at the shelter in a certain date range.
Create a new query and add the Animals table. Add the following fields to the query: AnimalName, AnimalType, Gender, Age, DateFound, Spayed/Neutered, and Notes. Type [Enter Animal Type] in the Criteria row for the AnimalType field. Save the query as Find Animal Type then run the query. Type Dog when you are prompted to Enter Animal Type, then click OK. All 6 dogs in the database are displayed. Close the Find Animal Type query then re-run the Find Animal Type query from the Navigation Pane. Enter Cat when prompted and click OK. Verify 35 records are displayed. Close the query.
Step 11
Make a copy of the Find Animal Type query called Find By Date. Open the Find By Date query in Design view and remove the criterion from the AnimalType field. Add Between [Enter Start Date] and [Enter End Date] in the Criteria row for the DateFound field. Save and run the query. Type 9/1/2021 when prompted to Enter Start Date and click OK. Type 12/31/2021 when prompted to Enter End Date and click OK. Verify 5 animals are displayed. Close the query.
Step 12
Create a Report from the Find Animal Type query. Type Dog when you are prompted to Enter Animal Type, then click OK. The same 6 records displayed earlier appear in report form. As this is designed to be viewed on screen and not printed, you will not worry about resizing fields. Save the report as Animal Report and close the report.
Step 13
Jimmy has requested some modifications to the database that you can achieve using advanced functions. You will assist him by setting a default date and rounding animal weights.
Open the Animals table in Design view and change the Default Value for the DateFound field to Date().The default value for all new records is the current date. Switch to Datasheet view, saving the changes when prompted, and scroll to the new record at the bottom of the table. The first blank record has a default value of the current date. Close the Animals table.
Step 14
Create a new query and add the Animals table. Add the following fields to the query: AnimalName, AnimalType, Age, DateFound, Notes, and Weight.
In the first column after the Weight field, create a new column that rounds the weight field. Use the Expression Builder to select the Round function (in the Math section of the Built-In Functions) and add it to the Expression window.
Round(<< number >>, << precision >>) displays in the Expression Builder.
Remove << number >>, << precision >> from the expression and replace it with Weight. Round(Weight) displays in the Expression Builder. Click OK then replace Expr1 in your new column with Rounded and run the query. Check the Rounded column against the Weight column to make sure animal weights are rounded to the nearest whole number. Save the query as Rounded Weights and close the query.
Step 15
Jimmy hopes to find data entry errors in his database. Due to the sheer number of volunteers, he has noticed mistakes in the database, and he would like an automated query to find any pets that are listed as adopted but do not have an owner name. You will help him to accomplish this by using an IsNull function inside an IIf function.
Create a new query, add the Animals table, then add the following fields to the query: AnimalName, AnimalType, Adopted, OwnerName, OwnerPhone, and DateOfAdoption. You will use a combination of an IIf and IsNull function to find animals that are listed as adopted but do not have owners listed.
Add Yes to the Criteria row of the Adopted field. You will limit your query results to animals that are listed as adopted.
Step 16
In the first open column after the DateOfAdoption field, open the Expression Builder and select the IIf function (in the Program Flow section of the Built-In Functions) to add it to the Expression window.
IIf(<< expression >>, << truepart >>, << falsepart >>) displays in the Expression Builder.
Delete << expression >> and click Inspection in the Expression Categories section of the Expression Builder. Double-click IsNull.
IIf(IsNull(<< expression >>), << truepart >>, << falsepart >>) displays in the Expression Builder.
Replace << expression >> with OwnerName.
IIf(IsNull(OwnerName), << truepart >>, << falsepart >>) displays in the Expression Builder.
Replace << truepart >> with "Error" and falsepart with "Ok".
IIf(IsNull(OwnerName), "Error", "Ok") displays in the Expression Builder.
After clicking OK, replace Expr1 with ErrorCheck. Run the query. The first Error message appears for Animal #7 (Leo). Notice Leo is listed as adopted but has no owner. Jimmy can now review his records and fix the errors.
Save the query as Data Entry Check then close the query.
Step 17
Jimmy has been asked by a local animal control agency to provide a list of all animals that have been found in the month of February in any year. The agency is hoping to use this information to determine whether to add more part-time workers for February. You will help him get a list of all animals the shelter has collected during any February.
Create a new query, add the Animals table, then add the following fields to the query: AnimalType, Gender, Age, DateFound, Weight, and Notes.
In the first open column after the Notes field, open the Expression Builder and select the DatePart function (in the Date/Time section of the Built-In Functions) to add it to the Expression window.
DatePart(<< interval >>, << date >>, << firstdayofweek >>, << firstweekofyear >>) displays in the Expression Builder.
Delete << firstdayofweek >> and << firstweekofyear >> as well as the extra spaces and commas.
DatePart(<< interval >>, << date >>) displays in the Expression Builder.
Replace << interval >> with "m" and date with DateFound.
DatePart("m",DateFound) now displays in the Expression Builder.
After clicking OK, replace Expr1 with Month. Run the query and examine the new column. The new column now contains a number corresponding to the number of the month of the year (for example, 1 for January and 12 for December).
Step 18
Switch to Design view and add 2 in the Criteria row for the newly created Month column. Run the query. The 2 animals found in the month of February appear. Save the query as February Animals then close the query.
Step 19
Close all database objects. Close the database and then exit Access.