Lab 1

PARAMETERS __cboCurrentEmployee Value; SELECT DISTINCTROW * FROM [SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID], Orders.[Order Date], Orders.Taxes, [Order Price Totals].[Price Total], Customers.Company, Orders.[Status ID] FROM Customers INNER JOIN (Orders INNER JOIN [Order Price Totals] ON Orders.[Order ID]=[Order Price Totals].OrderID) ON Customers.ID=Orders.[Customer ID] WHERE ((Not (Orders.[Status ID])=3)) ORDER BY Orders.[Order Date] DESC]. AS Home WHERE ([__cboCurrentEmployee] = [Employee ID]);

SELECT Inventory.* FROM Inventory WHERE (((Inventory.[Current Level])<[Reorder Level]));

SELECT DISTINCTROW Employees.[Country/Region], Employees.[Last Name], Employees.[First Name], Orders.[Shipped Date], Orders.[Order ID], [Order Subtotals].Subtotal AS [Sale Amount] FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID]=[Order Subtotals].[Order ID]) ON Employees.ID=Orders.[Employee ID] WHERE (((Year([Shipped Date]))=Year(Date())) AND ((DatePart("q",[Shipped Date]))=DatePart("q",Date())));

SELECT [ID], [Employee Name], [Job Title] FROM [Employees Extended] ORDER BY [Employee Name];

SELECT Inventory.[Product ID], Inventory.[Product Name], Inventory.[Qty Available] FROM Inventory ORDER BY Inventory.[Product Name];

SELECT Id, [Product Name] FROM Products ORDER BY [Product Name];

SELECT Orders.*, [Orders Status].[Status Name], [Customers Extended].[E-mail Address] FROM [Customers Extended] INNER JOIN ([Orders Status] INNER JOIN Orders ON [Orders Status].[Status ID]=Orders.[Status ID]) ON [Customers Extended].ID=Orders.[Customer ID];

SELECT [Purchase Orders].*, [Purchase Order Status].Status FROM [Purchase Order Status] RIGHT JOIN [Purchase Orders] ON [Purchase Order Status].[Status ID]=[Purchase Orders].[Status ID];

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Customers.* FROM Customers ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Employee Name], Employees.* FROM Employees ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

SELECT [ID], [Employee Name], [Job Title] FROM [Employees Extended] ORDER BY [Employee Name];

SELECT DISTINCTROW * FROM Strings;

SELECT Products.ID AS [Product ID], Products.[Product Name], Products.[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz([Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level], Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level], IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To Reorder] FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID=[Inventory Sold].[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID=[Inventory Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID=[Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.ID=[Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON Products.ID=[Products On Back Order].[Product ID];

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity On Hold] FROM [Inventory Transactions] WHERE ((([Inventory Transactions].[Transaction Type])=3)) GROUP BY [Inventory Transactions].[Product ID];

SELECT [Purchase Order Details].[Product ID] AS [Product ID], Sum([Purchase Order Details].Quantity) AS [Quantity On Order] FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Posted To Inventory])=False)) GROUP BY [Purchase Order Details].[Product ID];

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity Purchased] FROM [Inventory Transactions] WHERE ((([Inventory Transactions].[Transaction Type])=1)) GROUP BY [Inventory Transactions].[Product ID];

SELECT [Inventory Transactions].[Product ID], Sum([Inventory Transactions].Quantity) AS [Quantity Sold] FROM [Inventory Transactions] WHERE ((([Inventory Transactions].[Transaction Type])=2)) GROUP BY [Inventory Transactions].[Product ID];

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal Code], Orders.[Ship Country/Region], Orders.[Customer ID], Customers.Company AS [Customer Name], Customers.Address, Customers.City, Customers.[State/Province], Customers.[ZIP/Postal Code], Customers.[Country/Region], [Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date], Orders.[Shipped Date], Shippers.Company AS [Shipper Name], [Order Details].[Product ID], Products.ID AS [Product ID], [Order Details].[Unit Price], [Order Details].Quantity, [Order Details].Discount, CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice, Orders.[Shipping Fee], Products.[Product Name] FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees Extended] ON Orders.[Employee ID]=[Employees Extended].ID) ON Customers.ID=Orders.[Customer ID]) ON Shippers.ID=Orders.[Shipper ID]) LEFT JOIN ([Order Details] LEFT JOIN Products ON [Order Details].[Product ID]=Products.ID) ON Orders.[Order ID]=[Order Details].[Order ID];

SELECT [Order Details].*, CCur([Quantity]*[Unit Price]*(1-[Discount])) AS [Extended Price], [Order Details Status].[Status Name] FROM [Order Details] LEFT JOIN [Order Details Status] ON [Order Details].[Status ID]=[Order Details Status].[Status ID];

SELECT [Order Details Extended].[Order ID] AS OrderID, Sum([Order Details Extended].[Extended Price]) AS [Price Total] FROM [Order Details Extended] GROUP BY [Order Details Extended].[Order ID];

SELECT [Order Details].[Order ID], Sum(CCur([Unit Price]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal FROM [Order Details] GROUP BY [Order Details].[Order ID];

SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID], Orders.[Order Date], Orders.[Shipped Date], [Order Price Totals].[Price Total] AS [Sub Total], Orders.[Shipping Fee], Orders.Taxes, [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], Orders.[Ship Name], Orders.[Ship Address], Orders.[Paid Date], [Orders Status].[status name] AS Status FROM [Orders Status] RIGHT JOIN (Orders LEFT JOIN [Order Price Totals] ON Orders.[Order ID]=[Order Price Totals].OrderID) ON [Orders Status].[Status ID]=Orders.[Status ID] ORDER BY Orders.[Order ID] DESC;

SELECT Orders.[Order Date], Sum([Order Details].Quantity) AS SumOfQuantity, Products.Category FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.[Order ID]=[Order Details].[Order ID]) ON Products.ID=[Order Details].[Product ID] GROUP BY Orders.[Order Date], Products.Category ORDER BY Orders.[Order Date] DESC;

SELECT [Order Details].[Product ID], Orders.[Order ID], Orders.[Order Date], Orders.[Shipped Date], Orders.[Customer ID], [Order Details].Quantity, [Order Details].[Unit Price], [Order Details].Discount, "Sale" AS Transaction, [Customers Extended].Company AS [Company Name], [Order Details].[Status ID] FROM ([Customers Extended] INNER JOIN Orders ON [Customers Extended].ID=Orders.[Customer ID]) INNER JOIN [Order Details] ON Orders.[Order ID]=[Order Details].[Order ID] ORDER BY Orders.[Order Date];

SELECT [Purchase Order Details].[Product ID], [Purchase Orders].[Purchase Order ID], [Purchase Orders].[Creation Date], [Purchase Order Details].Quantity, [Purchase Order Details].[Unit Cost], "Purchase" AS Transaction, Suppliers.Company AS [Company Name], [Purchase Order Details].[Posted To Inventory] FROM Suppliers INNER JOIN ([Purchase Orders] INNER JOIN [Purchase Order Details] ON [Purchase Orders].[Purchase Order ID] = [Purchase Order Details].[Purchase Order ID]) ON Suppliers.ID = [Purchase Orders].[Supplier ID] WHERE ((([Purchase Order Details].[Posted To Inventory])=True)) ORDER BY [Purchase Orders].[Creation Date];

SELECT Orders.[Order Date], Products.[Product Name], Products.Category, [Quantity]*[Unit Price] AS Amount FROM Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ID=[Order Details].[Product ID]) ON Orders.[Order ID]=[Order Details].[Order ID] ORDER BY Orders.[Order Date], Products.[Product Name];

SELECT Orders.[Order Date], [Employees Extended].[Employee Name], Sum([Order Details].Quantity) AS SumOfQuantity, Products.[Product Name] FROM Products INNER JOIN (([Employees Extended] INNER JOIN Orders ON [Employees Extended].ID=Orders.[Employee ID]) INNER JOIN [Order Details] ON Orders.[Order ID]=[Order Details].[Order ID]) ON Products.ID=[Order Details].[Product ID] GROUP BY Orders.[Order Date], [Employees Extended].[Employee Name], Products.[Product Name] ORDER BY Orders.[Order Date] DESC, [Employees Extended].[Employee Name], Products.[Product Name];

SELECT Products.[Product Name], Orders.[Order Date], Sum([Unit Price]*[Quantity]) AS Amount FROM Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ID=[Order Details].[Product ID]) ON Orders.[Order ID]=[Order Details].[Order ID] GROUP BY Products.[Product Name], Orders.[Order Date], Products.[Product Name] ORDER BY Sum([Unit Price]*[Quantity]) DESC, Products.[Product Name];

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity] FROM [Product Orders] UNION SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] FROM [Product Purchases] ORDER BY [Order Date] DESC;

SELECT [Order Details].[Product ID] AS [Product ID], Sum([Order Details].Quantity) AS [Quantity On Back Order] FROM [Order Details] WHERE ((([Order Details].[Status ID])=4)) GROUP BY [Order Details].[Product ID];

SELECT [Purchase Order Details].*, [Quantity]*[Unit Cost] AS [Extended Price], Products.[Product Name] FROM Products INNER JOIN [Purchase Order Details] ON Products.ID=[Purchase Order Details].[Product ID];

SELECT [Purchase Details Extended].[Purchase Order ID] AS [Purchase ID], Sum([Purchase Details Extended].[Extended Price]) AS [Price Total] FROM [Purchase Details Extended] GROUP BY [Purchase Details Extended].[Purchase Order ID];

SELECT [Purchase Orders].[Creation Date], [Purchase Order Status].Status, [Purchase Orders].[Supplier ID], [Purchase Orders].[Shipping Fee], [Purchase Orders].Taxes, [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], [Purchase Price Totals].[Price Total] AS [Sub Total], [Purchase Orders].[Payment Date], [Purchase Orders].[Approved By], [Purchase Orders].[Approved Date], [Purchase Orders].[Submitted By], [Purchase Orders].[Submitted Date], [Purchase Orders].[Status ID], [Purchase Orders].[Purchase Order ID] FROM [Purchase Order Status] INNER JOIN ([Purchase Orders] INNER JOIN [Purchase Price Totals] ON [Purchase Orders].[Purchase Order ID]=[Purchase Price Totals].[Purchase ID]) ON [Purchase Order Status].[Status ID]=[Purchase Orders].[Status ID] ORDER BY [Purchase Orders].[Status ID], [Purchase Orders].[Purchase Order ID] DESC;

SELECT Orders.[Order ID], Orders.[Order Date], [Employees Extended].[Employee Name] AS Employee, [Customers Extended].Company AS [Customer Name], Products.[Product Name], [Order Details Extended].[Extended Price] AS Sales, [Customers Extended].[State/Province], [Customers Extended].[Country/Region], [Employees Extended].ID AS [Employee ID], Products.ID AS [Product ID], [Customers Extended].ID AS [Customer ID], Year([Order Date]) AS Year, Month([Order Date]) AS Month, DatePart("q",[Order Date]) AS Quarter, Format([Order Date],"mmmm") AS [Month Name], Products.Category, Products.[Product Name] AS Product, [Customers Extended].Company AS Customer, (Month([Order Date]) Mod 3) AS MonthOfQuarter FROM ((((Orders INNER JOIN [Customers Extended] ON Orders.[Customer ID]=[Customers Extended].ID) LEFT JOIN [Employees Extended] ON Orders.[Employee ID]=[Employees Extended].ID) INNER JOIN [Order Details Extended] ON Orders.[Order ID]=[Order Details Extended].[Order ID]) INNER JOIN Products ON [Order Details Extended].[Product ID]=Products.ID) INNER JOIN Invoices ON Orders.[Order ID]=Invoices.[Order ID];

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Shippers.* FROM Shippers ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Suppliers.* FROM Suppliers ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

SELECT DISTINCTROW Orders.[Order ID], Orders.[Order Date], [Order Subtotals].Subtotal AS SaleAmount, [Customers Extended].Company AS CompanyName, Orders.[Shipped Date] FROM [Customers Extended] INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID]=[Order Subtotals].[Order ID]) ON [Customers Extended].ID=Orders.[Customer ID] ORDER BY [Order Subtotals].Subtotal DESC;