• Under our current conditions, your company now requires that all employees have their vaccination status documented. Although most people would just put together an Excel spreadsheet, you know better. This is really a database application.
• You have just come away from a meeting where the required information was decided upon. Employees will require vaccinations, and subsequent "Boosters".
• The required fields are listed below.
• Your task is to automate this process, by creating the system using Microsoft Access. Create the application on your Desktop. Name the Access file as your last name. You will create the system using tables, forms, queries and reports. Make sure the forms and reports look good and properly display the information.
• You can use Alt-Tab to easy jump between these questions and your Access file.
• The system will use the two related tables described below. Create the tables according to the following data dictionary.

You will have to modify the information below to account for the relationship you'll be setting up. Each Employee can have many Vaccines.

Vaccine
Field	Data Type	Other Criteria
VaccineID	Autonumber	Set as a Primary Key.
VDate	Date/Time	Set as Required. Don't allow future dates. Set default to the current date
VType	Short Text, 15	Lookup. Types include "Moderna";"Astra-Zeneca";"Pfizer";"J&J".
Booster	Yes/No
GovCost	Currency	(Government Cost) Set default to $5.00, 2 decimal places, and don't allow negative values.

Employees
Field	Data Type	Other Criteria
EmpID	Short Text, 4	Set as a Primary Key.
FName	Short Text, 20
LName	Short Text, 20
Phone	Short Text, 15	Use an Input Mask.
Email	Hyperlink
Dept	Short Text, 20	Lookup. Types will include at least "Admin";"Marketing";"IT" and "Sales".
Exempt	Yes/No

1. Create the tables as above. You will add records in questions 3 and 4 below.................................................................... . __/20

2. Create the proper relationship between the two tables. Enforce Referential Integrity. ...................................................... . __/10

Forms: 3. Create a columnar form allowing entry of data into the Employee table. Name it Add Employees . Add 4 Employees into the table. Use your name in the first record, make up the others. ... __/5 4. Create a form with a subform allowing entry of new Vaccinations. Use all fields from both tables. Name it Add Vaccinations . Create at least 15 or more vaccinations. Set three of them as Boosters. Enter reasonable values for the other information. ................................................................................................................. . __/5 Queries:

5. Create a query named Cost that will return only those vaccines that cost more than a certain amount. You choose the amount. ............................................................................................................................................................ __/5

6. Create a Query that will use all fields in both tables, but only return vaccines that are not boosters. Name it Not Boosters . ............................................................................................................................................................. __/5

7. Create a Query named Older that will return the EmpID, name, Dept, VDate, VType and GovCost for vaccines orders older than a certain date. You pick the date. .................................................................. ....................... __/5 8. Create a parameter Query that will return all the fields from the Vaccination table and the names and Departments from the Employee table, but only for a certain Dept. You should be prompted for a Dept when it runs. Name this query Parameter. ........................................... ...................... __/5

Report: 9. Create a report called Vaccines by Dept that will list all the you feel would be relevant. List Employee names, Dept, VType, VDate and GovCost. Group on Dept, with totals on GovCost. .............................. __/10