VC6

VC4

Overview

For VC4, the overall goal is to utilize access to build a transaction processing system for a snowboard company. You have three different categories, products, customers, and orders. The overall goal for VC4 is to get familiar with the Access software and get used to entering data into tables, create relationships between tables, and create online forms.

Steps

1. Open OMIS 351 schedule and click “Virtual Case 4” under the section “Unit 6: Business Intelligence and Data Analytics”

2. Once document is open, click on “you can get it here” highlighted in red on the bottom of the page to get the data necessary for VC4

3.     Copy and paste these data tables into three different excel sheets named product, customer, and order as shown in the images down below

4. Open Microsoft Access via Microsoft Access app or NIU anywhere apps

5. Click “blank desktop database” and define your file by clicking on the file icon and finding the same directory and naming it VC4, click ok. This will create a blank table

6. Click the red x towards the top right to remove the blank table

7. Import excel sheets by clicking “external data” at the top > click “excel” > click “browse”

a.     You will have to import all three filed individually

b.     After clicking open:

                                               i. Click “first row contains column headings”

                                             ii. Click “next”

                                           iii. Click “next” again (DON’T DO ANYTHING ON THAT PAGE)

                                            iv. Click “Choose my own primary key”

                                             v. Change title to “customer”, “products”, or “orders” depending on which excel sheet your exporting

                                            vi. Do these steps for all three excel sheets

8. Click home > view > design view

a.     You will need to compare this data to original data and see if data is accurate

b.     From original data sheet, individually copy and paste description into orders as shown in the picture down below

9. Create relationships

a.     Click database tools > “relationships”

b.     Add all three tables

c.     Create relationships each table (Ex: cust id(customers) and customer id (orders) as seen in the photos down below

                                                        i. Make sure to click “enforce referential integrity” each time

10.  Finish Forms

a.     Click one of the tables

b.     Click “create” on the top row of options

c.     Click “form”

d.     Click the x

e.     Click yes

f.      Click ok

g.     Do that for all three tables

VC5

Overview

         VC5 is essentially an extension of VC4, in which after we have created the tables, relationships, and forms, we can ask questions about our business intelligence. With VC5, we are prompted five questions about our data and instructed to create and additional three that shows something about our data. What’s listed below is a list of questions we are required to answer and I will explain how to answer these questions in order.

1. In alphabetical order, list the name and address for all customers in Minnesota (MN)

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers table

d.     From customer table, click: “cust name”, “maddr”, “”city”, “stateprov”, and “zippr”

e.     Click the red exclamation point that says “run”

f.      Under the customers tab, type “MN”, then change sort under “custlname” to ascending and click “run” as seen as the picture down below

g.     Save by clicking the “x” and change query name to “Customers in Minnesota”

2.   List the order date and name of all customers who purchased a Rider snowboard

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers, orders, and products table

d.     Click “custlname” and “custfname” from the customers table, click “ordate” from the orders table, and “prod” from the products table

e.     Type “Rider” under the products row and unlick the box, change sort under “custlname” to ascending, then click “run”

f.      Click the “x” and save name as “Rider Order Dates”

3. Provide the total sales amount for all snowboards sold over the Internet

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers, orders, and products table

d.     Click “saleamnt” and “ordertype” from the orders table

e.     Type “internet” under the ordertpe column

f.      Click “totals” on the top right of the screen

g.     Under saleamnt, change total to “sum”

h.     Click “run”

i.      Click the “x” and save name as “Sales Over Internet”

4.   Provide the total sales amount for all snowboards sold over the phone

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers, orders, and products table

d.     Click “saleamnt” and “ordertype” from the orders table

e.     Type “phone” under the ordertpe column

f.      Click “totals” on the top right of the screen

g.     Under saleamnt, change total to “sum”

h.     Click “run”

i.      Click the “x” and save name as “Sales Over Phone”

5. Provide the total sales amount for all snowboards sold through sales representatives.

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers, orders, and products table

d.     Click “saleamnt” and “ordertype” from the orders table

e.     Type “sales rep” under the ordertpe column

f.      Click “totals” on the top right of the screen

g.     Under saleamnt, change total to “sum”

h.     Click “run”

i.      Click the “x” and save name as “Sales Over Sales Rep”

6. How many customers purchased a Rider snowboard over the Internet?

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers, orders, and products table

d.     Click “custid from customers table, click “ordtype” from the orders table, and “prod” from the products table

e.     In the criteria row for prod, type “Rider”

f.      In the criteria row for ordtype, type “Internet”

g.     Click “totals” on the top right of the screen

h.     Under custid, change total to “count”

i.      Click “run”

j.      Click the “x” and save name as “Count of rider over internet”

7. List the customers name and card type for all customers who purchased a snowboard using a Misery Card or Vista credit/debit card

a.     Up on the top row click “create”

b.     Click “Query Design”

c.     Add the customers, orders, and products table

d.     Click “custlname” and “custfname” from customers table and click “ctype” from the orders table

e.     In the criteria row for ctpye, type “Master Card” and “Vista” right under as shown in the picture below

f.      Under custlname, change show to ascending

g.     In the empty field next to the right of ctpye, rightclick and click “properties”

h.     Click “show properties”

i.      Go to unique values and change “no” to “yes”

a.     Click “run”

b.     Click the “x” and save name as “Count of rider over internet”

j.      Click Run

k.     Click the “x” and save name as “Misery or Visa Purchases”

8. Create reports

a.     Click on the query you wish to make a report on

b.     Click “Report”

c.     Click the “x”

d.     Save the default name

e.     Do that for each report and take screenshots

VC6

Overview

         The overall concept of VC6 is an overview of what is learned in VC4 and VC5 in which we get familiar with the Access software and get used to entering data into tables, create relationships between tables, and create online forms and use these to answer different questions and make reports.

Steps

1. Download the three excel files names “customers”, “products”, and “orders”

2. Open access, and find the database your working on and name it VC6, then click “create”

3. Click the “x” on the right side to remove empty table

4. Click “external data”

5. Click “excel” and add the three excel sheets previously saved

a.     Click “choose my own private key” then click “CustID, OrderID, and ProdID” then click next and name it what you’re working on “customers” or “products” or “orders”

6. Relate these tables

a.     Click “database tools”

b.     Click “relationships”

c.     Click on customers, orders, and products

d.     Match relationships (custid I customers to custid in orders, etc.)

e.     Click “enforce refrential integrity” and create

f.      Save the relationships

7. Create Queries

a.     Click the “create” tab

b.     Click “query design”

c.     Add all three tables

d.     Add “FirstName”, “LastName”, “ProdName”, and “Amnt”

e.     Under Criteria for “Firstname” and “Lastname” type your name “Mikhail” “Alviar”

f.      Click “run”

g.     Click “x” and save as “My Purchases”

h.     Click the “create” tab

i.      Click “query design”

j.      Add all three tables

k.     Add “FirstName”, “LastName”, and “Amnt”

l.      Under Criteria for “Firstname” and “Lastname” type your name “Mikhail” “Alviar”

m.   Click “totals”

n.     Change Total row for “Amnt” to be sum

o.     Click “run”

p.     Click “x” and save as “My Total”

q.     Click the “create” tab

r.      Click “query design”

s.     Add all three tables

t.      Add “FirstName”, “LastName”, and “Amnt”

u.     Under Criteria for “Firstname” and “Lastname” type your name “Mikhail” “Alviar”

v.     Click “totals”

w.   Change Total row for “Amnt” to be Sum

x.     For criteria under Amt, type “ > your total from the previous query”

y.     Change the total row under amt to ascending

z.     Click run

aa.  Click “x” and save as “More than me”

8. Create Reports

a.     Click “more than me” query

b.     Click “create” up top

c.     Click “report”

d.     Right click Sum of Amt

e.     Click “sort smallest to largest”

f.      Click the “x”

g.     Save as same name

h.     Do the rest for all the queries

i.      Perform a screenshot of all the reports

9. Post reports onto google site

a.     Log into student google site

b.     Create a new page named VC6

c.     Create three sub pages named after all three reports/queries

d.     Upload links to eah report for each sub page

10.  Email TA for completion