CIS 123 Database assignment: Coffee inventory

In this assignment you create a database of products for a store that sells coffee. You need a Microsoft Access 2010 or later. If you need a copy of Access and you have Windows, then you can get a copy as part of the Microsoft program that Kishwaukee CIS is a part of. Students currently enrolled in an active qualifying CIS course can download, install, and use a full copy of Access for their personal non-commercial use on their personal computer. Access does not run on Mac.

This problem uses a number of features of Access and it is expected that there will be questions. Keep in mind that the CIS 123 website has a FAQ sheet for using Access, there will be a demonstration of Access in one of the optional class sessions, and there are many tutorials on how to use specific features in Microsoft Access on the Web. This is particularly useful if you want a question answered quickly outside of class time and you don't have an Access reference at hand.

1. Download and save your individualized starting Access database and Excel workbook which you can get here. Remember where you download your files. Make sure you close Access BEFORE you upload your .accdb database file to the dropbox in Desire2Learn.
2. Do NOT work closely together or copy or share work with other students. The penalties can be quite severe. You are welcome to ask all the questions you want and to seek help from the instructor.
3. The database you download will already contain one table named Weight.
4. Keep in mind when creating tables that you have to set the required property of a field to Yes to make it required.
5. Note: In newer versions of Access, a text field that has a short length (<= 255) is considered a "Short Text" field for its data type.
6. Create a new table named Product using the Table Design view. It should have the following five fields, and ONLY the following five fields: (5 pts)
   1. ProductID: text field, 4 characters long, required, primary key
   2. CoffeeCode: text field, 4 characters long, required
   3. WeightCode: text field, 1 character long, required
   4. Decaf: text field, 1 character long, default value of D, not required
   5. Price: currency field, required
7. You might want to check to make sure you have the required property set to Yes for the four fields above which listed that.
8. Copy and paste the data from the Inventory.xlsx workbook into the rows (records) of the Product table you just created. (2 pts)
9. Delete the record with ProductID 2372 from the Product table. (1 pt)

1. Create a new table named Coffee by importing the data from the coffee.mdb file. (3 pts)
2. Change the field names to camel-casing. [This has to be done in datasheet view.] Camel-casing means that the first letter of every word in a field name should be uppercase, while all other letters should be lowercase. One example is that COFFEENAME would become CoffeeName. This step has to be done manually in datasheet view. Right click on a column name, choose Rename, and then manually type in the camelcase version. (1 pt).
3. Make the CoffeeCode field the primary key. (1 pt)
4. Change the format for the Decaf field to "True/False". This can be done in design view by changing the Format property for the Decaf field. (1 pt)
5. Close the Coffee table.

Using the Product table and query design view, create a query to find the ProductID, WeightCode, and Price where CoffeeCode is equal to "BRUM". Have the query sort the results in descending order by price, and don't show the CoffeeCode field. The sort is required to be done in design view, NOT datasheet view. Name the query BrumCoffees. (3 pts)

Note: The results of this query will look a little different after you change the data for the product with ProductID 2410 later in this project.

Note: The next step requires you to create a relationship. All tables and queries should be closed while you are creating a relationship.

Use the Relationships button on the toolbar of the Database Tools tab to relate the Coffee, Weight, and Product tables using the appropriate fields. Select ALL of the options to enforce referential integrity. This includes enforce referential integrity, cascade update related fields, and cascade delete related records. (3 pts)

Create a new query in design view using all three tables.

1. display the CoffeeType, CoffeeName, ProductID, Product.Decaf, Price and WeightSize fields (2 pts)
2. sort the data in ascending order based on the CoffeeName field; the sort is required to be done in design view (NOT datasheet view) (1 pt)
3. have the query only display records where the CoffeeType field equals "Flavored" (1 pt)
4. save the query with the name FlavoredCoffees (1 pt)

Create a new query based on the Product table using design view

1. display all fields from the Product table except "Decaf" (1 pt)
2. have the query only display records where the WeightCode field is not "A", and whose price is > 50 (4 pts)
3. add a calculated field named NewPricing that adds 3% to price; do NOT add a new field to the Product table - the NewPricing field should only exist in this query (3 pts)
4. modify the format of the NewPricing field to be currency format [You can change the format of a field by right clicking on the column header in design view and choosing to show the properties. The properties list should have a Format property where you can select Currency.] (1 pt)
5. sort the data in descending order based on the NewPricing field; the sort is required to be done in design view (NOT datasheet view) (1 pt)
6. save the query with the name NewPrices (1 pt)

Create a new query based on the Product table using design view

1. Note: This query uses what are called aggregate functions (min, max, count, average, etc.). Add whatever fields you want to perform such a function on. Add the field once for each aggregate function you want to perform on it. The Totals button on the toolbar can then be used to display the Total line in design view. The Total line lets you choose which aggregate function you want to perform. It is also used for grouping data.
2. create a field named LowestPrice and have it display the minimum price among all the records; remember to change the name of this field in the query (2 pts)
3. create a field named HighestPrice and have it display the maximum price among all the records; remember to change the name of this field in the query (2 pts)
4. save the query with the name LowestAndHighestPrices (1 pt)
5. check to make sure your results are correct

Create a form based on the Product table.

1. use the Form Wizard
2. use all the fields from the Product table (1 pt)
3. the form should use a columnar layout (1 pt)
4. save the form with the name Product Data

Update the Product table using the Product Data form.

1. find ProductID 2410 and make the following changes (1 pt)
   1. change the WeightCode field value to A
   2. change the Decaf field value to D
   3. change the Price field value to 8.99
2. find and delete the record with ProductID 4306 (1 pt)
3. add a new record with the following values (1 pt)
   1. ProductID: 2306
   2. CoffeeCode: AMAR
   3. WeightCode: A
   4. Decaf: nothing
   5. Price: 7.99
4. close the form
5. reopen the form and make sure the new record is now in order by ProductID

1. use the Coffee table as the primary table (choose it first) and use all fields in the Coffee table except the Decaf field (1 pt)
2. use the Product table as the related table (choose it after the Coffee table) and use all fields in the Product table except the CoffeeCode field (1 pt)
3. have the report show the data by Coffee (it should already be selected since it is the default)
4. specify CoffeeType as an additional grouping level (1 pt)
5. sort the detail records in ascending order by ProductID (1 pt)
6. leave the report with the default Stepped, Portrait layout
7. Save the report and name it ProductDetails

1. Excel workbook with Product table data for import
2. Old Access format file with Coffee table for import
3. BrumCoffees query results
4. Relationship result
5. FlavoredCoffees query results
6. NewPrices query results
7. LowestAndHighestPrices query results
8. ProductDataForm
9. ProductDetails report