Assignment is subject to corrections and changes! If printing this assignment, please be sure to check the online version for changes and to access any links

DUE DATE: See Laulima, Tasks, Tests & Surveys
POINTS:50 points
SUMMARY: Modify a database. Work with queries. Create reports.
SKILLS NEEDED:
  • Critical Thinking!
  • Tables: Modify a table in design view. Add & edit data.
  • Queries: Create deletion & update queries in design view with criteria. Modify a query. Create calculated fields.
  • Reports: Create reports utilizing basic reports, label reports. Report Wizard. Modify reports in design view.
  • Math: Know how to work with percents.

Assignment is subject to corrections and changes! If printing this assignment, please be sure to check the online version for changes and to access any links. PLEASE ALWAYS LOOK BACK AT THE WEB VERSION WHEN COMPLETING THIS ASSIGNMENT.

IMPORTANT: Software Assignment: Access Part A must be completed prior to starting this part. Please correct any errors from Part A in relationships or these queries: Midyear, Mailing List, Parts List, CRZ-2000 Customers before proceeding.
If errors are not corrected 5 pts will be subtracted from your score
.

Distance learning sections: Email the instructor if the instructions are unclear. If you have completed the lessons and training and still are not sure how to do a particular step, Email the instructor for help.

Classroom sections: Ask the instructor in class.

All work must reflect INDIVIDUAL effort. If you need help, ask the instructor.

Preliminary: Open the file CrazyComputers-yourname.accdb from your ICS101 database folder. If there is a security warning click the Options button and select Enable this content.

  1. Concept: Modify a Table
    1. (1 pt) Open the Vendor table in Design View. Add a LOOKUP field called Vendor Type at the end of the list of fields as shown below.

      Select I will type in the values then click next
      Only one column is needed. Enter the following values in that column:
      • Hardware
      • Software
      • Both
    2. (1 pt) In Datasheet View, enter values for the existing records as follows using the drop down list.
      • Record 10000: Hardware
      • Record 10001: Both
      • Record 10002: Software
      • Record 10003: Hardware
      • Record 10004: Software
      • Record 10005: Hardware
      • Record 10006: Hardware
      • Record 10007: Software
      • Record 10008: Hardware
      • Record 10009: Hardware
      • Record 10010: Both
  2. Concept: Edit data
    1. (.5 pt) In the Vendor table make the following changes to the record for Microsoft
      • The work phone should be: to 800-642-7676
      • The City should be Redmond
      • The Zip should be 98052
    2. (1 pt) In the Stock table in the Vendor# field
      • Use the Replace button to find all occurrences of 10001 and replace with 10000 in the Vendor# field. Be sure to select the field first. (It's the same replace that you would use in any other MS Office program)
  3. Concept: Create and use update & deletion queries. (Be sure to complete the lesson on Action Queries before attempting these steps!)
    1. (6 pts) Create a query in query design for the stock table and change the query type to Update. Vendor 10007 has decided to increase their prices 2% (which increases our costs) and this should be passed on to the customers in a price increase. Create an update query to update both the cost and price fields for parts from vendor 10007 only. You will need to use Builder to create a formula to update these fields. (Oh, oh percents again - review)
      Save the query as Update10007
      .
      Run the query - once and only once. (You should get a message that the database is updating 19 rows - if your number is different then click No, and check your query for errors - if you do the next query before this one your number of rows may be less.).
    2. (2 pts) Create query in query design for the stock table and change the query type to Delete Parts 10064 through 10076 should be delete from the parts table. Save the query as Delete Parts.
      Run the query. (You should get a message that the database is deleting 13 rows - if your number is different then click No, and check your query for errors.).
  4. Concept: Modify a query.
    1. (2 pts) Open the query Midrange in Design View. Create a calculated field called Sale to show the price with a 5% discount. You will need to use Builder to create a formula for the new field.
      Save the query and close it.
  5. Concept: Create Reports based on tables & queries
    1. Create a report based on the customers table, Use the Report button from the Create menu
      1. (.5 pt ) Include fields: First Name, last name, city, phone numbers, date of first purchase (Delete any columns not needed)
      2. (1 pt ) Report should be grouped on city.
      3. (1 pt ) Shorten the width of fields so all the columns fit across the width of one page
      4. (.5 pt ) Add the picture, CCC-LOGO (from ICS101Assignments Folder) to the top of the report
      5. Save the report as Customers by City
    2. Use the labels button on the create ribbon to create mailing labels based on the mailing list query using.
      1. (.5 pt) Use Avery Label #5160
      2. (.5 pt ) Change the font to 11 points.
      3. (2.5 pt ) Be sure to have spaces between fields and field should be on the appropriate line for a mailing label. (If you don't know what a mailing label should look like, do a web search!)
      4. (.5 pt) Sort on Zipcode
      5. Save & close the labels
    3. Create a report with the Report Wizard based on the query Parts List
      1. (.5 pt) All fields from the query should be used
      2. (.5 pt) View data by vendor
      3. (.5 pt) Group by type    
      4. (.5 pt) Sort by Part Name
      5. (.5 pt) Outline layout should be used.
      6. (1 pt) Modify the report in design view to make sure all fields can be seen and are properly formatted. 
      7. (.5 pt) Change the title to Parts List by Vendor
      8. (1 pt) Move the title in the Report Header to the Page Header then close up the Report Header.
      9. Save the report and close it.
  6. Concept: Create and modify the layout of report.
    1. (3 pt) Create a report using the report wizard based on the Midrange query - using all the fields from that query (no grouping or sorting). Select tabular layout and save the report as Holiday Sale
    2. Modify the report
      1. (.5 pt ) Change the title at the top of the page to read Christmas Gift Ideas
      2. (.5 pt ) Center the heading across the page
      3. (.5 pt ) Add the logo picture (CCC-logo.gif) to the top of the page.
      4. (.5 pt ) Change the formatting for the Sale field to Currency.
      5. (.5 pt ) Change the column widths so that the Price & Sale Price columns are narrower and the Part name is wide enough to see all of the field items (you may need to look at the field in View mode)
      6. (.5 pt ) Change the page headers to read as follows:
        Sales Items Description Original Price Sale Price
      7. (.5 pt ) Delete the items in the footer
      8. (.5 pt ) Insert a label in the footer then enter the following lines .
        Four Locations to Serve You: Kailua, Honolulu, Kapolei, Hilo
        Open Daily 9am to 9pm
      9. (.5 pt ) Center the footer across the page
  7. Create a postcard to notify all customers of the CRZ2000 of a problem with their computer and modify the report in design view.
    1. Start by using the labels button on the create ribbon to create mailing labels based on the CRZ2000 Customers query using.
      1. (.5 pt ) Avery Label #5389
      2. (.5 pt ) Change the font to 11 points.
      3. (2.5 pt ) Be sure to have spaces between fields and field should be on the appropriate line for a mailing label.
    2. Modify the report in design view (Don't confuse mailing labels which is a type of report and labels which are elements within reports which allow you to enter text.)
      1. (.5 pt) Shorten the width of the mailing label fields so that it is 3 inches wide and located to the right of the postcard area.
      2. (.5 pt) Use the Insert Image icon to insert the CCC-Logo.gif (after selecting the file name, you will need to draw a box.) The image should be at the top on the left hand side of the report
      3. (.5 pt) Beneath the logo image, add a label with the following information
        We are sorry to inform you that a computer you purchased from us recently has a defective video card. The affected computer is:
      4. (.5 pt) Format the text in the label to be 14 pt and black text color (not the background)
      5. (.5 pt) Beneath that label use Add existing fields to select the field Part Name, (Drag the field)
      6. (.5 pt) Format the field to be centered 14 pt and black text color (not the background) and widen the field
      7. (.5 pt) Beneath the field add in a label with the following information
        Please call our office at
        (808)222-1234
        to schedule a free on site repair
      8. (.5 pt) Format the text in the label to be centered, 14 pt and black text color (not the background)
      9. (.5 pt) On the properties sheet for the detail, change Force Page Break to Before Section
        Properties sheet showing the detail selected and force new page break set to before section
      10. (.5 pt) Be sure that the width (6") and length (4") of the page has not been changed.
      Report in design view should look like:
      Visual layout of report
    3. Save the report. Open the report in report view to check that all of the labels and fields can be viewed. If any items do not show the entire contents, return to design view and increase the size of the items.
  8. Experiment with queries & reports
    1. (6 pts) Create a new select query of your choice. The query can be based on any of the tables (or multiple ables) within the database and must have criteria on 2 different fields. The query must have enough fields added so that it useful information. Save and name the query appropriately.
    2. (2 pts) Create a report based on the query created in step a - above. The report must contain all of the fields from your query. Save and name the report appropriately.
  9. Save and close all open reports, tables, etc.
  10. Database files tend to become rather large and sometimes contain extra space.  To get rid of excess, it is important to Compact the database.  Click on the File tab then select Compact & Repair Database on the right hand side. ( If using Office 2007 - Click on the Office button then Manage and select Compact & Repair Database.) If working on campus you may receive an error if saving files on the network.
  11. Exit from Access. IMPORTANT! Exit the program before submitting the file! I may not be able to open your file otherwise. Submit on Laulima in Tasks, Tests, & Surveys section
Need Help?
Contact the instructor
via UH email
Please attach your file to the email.

If printing out this assignment. Please be aware that corrections or changes may be made to the assignment at any time. Please check back to the web version to check for changes. Also there may be links on this page that you need to access for the assignment - please go back to the web version to access the links.