Lab 8: Introduction to MS Access

A little heads up - This lab is pretty simple. :) 

Initial set up

1. You have a file, 'moviesDB.mdb' in your ICON. Download that. Double-click to open that.
2. This will be your starting point.  

General info about  MS Access
To view tables

1. Double-click on the 'Director:Table' to view the tables and values associated with it. You'll have something, that looks like this:

Switch to 'Design View'

1. Right-click on the table, and click 'Design view' as shown below

View relationship between tables

1. Select Database Tools -> Relationship. Maximize the smaller tables to view the relationships well.
2. Learn and understand: How director and movies table are related? What is the primary key? How is it represented there? What does the connection arrow signify? 

You assignment starts here..

What is a query?
 Queries are used to view a subset of your data or to answer questions about your data.  

Query: Which movies have titles that start with the letter R?  

Which table and data do you need for this query?

Table: Movies
Column: Title - From title select movies which start with R.

Create a query

• Click Create tab -> Query Design (on the ribbon) -> Movies (Inside Show table) -> Add. Follow the picture below. Here, you select the tables that is required to implement the query. Once you have selected the tables you want, close the 'Show table' dialog box.

• Select the column you want as shown below

• Let's assume that we want the Movie 'Titles' column sorted in Ascending order. Therefore, we specify "Ascending" in "Sort:" row (Similar to Field - select from the drop-down list).
•  Make sure that "Show" check box is checked.
• Since we want all movies that start with letter R, we will use R* in the 'Criteria' column.
• '*'(called as 'asterisk') is a wildcard character which represents any combination of letters and numbers. You use it when you know part of a value, and the rest of the data — such as the length or pattern of the value — isn't important.
• You can use the Like operator to find values in a field that match the pattern you specify. Here, the pattern is 'R*" i.e any movie starting with the letter R.
• Save your query: After you write your criteria, press 'ctrl' and 's' keys to save the query. Give it a proper name. After you save, your query will appear on the Left side pane. You can double-click it to see the tables you have generated after running the query. 

• If you want to make changes to your query, you have to switch to design view. To do that, right-click on your query (which is on the left side pane), and click 'Design View' from there. 
• You'll get a page, which looks similar to this. 

• In this way, you'll create the following queries:

1. Which movies were released before 1980, ordered by year of release? 
2. When were each of the Psycho movies released? (Remember: You should have movie title along with release year)
3. Which directors have last names that start with the letter S? (Remember: You should have Director's Last name (1st preference - Ascending order), First Name (2nd preference - Ascending order))
4. Which movies were directed by directors who were born after 1955?  (Remember: This query involves combination of both the tables. You should have Director's Last name (1st preference - Ascending order), First Name (2nd preference - Ascending order) and Birth Year along with Movie Title (Ascending order).)

• Some reference links:
• Like condition - http://www.techonthenet.com/access/queries/like.php
• Query example - http://www.baycongroup.com/access2007/05_access.html

Lab 7: Javascript

Initial set up

1. You have a file, 'Lab-7-start.zip' in your ICON. Download that. Copy it on your desktop. 
2. Remember to extract the file. Right click on the .zip file and click 'Extract all'. Now, you will have a folder named 'Lab-7-start'.
3. Go to the folder 'Lab-7-start'. 
4. To view the design: Right click on 'start-bean.html' - click 'Open with' - click 'Google chrome'.
5. To view the code: Right click on 'start-bean.html' and click 'Edit with Notepad++'. Now you will see the HTML/Javascript code. 
6. Now, you can edit in two ways:
1. Edit the Notepad++ and view the changes on browser by refreshing it
2. Or, copy all the code in your Notepad++ and paste them here http://codepen.io/pen/ and view the changes interactively. 

Know what you have!

1. Remember this? This is CSS. Click here for a quick intro. This is the place you'll focus if you want to change the styles like background image, colors etc.

<style type="text/css">

    body {background-color:saddlebrown; 

               color:darkorange; 

               font-family:helvetica; 

               text-align:center}

 

    table {margin-left:auto; 

               margin-right:auto; 

               text-align:center; 

               background-color:#993300; 

               border-style:solid; 

               border-color:firebrick; 

               border-width:medium; padding:8px }

</style>

2. Assign variables inside <script> tag which you'll be using in your code.

<script type="text/javascript"> 

       var shots=1; 

       var drink="none"; 

 </script>

3. HTML Forms

form tag: HTML forms are used to collect user input. 

action attribute: The action attribute specifies where to send the form-data when a form is submitted.

<form action="" > 

4. HTML table

The <table> tag defines an HTML table. The <tr> element defines a table row, the <th> element defines a table header, and the <td> element defines a table cell. More info on tables.

The <input> tag specifies an input field where the user can enter data. The type attribute specifies the type of <input> element to display. The default type is: text. More info on input tag attributes.

The value attribute is used differently for different input types:

• For "button", "reset", and "submit" - it defines the text on the button
• For "text", "password", and "hidden" - it defines the initial (default) value of the input field
• For "checkbox", "radio", "image" - it defines the value associated with the input (this is also the value that is sent on submit). 

The onclick attribute fires on a mouse click on the element. Here, on mouse click, shots variable is assigned with value 1. More info on HTML event attribute.

<table>

    <tr>

    <td><input type="button" value="1" onclick='shots=1'/></td>

......

5. More on onclick..

onclick can also be used to define a set of instructions (commonly called as 'function').

<td><input type="button" value="Total" onclick=

          
           'var price;                                        //initialize a variable 'price'

var taxRate=0.088;  //initialize a variable 'taxRate' with a initial value = 0.088

if (drink == "espresso") 

           //'if' is a conditional statement. If the variable drink's value is "espresso", then assign the variable //price to 1.40

  price=1.40; 

.......... 

6. Final note:

document.forms[0].total.value="0.00" means, Get the value of 'total'(variable) from the  <form> element (index 0 - first) in the document. More on DOM (Document Object Model).

7. Now that we have a good idea about HTML/CSS/Javascript, follow the instructions below and modify your initial html document to complete your Lab assignment 7.

1) Put a background image on the page that tiles over the whole page. (2 points) Reference link

2) Write appropriate text for titles and buttons. (2 points)

3) Change all table colors, margins. Change all text colors. (2 points)

4) Change identifiers and variable names to descriptive names. Substitute “drink” and “shots” “espresso” etc. with appropriate names. (3 points for variable names, 1 point for the rest)

5) Change price value. (1 point)

6) Computations must work. (2 points)

7) Transfer the page to the MyWeb server and submit a working web link to the page. (3 points)

In the end, if you click any item (Ice cream/Frozen Yogurt/Cookies) and click 'Total' button, it should give you a result like below:

Lab 6: Advanced Excel Features

There are a lot of details which you'll need to look, in this assignment. I will be providing hints for the most important ones. There are 4 parts in this assignment. (You can revise the basics of spreadsheet here - Spreadsheet basics) 

#1. Download Lab-6-start.txt from ICON. This will be your starting point.
#2. Convert your data as shown below into your excel file.

#3. PART - 1

• Format Cells: Follow this link to learn about formatting cells. Format Cells.
• Merge Cells:

           If we want to merge the cells L3,M3,N3 and rows L4,M4,N4, select the cells - click Home on the ribbon and click "Merge & Center".

• Now, you need to modify the excel sheet according to the following rules: (Assuming the data is positioned beginning in Cell A1, we need to move down to make room for some title headings.)

1. Add three rows at the top of worksheet. Merge and center a title "Estimated Total Quarterly Sales, Retail and E-Commerce" that spans columns A through I of row 1. Change the font to be larger and bold.
2. Merge cells C3 and D3 and enter the title Retail Sales.
3. Merge E3 and E4, with the title E-Commerce % Total Sales. 
4. Merge F3 and G3. Enter the title Quarter to Quarter % Change.
5. Place Total Sales in F4 and E-Commerce in G4.
6. Merge H3 and I3. Enter the title Year to Year % Change.
7. Place Total Sales in H4 and E-Commerce in I4.
8. Modify all of the column widths as necessary so these headings fit. 

 In the end, you should have something like this

#4. PART - 2 (Formula)

• Now, we calculate values of the columns by using excel formula. For all the formula, once you have finished it for one cell, you can use the formula for the entire column by dragging the 'small plus sign' (that appears when you hover over small box on the right bottom of the cell) to the cell, until which you want to calculate. 

1. E-Commerce % Total Sales = Total E-Commerce Sales/Total Sales. The corresponding excel formula for row 5 will be =E5/D5. Now, drag it all the way down to fill the entire column.
2. Calculate quarter to quarter changes in cells F5 and G5. The formula is the difference between the current quarter (i.e the corresponding retail sales of the current cell you are in) and previous quarter’s sales divided by previous quarter’s sales.
3. Starting with 2013 1st quarter, enter the formulas to calculate year to year changes for both total sales and e-commerce in cells H5 and I5. This formula is similar to the formulas for part 2 above, except that to get a yearly difference for the same quarter. Check for Q1 in 2013 and Q1 in 2012.

#5. PART - 3 (Formatting) 

• It's time to format the values we have and make it more presentable.

1. Format the values in columns C and D as money, with a dollar sign, commas and no decimal places.
2. Format the remaining values as percentages with 2 decimal places.
3. Add appropriate borders and colors to separate the columns whose values you calculated.
4. Add conditional formatting to display all negative percentage values in columns F and G s in red. Drag the formatting to the entire column using the 'small plus sign' as mentioned above. You can use conditional formatting as shown below: 
5. Conditional formatting for column H and I: when a current value decreases from the corresponding value in the previous quarter, you display those values in bold. Place the conditional format in cells H5 and I5, and then copy it down. Hint: make a new rule. The formula for H5 is = H5 < H6.

#5. PART - 4 (Charts)

1. Create a new worksheet named Graphs on which to draw the chart. Use the string function Concatenate (Use this link to learn about this function concatenate function) to create label values in cell C5 (in a new column). Then copy the formula down the rest of the column using the ‘small plus sign’. After this, your excel should look like this: 
   
2. Create a bar graph based on the new column (C5) and Quarterly Percentage Changes for both Total sales and E-Commerce Sales. Format the value axis numbers as percentages and show the corresponding quarter beneath each pair of sales values for that quarter.

          

#6. Almost done!!

1. Rename the tab of each worksheet Like: ‘Data’ for the sales details and ‘Chart’ for the bar graph. The footer on the left, the date in the middle, and the page number on the right hand side. Hint: Insert, Custom Footer. 
2. Format each page so that when printed, each page shows Prepared by: Your Name in
3. Set the page to Landscape mode. 
4. Use Wrap Text on the Home menu to make all text in a column heading visible.
5. Experiment with Print Preview to determine which pages are better formatted as portrait or landscape when printed. Fit each worksheet into one page!

#7. When you are done, turn in your excel sheet to the dropbox.
#8. Grading (Total-20 points)

      Based on Formula

• Concatenation: 1 point
• % Total: 2 points
• Quarter to Quarter % change: 2.5 points
• Year to year % change: 2.5 points

      Based on Formatting

• Negative values - red, Descending values - bold: 2.5 points each (5 points)
• Rest of formatting (colors, cells): 2 points

      Based on charts and others

• Chart: 3 points
• Fit into one page, landscape, footer: 2 points
• Extra rules (conditional formatting): -1 point