Access

profileBigjayq8

 

Business Scenario

 

Your muesli company has been using Excel to track customer orders.  They are having difficulty analyzing the data and producing meaningful reports.  They have asked you to implement an Access database so they can add, modify and delete data as well as generate queries and reports based on the information contained in the database.

 

1.              Create Database Relationships (Part of this assignment has been done for you, use the file available on BlackBoard under Assignments.)

 

Your first task is to download a Microsoft Access database and create the relationships between tables in the database in Microsoft Access that has been populated with data from the Excelspreadsheet which includes the following tables:  Distribution Channel, Customer, Muesli, and Sale.  (5 points)

·      The data for the tables are stored in an Access database called AccessHWSpring2016E.accdb and the name of the database should be changed to E_Your_Name_Access.accdb. 

·      Build the relationships for the four tables per the model shown in Figure 1 below.

Figure 1: Model of the Relationships

 

 

2.              Create Forms to Handle Data Entry

Part 1.  Create a data input form for customers that will update the customer table so new information can easily be added to the database. Use the Form Wizard and choose the Columnar format.  Your form should look similar to the one shown in Figure 2.  (5 points)Use the form to add the following customer to the database:

CustomerID:  Use your student number for the identification number of the customer

Area:  NO

Distribution Channel: 12. (5 points)

 

 

Figure 2

 

Part 2.  Create a form to display each muesli and related sales data on a subform.  The form should look like Figure 3.  (5 points) Then use the form to add a new sale for the 1kg Original Muesli.  In the subform add the following data for the sale:

SaleID:  211

Sales Organization:  Use the first two letters of your last name

Customer ID:  Use your student number

Price:  $5.47

Quantity:  9,248.  (10 points)

 

Figure 3

 

 

3.              Create Queries to Analyze Information

(a) Create a select query called Q1 Customer List which displays each customer’s ID, area, and distribution channel.   Sort the list in numerical order by Customer ID (Hint:  You only need to use one table.  Change setting in “Sort” row of query design grid).  (5 points)

(b) Create a select query called Q2 List of Grocery Stores that displays their Customer ID and area.  Sort this list in alphabetical order by Area.  (Hint:  Only one table needed.) (5 points)

(c) Create a parameter query called Q3 List of Sales by Customer ID which will display Customer ID, Sale ID, Material Description, Quantity, and Price. The query should ask the user to:  Please enter the Customer ID: which prompts the user to enter a Customer ID number.  (Hint: Use only one table and use the following code (exactly as written) in the “Criteria” row of the query design grid:  [Please enter the Customer ID:].) (10 points)

(d) Create a parameter query called Q4 Sales Price by Muesli that asks a user to provide the Muesli ID and then displays 1) the muesli description, 2) the minimum price, 3) the average price, and 4) the maximum price of a specific muesli sold.  (Include both Muesli and Sale tables in the query, and follow the instructions for parameter query 3(c). above but alter it for the new information.  (Hint: use the summation symbol to display the “Total:” row in the query grid).  (10 points)

(e) Create a query called Q5 Sales Information for Raisin Muesli that displays data from all tables in the following order: SaleID, Sold-to-Party, Area, Channel Description, Sales Organization, Material Description, Price and Qty.  Your data should be sorted so that the largest sales quantity is shown first. Display only data for Raisin Muesli with sales quantities exceeding 4,000.  The output should show the largest sales quantity first. (Hint: use the “criteria” row and the LIKE operator (for example:  like “*Raisin*”) in the Muesli description field, and the appropriate equality/inequality operator to generate the correct data.)  (10 points)

(f)  Create a crosstab query called Q6 Sales Quantity by Distribution Channel, which produces a table such as the one shown below in Figure 4.  The format should look like the query below but your numbers will be different. (Hint: use the crosstab function in the Query Wizard and Q5 above as your input to create this query.  You need to copy Q5 and delete the restrictions so that all the sales data shows up for all of the cereals.  Name your new query Q6.  Base the crosstab query on Q6.). (10 points)  Your totals will be different from those below.

Figure 4

4.              Create Reports for Management 

Create a report based on the Muesli Table. (5 points)The report should look like the one below in Figure 5.

 

 

Figure 5

Create a query to base a new report on called Report Query which will display Customer ID, Sale ID, Material Description, Price, and Quantity.  Use the expression builder to create a column in the query that calculates the Sale Total (multiply Price * Quantity).  (5 points)

Create a report based on the Report Query you just createdas shown in Figures 6a and 6b below. (10 points)

·      Change the title to: Sales by Customer.  Your data will be different than below, but your format should match that of the report below. 

·      Add the label: Prepared by Your Name. 

·      Use the format given

o   Group by Customer ID

o   Sales ID should be sorted in ascending order

o   For each customer, sum the Quantity and Sale Total

o   Provide a grand total of Quantity and Sale Total.

 

 

Figure 6a). Sales Report – Top of first page showing sorting by Customer ID, etc. Subtotal is displayed for each customer.

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 6b).  Sales Report – Part of last page showing Grand Total as well as subtotals for

Quantity and Sale Total.

  • 8 years ago
  • 20
Answer(1)

Purchase the answer to view it

blurred-text
NOT RATED
  • attachment
    accesshwspring2016_e-2_1.accdb