Question

Part (a) CREATE TWO TABLES IN MICROSOFT ACCESS: (1) A Customer Table which includes the following fields: Customer Name, Customer Address, and Credit Limit. (Note: All customers have a $40,000 credit limit). (2) A Sales Invoice table which includes the following fields: Customer Name, Salesperson, Date of Sale, Invoice Number, Amount of Sale. Part (b): Run the following queries: Query 1: List all sales between 10/20/2014 and 11/20/2014 that were greater than $2,500. Include in your query the customer name, date of sale, invoice number, and amount of sale. List the sales in alphabetical order by customer name. Query 2: List total sales by each salesperson for October and November 2014 in descending order. Include in your query the salesperson name and amount of total sales. Query 3: List the total sales by customer in descending order. Include in your query the customer name, customer address, and amount of total sales per customer. Query 4: List the remaining credit available for each customer. Include in your query the customer name, customer address, credit limit, amount of total sales per customer, and remaining credit available for each customer in descending order of remaining credit available.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

1.Table Name :Customer

2.Table Name :SalesInvoice

Query 1:

SELECT CustomerName,DateOfSale,invoiceNumber,amountOfSale
FROM SalesInvoice
where DateofSale between ('10/20/2014') and ('11/20/2014') and
amountOfSale >2500
order by CustomerName;

Explanation :

• Given SQL query above will find the CustomerName, DateOfSale, invoiceNumber, amountOfSale from SalesInvoice

*********************************

Query 2 :

SELECT Salesperson,sum(AmountOfSale)
FROM SalesInvoice
where DateofSale between ('10/01/2014') and ('11/30/2014')
group by Salesperson
order by sum(AmountOfSale) desc;

Explanation :

• This sql query will group all sales amount in oct and nov by sales person and will return the result.

*********************************

Query 3:

SELECT SalesInvoice.CustomerName, CustomerAddress,sum(AmountOfSale) as 'amount of total sales'
FROM Customer inner join SalesInvoice
on
Customer.CustomerName=SalesInvoice.CustomerName
group by SalesInvoice.CustomerName,CustomerAddress
order by sum(AmountOfSale) desc;

Explanation :

• SQL Query above will join two tables customer and Salesinvoice based on customerName
• and will group the records based on customerName

*********************************

Query 4:

SELECT SalesInvoice.CustomerName, CustomerAddress,creditLimit,sum(AmountOfSale) as 'amount of total sales',creditLimit-sum(AmountOfSale) as ' credit available'
FROM Customer inner join SalesInvoice
on
Customer.CustomerName=SalesInvoice.CustomerName
group by SalesInvoice.CustomerName,CustomerAddress,creditLimit
order by creditLimit-sum(AmountOfSale) desc;

Explanation :

• SQL query above will join two tables customer and salesinvoice and will return the details.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.