Major Project 3 - Access – K200 Microcomputer Applications in Kinesiology – Spring 2018

Major Project3 - Access – 150 Points

Purpose & Introduction. Thus far in K200 you have been working out of the textbook following step-by-step instructions to create and edit databases. This helped you learn skills in creating and editing databases. Now you will need to use those learned skills to complete the below projects.

There are three parts to Major Project 3 - Access: Part 1 - Skills Review; Part 2 -Instructor CD Database; and Part 3 -My Items Database. Each will require you to apply what you have learned about Access.

Student Data Files. You will only need a student data file for Part 1 - Skills Review. See Part 1 - Skills Review instructions.

Submit for Grading. Electronic copies of the major project files must be in the Major Project #3 - Accesssection inCanvas Assignments by 11:59 pm. Wednesday, March 21, 2018. If your work is not in this section it will not be graded. You will not submit any printed material.

Files to be submitted:

  1. Lastname_Firstname_acc01_SRDepartments.accdb; and
  2. Lastname_Firstname_Instructor_CD_Database.accdb; and
  3. Lastname_Firstname_My_Items_Database.accdb.

TASKS: Part 1 - Skills Review; Part 2 - Instructor CD Database; and Part 3 - My Items Database

Part 1 –Skills Review, pp. 539-540

Assess Skills: 1. Create new database; 2. Import data; 3. Create relationships; 4. Edit database.

Follow the instructions given on pages 539-540 of your Skills for Success with Microsoft® Office 2016 Volume 1textbook. You will start with a blank database and the Excel file named acc01_SREmployees.You will find the Excel file your Access Chapter 1: Create Database Toolstextbook files. This part is worth 20 points.

Criteria for Success: Grading Rubric:

  • Correct database has been created according to the textbook. (-10 if anything incorrect or missing)
  • Must see all of each field. No data or label hidden due to narrow column. (-10 if anything incorrect or missing)
  • Correct fields have been made. (-10 if anything incorrect or missing)
  • Correct relationship has been made.(-10 if anything incorrect or missing)
  • Must show relationship report. (-10 if anything incorrect or missing)
  • Database has correct name: Lastname_Firstname_acc01_SRDepartments.accdb. (-10 if incorrect or missing)
  • Submitted to Major Project #3 – Access in Assignments. (-20 if not done)

Part 2 –Instructor CD Database

Assess Skills: 1. Critical thinking for designing a database; 2. Entering and organizing data in a table; 3. Creating forms and reports without default formatting; 4. Creating queries.

Follow the below instructions to create your Instructor CD Database. This part is worth 65 points.

This purpose of Part 2 is to give you practice in creating a database. One of the toughest parts of database management is creating the database. When creating a database you must answer the following questions:

1.)Why do you need this database?

2.)What does this database need to do?

3.)Will I be able to update and change it in the future?

The most important thing to remember is THERE IS NO ONE WAY TO CREATE A DATABASE! All of us will create databases differently. That is normal. What I need to see from you is:

1.)You have some kind of organization in your database.

2.)Anyone else could take over your database and manipulate it and add to or subtract from it.

You will create a database called Lastname_Firstname_Instructor_CD_Database.accdb.Below is a list called CDs for InstructorCD Database. The list is made of 10 CDs. This is the list you will use to make your database table.Give the table a name you feel is appropriate. You must decide how to categorize and organize the CD information. (Hint: Look at the CD Queries. In order to create those queries, what information will you need to have in your table?)All 10 of the CDs must be in the database; however how you organize the CD information is up to you. Yes, some information from each CD will be missing. Yes, not all the information will add up for every CD (i.e., there will be artist listings for some and not for others). How you deal with these inconsistencies is up to you.

Your CD table must have descriptions for each field in the design view. Give the table a name you feel is appropriate and that described the content of the table. You will then make a form and a report using the table data. Both form and report must NOT have default formatting and be colored pink, purple or beige. You will also need to answer the queries listed below the CD list. For all objects, make sure all fields and data are visible and not hidden by column lines.

Created database must have:

  1. One table with the ten CDs listed below.
  2. One form created from the table. Must have purple, pink, or beige formatting.
  3. One report created from the table. Must have purple, pink, or beige formatting.
  4. Five queries – see list below.

CDs for Instructor CD Database.

  1. Tamlaeyn 2.0: A Rake’s Time in Muses for Embrace the Muse Season 3; burned CD by D.M.; 19 songs by various artists; Spring 2005.
  2. The Lord of the Rings – The Return of the King soundtrack. Music by Howard Shore; published 2003; 19 songs.
  3. Hail, Sousa! University of Michigan Band; published 1985; Songs by John Phillips Sousa; 15 songs.
  4. V; burned CD; Spring 2005, various artists
  5. Baroness Eleanor Elise apEiluned of Highground - Soundtrack v.1 for Changeling Game Embracing the Muses; burned by M. L.; Spring 2005; 19 songs; artist: Big Country.
  6. Desert Roses 3; Compilation CD; Various artists; published 2004; 13 songs.
  7. Dino Season Four: What Would Percival Do?; burned by K. N.; Spring 2005; 20 songs; various artists.
  8. Bebe Le Strange; artist: Heart; music written and performed by Heart; 10 songs; published 1980.
  9. Kaye Arden 2 CD Set: Into the Woods and Nightmares in the Woods Spring 2005; CD 1: 17 songs; various artists; CD 2: 16 songs; various artists; burned by H.D.
  10. Boston; artist: Boston; published 1976; 8 songs; all songs performed by Boston

CD Queries:

  1. Which CDs were burned? Name this query Burned.
  2. Which CDs were created in 2005? Name this query 2005.
  3. Which CD has Big Country as the artist? Name this query Big Country.
  4. How many CDs have various artists? (Note: A compilation also means the CD has various artists.) Name this query Various Artists.
  5. Which CD has the oldest publishing date? Note: Only one CD should be showing! Name this query Publishing Date.

Criteria for Success: Grading Rubric:

  • Database must be called Lastname_Firstname_Instructor_CD_Database.accdb.(-10 if incorrect)
  • Must have all 10 and only 10, CDs in the table, the form, and the report.(-10 if incorrect)
  • Form must have purple, pink, or beige coloring; no default formatting.(-10 if incorrect)
  • Report must have purple, pink, or beige formatting; no default formatting. (-10 if incorrect)
  • Must have all five queries and all queries must show complete and correct data.(-5 for each missing or incomplete query)
  • Must see all of each field! No data or label hidden due to narrow column or row in table or queries. (-5 if anything incorrect or missing)
  • All database objects share the same data.(-5 if incorrect)
  • Submitted to Major Project #3 - Access in Assignments.(-65 if not done)

Part 3–My Items Database

Assess Skills: 1. Critical thinking for designing a database; 2. Entering and organizing data in a table; 3. Creating forms and reports without default formatting; 4. Creating queries.

Follow the below instructions to create your My Items Database. This part is worth 65 points.

Created database must have:

  1. One table with at least 10 items (more is perfectly acceptable).
  2. One form created from the table. Must have purple, pink, or beige formatting.
  3. One report created from the table. Must have purple, pink, or beige formatting.
  4. Four queries – list below.

It is now time for you to create your own database!The purpose of Part 3 is to get you thinking about database design and creation using objects you know. You will design a database around 10 or more items that you own. You will name the database Lastname_Firstname_My_Items_Database.accdb. You will give the database objects, such as the table, form, report, and queries appropriate names. The names Query1 and Table1 will NOT be accepted as appropriate names. How you design your database will be up to you but make sure you can answer the queries listed below.

At the heart of your database is one table listing at least 10 items that you own. For example, kitchen utensils, DVDS, CDs, toys, books, textbooks, keepsakes,socks, etc., can be in this table. A good example of this is Part 2 – Instructor CD Database. It can even be a mixture of items, such as wash clothes and t-shirts. You may use a primary key or not. That is up to you.

From the table you will make a form and a report. Both the form and the report must NOT have the default formatting and must be colored pink, purple or beige. You will also need to answer the below queries. Use the queries to help you design your table. In other words, if you can’t answer the below queries, then you have not created a viable table.

When makingyour table you must give the fields appropriate names. For example, a field name should not be “Dinosaur”. Make a form and a report using your table. For the form and the report, do not use the default formatting and be sure they are colored pink, purple, or beige. For all objects, make sure all fields and data are visible and not hidden by column or row lines.

Queries:

  1. What was the first item(s) in the table that I owned? Show only first item(s) acquired.Give query an appropriate name.
  2. What is the last item(s) in the table that I owned? Show only last item(s) acquired. Give query an appropriate name.
  3. Which item(s) do I use every day? Give query an appropriate name.
  4. Which two and only two items, would you want with you if stranded on a desertisland? Show only two items.Give this query an appropriate name.

Criteria for Success: Grading Rubric:

  • Database must be called Lastname_Firstname_My_Items_Database.accdb.(-10 if incorrect)
  • All database objects must have appropriate names.(-5 for each incorrect or misleading name, e.g., toothbrush not named coffee table)
  • Must have at least 10 items in database table. (-10 if incorrect)
  • You must have 1 table, 1 form, and 1 report.(-10 if incorrect)
  • Form must NOT have default formatting and be coloredpurple, pink, or beige.(-10 if incorrect)
  • Report must NOT have default formatting and be colored purple, pink, or beige. (-10 if incorrect)
  • You must have 4 queries and all queries must show complete and correct data. (-5 for each missing query or data)
  • Queries are properly named. (-5 for each incorrect or misleading name, e.g., no queries should be named “Elephant”)
  • Must see all of each field! No data or label hidden due to narrow column or row in table or queries. (-5 if anything incorrect or missing)
  • All database objects share the same data.(-5 if incorrect)
  • Submitted to Major Project #3 - Access in Assignments.(-65 if not done)

mp-3-access-sp-2018.docxMargaret Lion, 2012 (c)Updated 2018 Page 1 of 5