Database Queries – With Microsoft Access

Database Queries – With Microsoft Access

Database Queries – With Microsoft Access

40 Points

Download the A5_SalesCo.accdb database file and review the tables, fields and relationships to

gain a better understanding of the data. For your convenience, the relationships are posted below.

Create and execute queries that will answer the following 15 questions. You may use the graphical

design view of Access to write your queries for this assignment. Save each of the queries to the

database file – using the naming convention of Q01, Q02, Q03 … Q15 – corresponding to the

question number.

Questions 1 – 10: 2 points each.

1. Working in the Accounting area of the business, you are interested in getting a list of Customers along with their outstanding balance (cust_balance). Order the data by the

customer last name. Format the balance to display with a currency format.

2. You find that listing all customers does not make it easy to see who to focus our collection efforts on, re-write Q1 to display only those customers with a balance greater than $3,000

and order the data in descending sequence by the customer’s balance.

3. You were just given your sales territory (Connecticut) and you need to get a list of all of your customers in that state with all of their vital information.

4. As a sales manager, you are interested in getting a list of all invoices for the 4th Quarter of 2017 (October 1 – December 31). Include the invoice number, date and total from the

invoice table, also display the employee name (first and last) from the Employee table and

calculate and include the commission earned using the formula of (inv_total * emp_comm).

Format the display of any currency type data to display as such.

BUAD 3050 – Assignment #5

Database Queries – With Microsoft Access

40 Points 5. As a salesperson, you want a list of all product SKU, description and prices for our

Exterior (prod_type), Water based (prod_base), Primer (prod_category) products.

6. As a salesperson, you need to get all of the pertinent information for all varnish type products before talking with one of your customers. A Varnish type product has the word

varnish somewhere in the produce description. Order the data by product description.

7. As the inventory manager, you need to see any products that need replenishment. A product needs replenishment if the prod_QOH is less than the prod_MIN column. Display

the product’s SKU, Description, QOH and MIN.

8. As an HR analyst, you have been tasked with reaching out to your most junior employees and need a list of employees hired after Dec 31st, 2015 who have a title of Clerk I or Clerk

II. You need their full names along with their emails. Order the data by last name. Do

NOT display the employee’s date of hire nor their title.

9. As an HR analyst, you need to know who the managers of the company are (by having the word “Manager” as part of the employee’s title. Create a list that displays the Employee

number, name and title.

10. As a Financial analyst who is interested in cash flow analysis, you are interested in the total of all outstanding customer balances. Create a query to get a sum of the Cust_balance

column of the customer table.

Questions 11 – 15, 3 points each.

11. As the newly hired VP of marketing, you need to get a count of customers in each of the states covered by your company. Order the data by State.

12. To find out which states provide the most sales, you need to get a total of all sales (inv_total) for each of the states. Order the data by sales in descending sequence.

13. You need to identify your 5 best salespersons for the year 2017 and how much they sold. 14. The Marketing department is trying to reduce the cost of sending it’s mailers by identifying

any potential duplicate customers. A customer may be a duplicate if the address, city and

state are duplicated in another row. Display all columns of data for any potential

duplicates.

15. To reduce the number of vendors that you use, you need to identify which vendor provides no products. Display all data for the unmatched vendors.

Create a Word document to assemble a simple to grade solution document. Use the naming

convention, Q01, Q02, Q03 … etc. Also, add the following data for each of the questions (queries):

• Identify the question number

• Copy and paste a screen shot of the design view for your query

• Copy and paste the query results – when more than 10 rows of data are returned from the query – snip only the first 10.

• Do NOT give me a full screen shot for these copy and paste images. Crop (or only snip) the relevant part of the screen.

• Separate each question’s solution by some visual delimiter (row of underscores, **, or a line)

BUAD 3050 – Assignment #5

Database Queries – With Microsoft Access

40 Points If, for example, you need to list all of the vendors from Tennessee. Your submission for this

question will look like this:

Q##

Save your Word document as lastName_A5.docx and post it and your database to Blackboard.

Project Deliverables:

1) Post the A5_SalesCo.accdb file back to Blackboard with the 15 queries named and saved as directed.

2) Assemble and submit a single Word document that follows the submission requirements illustrated above for the 15 queries.

3) Post the A5_SalesCo.accdb file back to Blackboard with the 15 queries saved as directed. 4) Organize your packet – clearly label and identify the solution document portion of

the submission – the submission and organization of your packet is worth 5 points.

It is not necessary to print any part of this assignment – submission and grading will be done

via Blackboard.

The following grading rubric (may) will be returned to you, if it is necessary to communicate

where points were lost.

BUAD 3050 – Assignment #5

Database Queries – With Microsoft Access

40 Points

Grading Rubric: ____________________________________ Section: ______

Points Earned

Category Description/Requirements

______(20) Queries

1 – 10

1. List of Customers along with their outstanding balance. Order by last name. Format the balance to currency.

2. Display only those customers with a balance greater than $3,000 and order customer’s balance (DESC)

3. List of all customers in the state of ‘CT’ 4. List of all invoices for the 4th Quarter of 2017. Include the invoice

number, date and total from the invoice table, the employee name and calculate and include the commission earned using the formula of (inv_total * emp_comm). Format any currency type data.

5. List of all product SKU, description and prices for our Exterior, Water based, Primer products.

6. List all pertinent information for all varnish type products. Order by product description.

7. List any products that need replenishment. Display the product’s SKU, Description, QOH and MIN.

8. List of employees hired after Dec 31st, 2015 who have a title of Clerk I or Clerk II. Display names and emails. Order the data by last name. Do NOT display the date of hire nor title.

9. List managers of the company with Emp#, name and title. 10. Display the total of all outstanding customer balances.

______(15) Queries

11 – 15

11. List the count of customers by state. Order the data by State. 12. List the total of all sales (inv_total) for each state. Order DESC by

sales. 13. Identify TOP 5 salespersons and how much they sold. 14. Identify Duplicate customers 15. Identify which vendor provides the least number of products. Be sure

to include any vendors that provide 0 products.

______(5) Proper Submission

• A5_SalesCo.accdb submitted to BB with the 15 queries (named) and saved as directed.

• Single Word document with screen shots inserted for each query problem: in design view and datasheet view – submitted to

Blackboard

• Word document well organized and easy to grade.

______(40) Total Points Earned

Attention: No late work accepted.


Comments are closed.