Rogers State University Simple Query Update Append and Delete Project - Prolificstudies (2023)

This lab assumes you are using MS Access 13 or above. You may use a higher version; however, be aware that there may be slight changes in the different versions. If your submission does not work in MS Access 13 or above, it is considered non-functioning and you will receive a zero.

TOTAL: 36 points

(Video) 10 Most Dangerous Prison Inmates In The World

  • Part A: 39 points
    • (5 pts) Required changes made to the items table
    • (3 pts) Update Type Query meets specifications
    • (4 pts) Item Split Form meets specifications
    • (3 pts) Update Query meets specifications
    • (3 pts) Reorder Filter Query meets specifications
    • (5 pts) Delete ‘M’ Query meets specifications
    • (5 pts) Table Properties has a description for the Items table that includes first and last name
    • (6 pts) Item Status Report meets specifications
  • Part B: 1 point
    • Follows requested project submission format

Getting Help

If you have a question, please post on Piazza, and we will answer as quickly as possible. We will try to answer questions and provide help within 24 hours of your request. If you do not receive a response in 24 hours, please send the request again.

Although we will answer questions, provide clarification, and give general help where possible up until the deadline, we will not help you with your submission within 24 hours of the deadline. We will not provide any help after the deadline.

(Video) He Took A Photo Of His Pregnant Wife, But When He Saw The Photo


This is an individual lab assignment. You must do the vast majority of the work on your own. It is permissible to consult with classmates to ask general questions about the assignment, to help discover and fix specific bugs, and to talk about high level approaches in general terms. It is not permissible to give or receive answers or solution details from fellow students.

You may research online for additional resources; however, you may not use material that was written specifically to solve the problem you have been given, and you may not have anyone else help you write the material or solve the problem. You may use resources found online, providing that they are appropriately and clearly cited, within your submission.

(Video) Richard Dolan: The Wilson Memos & Bob Lazar

By submitting this assignment, you agree that you have followed the above guidelines regarding collaboration and research.

In this lab, you will learn to:

(Video) Top 10 Celebrities Who Destroyed Their Careers On Late Night Shows

  • Use and Design MS Access Queries

Modifying a Database

Note: This is the third in a series of Database (Access) labs that build on each other. We will use a single database file and keep adding to that file as the labs progress. Please submit the version of the file as it exists after each lab is complete.

Problem: The manager of the Science Museum gift shop needs to change the database structure, add validation rules, and update records. Also, a volunteer at the gift shop was asked to add some items to the database. By mistake, the volunteer created a new database in which to store the items. These items need to be added to the Museum Gift Shop database

(Video) The Paulo Freire Birth Centenary Fest | 1st Day (6 December 2021)

  1. Open the “Lab7-lastname.accdb” database and File > Save As “Lab8-lastname.accdb”
    1. Note, if you get a message that says, “Security Warning – Some active content has been disabled”, click on the “Enable Content” button.
  2. Open the Items table in Design view
  3. Add a new short text field, “Item Type”, to the Items table.
    1. Note – When complete:
      1. The field MUST appear AFTER the Description field.
      2. This field will contain data on the type of item for sale.
      3. The valid item types are ACT (activity, game), BKS (book), and NOV (novelty, gift).
    2. In the properties General tab, make the field size 3
    3. Make the default value property “ACT”
    4. Click on the “…” button at the right of the “Validation Rules” property to bring up the Expression Builder dialog.
    5. In the Expression Builder dialog, create an expression that checks to make sure that the value is one of the three values specified above. Then hit OK.
    6. In the “Validation Text” field, enter a message that the user will see if the validation rule fails.
    7. Change the “Required” property to “Yes”.
    8. Go to the “Lookup” tab in the properties for this field, and change “Display Control” to “Combo Box”.
    9. Make the Row Source Type a “Value List”.
    10. Click on the “…” button at the right of the Row Source property to bring up the “Edit List Items” dialog.
    11. Fill in the “Edit List Items” dialog with the values above, and make “ACT” the default value, then click on “OK”
  4. Add a validation rule to the “On Hand” field to check to make sure that the On Hand value is between 0 and 50, inclusive. Add validation text as well.
    1. Note: you can add a validation rule either by using the Field Properties for the On Hand field or add a validation rule in the Table Properties property sheet.
  5. Save the items table.
    1. Note… A pop-up will appear that says “Data integrity rules have changed…” respond “Yes”.
    2. Another pop-up will appear that informs you that “Existing data violates the setting for the “Required” property…” Again, respond “Yes”.
    3. Another pop-up may appear which also gets a “Yes” response.
  6. Create a new query for the Items table. Make this an Update query to update the Item Type to “ACT”.
  7. Save this query as “Update Type Query” and run the query.
    1. You will get a pop-up that asks for confirmation… say “Yes”.
    2. Open the Items table to see these updates.
  8. Make a new form using the “More Forms” icon to select a “Split Form”, and save it as “Item Split Form”.
  9. View the Item Split Form in the Forms View, and use it to:
    1. Change the Item Type for items 6234, 6345, and 7123 to BKS.
    2. Change the item type for items 3663 , 4583 , 6185, 8196, and 8344 to NOV
  10. Save the changes, then open the Items table and make sure your updates are complete.
  11. Save and close all changes to your database, and exit Access.
  12. Download the Lab 8 Gift Shop New Items.accdb file and open this database.
  13. Create and run a query to append the data from the Additional Items table to the Items table in the Lab 8 Museum Gift Shop database (your existing database).
  14. Save the query as “Update Query” and close the Lab 8 Gift Shop New Items database.
  15. Re-open the Lab 8 Museum Gift shop database and Open the Items table. You should now see the additional items.
  16. Create a new Query by using the “Advanced” filter icon and “Advanced Filter/Sort” for the Items table.
  17. Filter the table to find all items with fewer than 10 items on hand and sort the filter by Item Type and Description.
  18. Apply the filter using the “Toggle Filter” icon, then save the query as “Reorder Filter”.
    1. Clear the filter from the Items table using the Toggle Filter icon again.
  19. Create a query to delete all records in the Items table where the description starts with the letter M. Run the query and save it as “Delete ‘M’ Query”.
  20. Right-click the Items table in the Object List pane. Click Table Properties, and add a description for the Items table that includes your first and last name.
  21. Save the changes to the table.
  22. On the Database Tools tab, click on the “Relationships” icon, to open up the “Relationship Tools” ribbon.
  23. In the Design Tab of the Relationship Tools Ribbon. If asked to open tables, open both the Vendor and the Items table.
    1. If there is already a line between Vendor/Vendor Code and Items/Vendor code, click on that line, and then click on the “Edit Relationships” icon.
    2. If there is no line between Vendors and Items, then click on the “Vendor Code” field in the Vendor table, and drag it onto the Vendor Code field in the Items table. When you do this, the Edit Relationships dialog should pop-up.
    3. The top of the dialog should already be filled in with Vendor table and Vendor Code selected on the left and Items table and Vendor Code selected on the right.
    4. Click on the “Enforce Referential Integrity” check box.
    5. Click on the “Cascade Update Related Fields” check box
    6. Leave the “Cascade Delete Related Records” box unchecked.
    7. Click on the “Create” button.
  24. You should now see the relationship (a line) between Vendor/Vendor code (1) and Items/Vendor Code (∞ indicating that many lines in this table can have a single vendor code.)
  25. Close the “Relationship Tools” tab by clicking on the X box inside the ribbon.
  26. Specify referential integrity between the Vendors table (the “one” table) and the Items table (the “many” table).
    1. Cascade the update but not the delete.
  27. Add the Item Type field to the Item Status Report.
    1. The new field should follow the Description field.
  28. Save the result and switch to the Report View. The result should look like:
    Rogers State University Simple Query Update Append and Delete Project - Prolificstudies (1)
  29. Save the workbook as Lab8-lastname.accdb


  • Required code naming and organization:
    • Lab8-lastname.accdb

Every lab will have required submission guidelines. Please read submission requirements carefully. Any deviations from specifications on future labs or projects will result in significant point deductions or incomplete grades.

Submit this file on MyCourses. To do so, go to:

(Video) Howard Sutcliffe: The Conservator's World

  • CS-105 > Assignment Submission > Lab 8
  • Attach your file and submit
    • Ignore the preview. It will not display correctly.
  • You may submit as many times as you like until the deadline. We will only review your final submission.

"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."


1. Howard Sutcliffe: The Conservator's World
(KSU Museum)
2. How To Start Dropshipping in 2023 (FOR BEGINNERS)
(Ac Hampton)
3. Neighbours Called Him Crazy, But He Had the Last Laugh
4. This Man Dug a Hole in His Backyard He Was Not Ready For What He Discovered There
5. Most embarrassing DUI stop of this trooper's career?
(Real World Police)
6. Mega-Studies Improve the Impact of Applied Behavioral Science
(Institute of Personality & Social Research)


Top Articles
Latest Posts
Article information

Author: Greg O'Connell

Last Updated: 05/19/2023

Views: 6318

Rating: 4.1 / 5 (42 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Greg O'Connell

Birthday: 1992-01-10

Address: Suite 517 2436 Jefferey Pass, Shanitaside, UT 27519

Phone: +2614651609714

Job: Education Developer

Hobby: Cooking, Gambling, Pottery, Shooting, Baseball, Singing, Snowboarding

Introduction: My name is Greg O'Connell, I am a delightful, colorful, talented, kind, lively, modern, tender person who loves writing and wants to share my knowledge and understanding with you.