In this project you will continue working with the greenhouse database from Chapter 1 Challenge Yourself 1.3. You will add a new table to keep track of the fertilizers used in the greenhouse and the plants that use them. You will make changes to the Plants, Employees, and MaintenanceLog tables including adding fields, deleting fields, and modifying field properties. You will also create and modify table relationships.
Skills needed to complete this project:
Creating a Table in Design View and Setting the Primary Key (Skill 2.8)
Modifying the Field Size Property (Skill 2.13)
Renaming Fields (Skill 2.3)
Setting the Default Value Property (Skill 2.12)
Adding Fields in Datasheet View (Skill 2.4)
Adding a Lookup Field from Another Table (Skill 2.16)
Deleting Fields in Datasheet View (Skill 2.6)
Working with Attachment Fields (Skill 2.15)
Adding a Lookup Field from a List (Skill 2.17)
Inserting, Deleting, and Moving Fields in Design View (Skill 2.9)
Using Quick Start to Add Related Fields (Skill 2.5)
Moving Fields in Datasheet View (Skill 2.7)
Changing Data Type (Skill 2.10)
Formatting Fields (Skill 2.11)
Creating Relationships (Skill 2.18)
Enforcing Deletions and Updates in Relationships (Skill 2.19)
Important: Download the resource files needed for this project from the Resources link. If they download in a zipped folder, make sure to extract the files after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.
Steps to complete this project:
Open the start file AC2019-ChallengeYourself-2-3-ALT.
If the database opens in Protected View, click the Enable Content button in the Message Bar at the top of the database so you can modify it.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
Create a new table in Design view using the following information.
Table AC 2.5 displays the field name, datatype, and caption information for the Fertilizers Table.
Field Name Data Type Caption
FertID Short Text
FertilizerName Short Text Fertilizer Name
NutrientRatio Short Text Nutrient Ratio
FertilizerForm Short Text Fertilizer Form
Price Currency
Set the FertID field as the primary key.
Save the table with the name: Fertilizers
Close the table.
Open the Plants table and make the following changes. You may work in Datasheet view or Design view as appropriate.
Set the field size for the PlantID field to: 12
Rename the FlowerColor field to: BloomColor
Set the Default Value property for the BloomColor field to red.
Add a lookup field named PreferredFertilizer to the end of the Plants table to reference the FertilizerName field in the Fertilizers table. The lookup list should be sorted alphabetically by the FertilizerName data. The primary key field should be hidden. Enforce referential integrity.
Add an Attachment field after the PreferredFertilizer field. Add the image azaleas.jpg to the record for the Mountain azalea.
Delete the MaxHeightFeet field.
Save and close the table.
Open the Employees table and make the following changes. You may work in Datasheet view or Design view as appropriate.
Move the LastName field so it appears before the FirstName field. Hint: Make the change in Design view so the change is applied to the underlying structure of the table.
Add the Phone Quick Start fields between the FirstName and Position fields.
Modify the Position field to use a lookup list limited to the following values: Supervisor, Greenhouse Developer, Greenhouse Design Tech, and Garden Advisor. Hint: Use the Lookup Wizard to create the lookup list and enter the values yourself. Double-click the right border of the Col1 column in the wizard to AutoFit the column to the data. Don’t forget to limit data entries to the value in the list.
Save and close the table.
Open the MaintenanceLog table and make the following changes:
Change the data type for the Watered field from Short Text to Yes/No.
Change the data type for the Inspected field from Short Text to Yes/No.
Change the data type for the Pruned field from Short Text to Yes/No.
Change the format for the MaintenanceDate field to Long Date.
Save and close the table.
Review the table relationships and make the following changes.
Create a one-to-many relationship between the PlantID field in the Plants table and the PlantID field in the MaintenanceLog table. Enforce referential integrity.
Modify the relationship between the EmployeeID field in the Employees table and the EmployeeID field in the MaintenanceLog table so any deletions or changes to the EmployeeID in the Employees table will be carried through to the EmployeeID field in the MaintenanceLog table.
Close the Relationships window, saving the layout changes.
Close the database and exit Access.
Upload and save your project file.
Submit project for grading.
