a. Create Item Extension Calculation

b. Create Query to calculate the total dollar amount of each sale. Include Invoice Number, Date, and Customer Number, Customer Name, and the Total Amount.

c. Calculate total sales only the total sales should be shown. Go back to query 2 b and verify that the totals are the same.

d. Create Query to calculate total sales for each inventory item. Make sure the totals agree with previous sales amounts.

e. Which customers made purchases from Jackson? Show only customer name, state and sum of amount in the report.

f. Calculate total sales per customer show customer number, name, state and total sales. A customer should appear on the list even though they had no sales, research how to get a null value to be displayed. (When totaling sales by customer, I used an if statement.) Be sure to save your final result as a make-table query so you can use in your next query (g).

g. How much credit does each customer still have available? (There have been no cash receipts). Your result should include all customers.

h. Prepare a query that will include all customers who have exceeded their credit limit. Show customer, state in which they live, and current balance.

i. All customers are given a ranking when they apply for credit. Only customers with a ranking of A should be given credit limits greater than $2,500. Create a list of customers who have been given an exception to the normal policy.

j. Who has the largest credit limit? (Final answer should show one customer)

k. Create a query to show a total sales amount per salesperson.

l. How many customers live in MO? Query should only provide you with a count.

m. Create a query which shows the qty sold of each inventory item. Include the item # and the description of each item in your answer. All items should be included and 0 should be shown if the item has not been sold.

n. Calculate the remaining balance for each inventory item. Query should include item #, description, beginning balance, and remaining balance.

o. Calculate a query that will show the ending dollar inventory balance for each inventory item. Report should include Item # and Description for each item. Put a total the end of the query as this would be the amount that would be on the balance sheet.

All Access Query1 x Relationships X Q Search... Tables Customer: Table Customer: Table Sales Inventory B Inventory Sales Sales-Inventory Queries 2a_Item Extension Calcu... 2b_Sales Per Invoice 2c Total Sales 2d_Sales Per Inventory 1 Customer Number Name City State Credit Limit Ranking Invoice Number Date Salesperson Customer Number Item Number Description Unit Cost Unit Price Beginning Balance Sales-Inventory Invoice Number 1 Item Number Quantity x - Gilkey AZ Query1 x Relationships Customer Nu - Name # 1000 Smith 1001 Jones 1002 Jeffries + 1003 + 1004 Lankford +1005 Zeile + 1006 Pagnozzi + 1007 Arocha # 1008 Dider + 1009 Maddock + 1010 Dickerson + 1011 Lawless 2a_Item Extension Calculation City State Phoenix AZ St. Louis MO Atlanta GA Phoenix Phoenix AZ Chicago Salt Lake City UT Chicago IL Phoenix AZ St. Louis MO Salt Lake City UT St. Louis MO Customer: Table X - Credit Limit - Ranking $1,600.00 B $1,500.00 B $6,000.00 A $5,000.00 A $2,000.00 B $2,000.00 B $3,000.00 B $1,000.00 B $5,000.00 A $3,000.00 A $4,500.00 B $3,000.00 B IL 74 Item Number - + 1010 + 1015 + 1020 + 1025 + 1030 + 1035 + 1040 + 1045 + 1050 + 1055 + 1060 Description Blender Toaster Mixer Television Freezer Refrigerator Radio Clock Microwave Food Processor Crockpot Unit Cost - $14.00 $12.00 $23.00 $499.00 $799.00 $699.00 $45.00 $79.00 $128.00 $225.00 $25.00 Unit Price - Beginning Bal - C $29.95 200 $19.95 300 $33.95 200 $699.95 $999.95 32 $849.95 $79.95 100 $99.95 300 $198.00 $297.00 $45.00 25 * Query1 X 2 Sales X Invoice Numl - Date - Salesperson - Customer Nu 101 1/3/2018 Wilson 1000 102 1/5/2018 Mahomet 1003 103 1/5/2018 Wilson 1002 104 1/5/2018 Drezen 1000 105 1/15/2018 Martinez 1005 106 1/16/2018 Jackson 1007 107 2/1/2018 Mahomet 1002 108 2/3/2018 Drezen 1000 109 2/4/2018 Jackson 1002 110 2/5/2018 Mahomet 1005 111 2/5/2018 Wilson 1008 112 2/10/2018 Wilson 1009 113 2/12/2018 Jackson 1002 114 2/15/2018 Wilson 1010 115 2/16/2018 Drezen 1001 116 2/15/2018 Jackson 1005 117 2/15/2018 Wilson 1011 118 2/25/2018 Drezen 1011 - C 2h_Exceeded Limit X Sales-Inventory X nvoice Numbe-t Item Number - Quantity 101 1015 101 1025 101 1030 101 1035 102 1045 103 1010 103 1015 103 1025 104 1025 104 1045 105 1035 106 1045 107 1030 107 1035 107 1040 107 1045 108 1025 108 1045 109 1025 110 1010 110 1045 111 1025 112 1010 112 1025 113 1050 114 1010 114 1025 115 1050 115 1055 116 1060 117 1020 117 1045 117 1060 118 1040 118 1050 All Access Query1 x Relationships X Q Search... Tables Customer: Table Customer: Table Sales Inventory B Inventory Sales Sales-Inventory Queries 2a_Item Extension Calcu... 2b_Sales Per Invoice 2c Total Sales 2d_Sales Per Inventory 1 Customer Number Name City State Credit Limit Ranking Invoice Number Date Salesperson Customer Number Item Number Description Unit Cost Unit Price Beginning Balance Sales-Inventory Invoice Number 1 Item Number Quantity x - Gilkey AZ Query1 x Relationships Customer Nu - Name # 1000 Smith 1001 Jones 1002 Jeffries + 1003 + 1004 Lankford +1005 Zeile + 1006 Pagnozzi + 1007 Arocha # 1008 Dider + 1009 Maddock + 1010 Dickerson + 1011 Lawless 2a_Item Extension Calculation City State Phoenix AZ St. Louis MO Atlanta GA Phoenix Phoenix AZ Chicago Salt Lake City UT Chicago IL Phoenix AZ St. Louis MO Salt Lake City UT St. Louis MO Customer: Table X - Credit Limit - Ranking $1,600.00 B $1,500.00 B $6,000.00 A $5,000.00 A $2,000.00 B $2,000.00 B $3,000.00 B $1,000.00 B $5,000.00 A $3,000.00 A $4,500.00 B $3,000.00 B IL 74 Item Number - + 1010 + 1015 + 1020 + 1025 + 1030 + 1035 + 1040 + 1045 + 1050 + 1055 + 1060 Description Blender Toaster Mixer Television Freezer Refrigerator Radio Clock Microwave Food Processor Crockpot Unit Cost - $14.00 $12.00 $23.00 $499.00 $799.00 $699.00 $45.00 $79.00 $128.00 $225.00 $25.00 Unit Price - Beginning Bal - C $29.95 200 $19.95 300 $33.95 200 $699.95 $999.95 32 $849.95 $79.95 100 $99.95 300 $198.00 $297.00 $45.00 25 * Query1 X 2 Sales X Invoice Numl - Date - Salesperson - Customer Nu 101 1/3/2018 Wilson 1000 102 1/5/2018 Mahomet 1003 103 1/5/2018 Wilson 1002 104 1/5/2018 Drezen 1000 105 1/15/2018 Martinez 1005 106 1/16/2018 Jackson 1007 107 2/1/2018 Mahomet 1002 108 2/3/2018 Drezen 1000 109 2/4/2018 Jackson 1002 110 2/5/2018 Mahomet 1005 111 2/5/2018 Wilson 1008 112 2/10/2018 Wilson 1009 113 2/12/2018 Jackson 1002 114 2/15/2018 Wilson 1010 115 2/16/2018 Drezen 1001 116 2/15/2018 Jackson 1005 117 2/15/2018 Wilson 1011 118 2/25/2018 Drezen 1011 - C 2h_Exceeded Limit X Sales-Inventory X nvoice Numbe-t Item Number - Quantity 101 1015 101 1025 101 1030 101 1035 102 1045 103 1010 103 1015 103 1025 104 1025 104 1045 105 1035 106 1045 107 1030 107 1035 107 1040 107 1045 108 1025 108 1045 109 1025 110 1010 110 1045 111 1025 112 1010 112 1025 113 1050 114 1010 114 1025 115 1050 115 1055 116 1060 117 1020 117 1045 117 1060 118 1040 118 1050