Part 1 - Verifying Data entry in a Database
Open the Student databasein the Access Quiz Files folder, andbefore you begin, add the following fields to the Student Table after Student Telephone Number.
|
Field Name
|
Data Type
|
Field size
|
|
Student Degree
|
Short Text
|
4
|
|
Student Major
|
Short Text
|
40
|
A. Lookup Field: Using the Lookup Wizard add the following criteria to the Student MajorField.
1. Choose the option to type in the values yourself
2. Columns: 1
3. Enter the following values:
i. Accounting
ii. Business Administration
iii. Engineering
iv. Marketing
v. Nursing
4. Label: Student Major
5. Finish the wizard
B. Validation Rules and Text: For the field, "Student Degree", create the following validation rule and text
1. Set Default value to "AA"
2. The values entered must always be in Upper Case (assure that this is set correctly)
3. The validation rule is AA, AAS and AS (you must use the correct syntax and in upper case)
4. Set the Validation Text to display "Degree must be AA, AS, or AAS"
5. Save the table and verify the Validation Rule is working by adding entries to all rows.
6. Save the design
Part 2 - Database Relationships
Continuing to use the Student database, join Tables to form a relationship
1. To the Degree table, add the field, Student id - it must be identical in all characteristics to the field in the Student table as this will be a foreign key. Save the design.
2. Create a relationship between the Tables: Student, Degrees
3. Link the two tables using the logical keys that match in both tables
4. Make sure you select the appropriate options. Note there must be referential integrity enforced.
5. Close and save the relationship.
6. Save the Database.
Part - 3 Simple Queries
Continuing to use the Student database, create a new Query Design based upon the Student Table
ANDCompound Criteria Query with wildcard
1. Add criteria to select Student Last Name starts with "Bu" and city can be "Nanuet"
2. Run Query to verify results and Save Query as "AND Selection"
**Hint this involves 2 fields
Starting a new Query Design based upon the Student Table
Or Compound Query with wildcard
1. Add criteria to select Student Last Name starts with "Bu" or the city can be "Nanuet"
2. Run Query to verify results and Save Query as "OR Selection
**Hint this involves 2 fields
Part 4 - Complex Queries
A. Parameter Query:
Open the Green Clean database in Access QuizFiles folder
1. Using the Customer table, create a parameter query
2. The query must display Firstname, Lastname, Street, City, State and Zip when it is run.
3. Parameter is based upon the field for City
4. Message should display "Please enter city"
5. Test the query
6. Save query as "Parameter Query".
Continuing to use the Green Clean database create a Calculated Query
1. Using the Inventory table, create a query doing the following
a. The query must display InvNbr, Description, Cost, Price, and QuantityinStock when run.
b. calculate the profit for each item in inventory
c. The field name is Profit and the calculation is the difference between Price and Cost multiplied by QuantityinStock for each InvNbr. (hint: remember PEMDAS rules)
d. Run the query to test it
e. Save the query as Profit Query
2. Using the Employee table
a. Update all employees to receive a 5% increase starting immediately.
b. Save as Update Salaries
3. Delete Customer
a. Delete the customer whose last name is Johnson
b. Save as Delete Johnson
4. Close the database
Submit all files to the drop box for the Access Quiz in Blackboard
Attachment:- files.zip