IDS 200 GROUP PROJECT

IDS 200 GROUP PROJECT

To Download tutorial Copy and Paste below Link into your Browser

for any inquiry email us at  ( essayblue@gmail.com )

IDS 200 Group Project

E
A
S
T
C
I
S
L
A
T
N
O
R
T
S
S
E
V
E
S 120 Hanover
Square West
minst
er London 27-28 Lugley
St Newp
ort Isle of
Wight 12 Berkley
Rd, Camd
en
Town Greater
London W
1S
1
P
O3
0
5H
D
N
W
1
8 UK UK UK 35 King
George Ave 34 Crowther
Rd 300
Queensbridg
e Rd East
Ham Cryod
on Hackn
ey E1
6
3 Greater
London London Greater
London 168 Croydon
Road Broml
ey Greater
London E8
3 SE
20
7 After the f_Product form is created, use the form to add the following
product to the existing list of products that the company has.
Field
Name ProductI
D Product
Name Value Autonumbered (should be 78) Cousin Christie’s Chocolate Covered
Cherries UK SE
25 Add a Product UK UK UK Quantity
PerUnit UnitPrice UnitCost UnitsInSt
ock UnitsOn
Order Reorder
Level Discontin
ued iii. 12 pints $19.99 $9.99 0 50 20 No Add an Order Use the frmCompleteOrder form that is already provided in the database
to add the following two orders: Add a new Order Number (Order ID) 12000. Customers : Eastern Connection. Products ordered: Fløtemysost & Cousin Christie’s Chocolate
Covered Cherries(Quantity 4 of each, 10% Discount). Make up the rest of the data (Dates, Shipping info, etc.)
Add a second order, this time for the customer B’s Beverages. The order should be for the product(s) B’s Beverages purchased
most recently. Hint: Modify the provided query qry_OrdersByCustomer to meet
the new requirements. Sort the query by order date to help you determine which product(s) B’s Beverages purchased most recently.
Create a new Order Number 12001 for this purpose. 5. Reports Create Customer Contact and Customer Order reports. a) Create r_CustomerContact: 10 points The data source for this report will be a query. Modify the query
qry_CustomerContactedApril provided for you in the database as a starting
point. This report should contain: All the contacts that have taken place with prospective customers (customer
status is “P” for Prospect).
All contacts on or after April 1, 2010.
Names (first and last names) of employees who spoke to or e-mailed customer. Description of discussion. b) Create r_CustomerOrder: 8 points Create a report to list the orders for the Customer with the customer ID of
65 by modifying the query qry_OrderDetailsTotalforCustomer provided for you
in the database as a starting point. This report should calculate: The total dollar amount for each order.
The grand total for all orders by this customer.
The discount need not be accounted for.
Sort the report by Order Date in the order of most recent to the oldest. B. Integration – 45 points in total This part of final project requires you to export data from the Access database
into two Microsoft Excel files. One of the Excel file will be used as the data source for
performing a sales analysis, and another one will be used as the data source for
performing a special product analysis.
1. Export Data from Access to Excel and Do Excel Sales Analysis: 36 points Export data from the tblCustomer, tblOrder, tblOrderLine and t_Product tables in
the Access database into Excel to generate a worksheet that will be used to answer
the questions. Furthermore, create a worksheet called Answers to type your
answers to the questions Q1, Q2, Q3, and Q4. Save your file with the naming
convention GroupName_SalesAnalysis.xlsx for your file (for example:
MondayA1_SalesAnalysis.xlsx). The first two questions are: Q1. Customers in what state/region had the highest dollar value of sales for
the company? Identify the customer who had the highest dollar value of
sales in that state and state the customer name, their sales value and income.
Q2. What state/region created the most income for the company? In order to answer questions Q1 and Q2, employ the following steps:
Use the query qryOrderTotalForCustomers for exporting the data into Excel;
Once the spreadsheet has been generated, create columns to calculate the sales
dollar amount and income for each order line (ignore the freight amount in calculating the income. You don’t need to consider the discount value while calculating
these fields);
Sort the spreadsheet by the appropriate field to order the data in a way that will
facilitate answering the questions;
Create subtotals by state/region; Create a second spreadsheet tab with just the summary information by state/region;
Create two bar charts or pie charts in the second tab – one for the sales dollar information and one for the income information.
The last two questions are: Q3. Which product had the highest dollar value of sales in each state/region within the USA? State the product names, the related state/region names,
and the related dollar value of sales. Q4. Which product had the highest dollar value of sales across the USA?
State the product name and the related dollar value of sales. In order to answer questions Q3 and Q4 above, employ the following steps:
Use the query qryTotalForProduct for exporting the data into Excel;
Once you generate the worksheet, sort it using the appropriate fields needed to
answer the question Q3;
Create a list of the highest selling products in each state/region on a separate
sheet. The list should identify the state/region, the highest selling product and
the dollar value of the sales of the product in each state/region;
After you have answered the question Q3, you will need to re-sort the spreadsheet using the appropriate field(s) in order to answer question Q4;
Once you have re-sorted the spreadsheet, you will need to calculate a subtotal to
determine the highest selling product in the USA. Identify this product, and add a
line to the list in the second sheet identifying this product and the sales dollar volume in the USA. 2. Export Data from Access to Excel and Do Excel Special Product Analysis: 9
points The company wants to identify the customers who had purchased Tarte au sucre
or Gnocchi di nonna Alice or Alice Mutton or Camembert Pierrot after 1/15/2009. For
this purpose you need to do the followings: Modify the query qry_ProductMailer provided for you in the database as a starting point to identify the customers.
Export this information into an Excel worksheet and save the exported Excel file
as GroupName_SpecialProduct.xlsx (for example: MondayA1_SpecialProduct.xlsx).
In cell A50 of the sheet, enter a Countif function to determine how many orders
of the above products were made in the USA. In cell A51 enter another Countif
function to determine how many orders of the above product were made in the
UK. (The Cell A50 will be empty before you perform this step, ie if you have mod- ified your query in Access and exported the results of the query, you will have
less than 50 records in the excel sheet.)
In cell K2, enter an If function to determine whether the customers who ordered
the above products were domestic or international customers. Fill the formula
down to all the rows which have the customer data. (Note: the word “Domestic”
or “International” must be printed in the associated cells of column K.)
Use conditional formatting to highlight the duplicates in customer names (in
this case it is the company names).
Save the Excel workbook. C. Additional Integration – 20 points in total The work under this part of the final project is optional and is worth a possible 25
points. The following is the work that needs to be completed in order to earn the 25
extra credits: Export data from the tblOrder, tblOrderLine, t_Product, and tblEmployee tables in
the Access database into Excel to generate a worksheet that ranks the sales persons by the dollar value of sales and income from sales. (Hint: this requires you
to develop a brand new query. Include the query in your Access database and
name the query q_ExtraCredit.)
Show the rank information graphically (using any kind of appropriate charts) as
well as in report format (table form).
Submit the workbook as GroupName_ExtraCredit.xlsx (for example:
MondayA1_ExtraCredit.xlsx). Note: Since this is an extra credit work, Instructors and TAs will not
provide any assistance. D. Peer Evaluation – 10 points in total Upon completion of the project each group member will complete an on-line Peer
Evaluation form for the group rating the level of participation of each group member.
Maximum possible points for peer evaluation are 15. The link to submit Peer Evaluation
will be available in Blackboard . IV. Deliverables and Due Date
A. Deliverables The followings are the deliverables that need to be submitted: 1. Access – The database file contains all of the tables, queries, forms, and reports. It
should be named using the naming convention GroupName_FinalProject.accdb (for
example MondayA1_FinalProject.accdb) and contain the following new or modified objects:
A new table which is t_Product; A new relationship which is between the t_Product and tblOrderLine tables; Two new queries which are:
o q_EmployeeCustomerContact, and
o q_ProductsByOrder; Two new forms which are:
o f_Customer, and
o f_Product; Four modified queries which are:
o qry_CustomerContactedApril,
o qry_OrderDetailsTotalforCustomer,
o qry_OrdersByCustomer; A modified form which is frmCompleteOrder; Two new reports which are:
o r_CustomerContact, and
o r_CustomerOrder; If you choose to do Additional Integration to earn extra credits then a new query
q_ExtraCredit should also be in your Access database. 2. Excel – Sales Analysis workbook which should be named using naming convention
GroupName_SalesAnalysis.xlsx (for example MondayA1_SalesAnalysis.xlsx) and
Special Product Analysis workbook should be named using naming convention
GroupName_SpecialProduct.xlsx (for example MondayA1_SpecialProduct.xlsx).
3. Extra Credit – Submit the workbook which should be named using naming convention GroupName_ExtraCredit.xlsx (for example MondayA1_ExtraCredit.xlsx)
4. Peer Evaluation – Find the link to submit the peer evaluation in Blackboard under
Assignments. B. Due Date Submit deliverables 1 to 3 by Sunday, May 1st, 2016 at 5:00 PM.
Submission links will be made available in MyITLab under Research Projects ->
Submit Final Project Deliverables. Only one member of the group needs to submit
the deliverable files. Submit Peer Evaluation individually Wednesday, May 4 th, 2016 at 5:00 PM. Any questions related to the Final Project should be sent to your TAs.


Comments

Popular posts from this blog

HRM595 WEEK 2 FIELD ANALYSIS ASSIGNMENT

NR 506 FULL COURSE

NR 512 FULL COURSE