Microsoft Access Excercise 2

SELECT [Customers].[ID], [Customers].[Last Name], [Customers].[First Name] FROM Customers;

SELECT Customers.ID, Customers.[Last Name], Customers.[First Name], Customers.[Phone Number], * FROM Customers ORDER BY Customers.[Last Name], Customers.[First Name], Customers.[Phone Number];

PARAMETERS __ID Value; SELECT DISTINCTROW * FROM [Order Items Query] AS Orders WHERE ([__ID] = [Order ID]);

SELECT DISTINCTROW * FROM Customers;

SELECT DISTINCTROW * FROM [Orders Table];

SELECT [Categories].[Product Types], [Products Table].[Product Name] AS [Products Table_Product Name], [Products Table].[Description], [Sales Unit].[Product Name] AS [Sales Unit_Product Name], [Menu Items].[Price], [Categories].[ID] AS Categories_ID, [Products Table].[ID] AS [Products Table_ID] FROM [Sales Unit] INNER JOIN ((Categories INNER JOIN [Products Table] ON [Categories].[ID] =[Products Table].[Category ID]) INNER JOIN [Menu Items] ON [Products Table].[ID] =[Menu Items].[Product ID]) ON [Sales Unit].[ID] =[Menu Items].[Sales Unit ID];

SELECT [Orders Query].* FROM [Orders Query] WHERE (([Pickup Date] Not In (#1/4/2011#,#1/18/2011#,#12/16/2011#,#12/17/2011#))) ORDER BY [Orders Query].[Last Name], [Orders Query].[Zip Code] DESC;

SELECT Categories.[Product Types], [Products Table].[Product Name], [Sales Unit].[Product Name], Sum([Order Items].Quantity) AS SumOfQuantity FROM Categories RIGHT JOIN ([Sales Unit] RIGHT JOIN ([Products Table] RIGHT JOIN ([Menu Items] RIGHT JOIN [Order Items] ON [Menu Items].ID = [Order Items].[Menu Item ID]) ON [Products Table].ID = [Menu Items].[Product ID]) ON [Sales Unit].ID = [Menu Items].[Sales Unit ID]) ON Categories.ID = [Products Table].[Category ID] GROUP BY Categories.[Product Types], [Products Table].[Product Name], [Sales Unit].[Product Name] HAVING (((Categories.[Product Types])="Cakes" Or (Categories.[Product Types])="Pies"));

SELECT Categories.[Product Types], [Products Table].[Product Name], [Sales Unit].[Product Name], Sum([Order Items].Quantity) AS SumOfQuantity FROM Categories RIGHT JOIN ([Sales Unit] RIGHT JOIN ([Products Table] RIGHT JOIN ([Menu Items] RIGHT JOIN [Order Items] ON [Menu Items].ID = [Order Items].[Menu Item ID]) ON [Products Table].ID = [Menu Items].[Product ID]) ON [Sales Unit].ID = [Menu Items].[Sales Unit ID]) ON Categories.ID = [Products Table].[Category ID] GROUP BY Categories.[Product Types], [Products Table].[Product Name], [Sales Unit].[Product Name] HAVING (((Categories.[Product Types])="Cookies")) ORDER BY Sum([Order Items].Quantity) DESC;

SELECT Customers.[First Name], Customers.[Last Name], Count([Orders Table].[Pickup Date]) AS [CountOfPickup Date] FROM Customers RIGHT JOIN [Orders Table] ON Customers.ID = [Orders Table].[Customer ID] GROUP BY Customers.[First Name], Customers.[Last Name];

SELECT Customers.[First Name], Customers.[Last Name], Customers.[Street Address], Customers.City, Customers.State, Customers.[Zip Code] FROM Customers WHERE (((Customers.City)="Raleigh")) OR (((Customers.[Zip Code])="27513"));

SELECT Customers.[First Name], Customers.[Last Name], Customers.[Street Address], Customers.City, Customers.State, Customers.[Zip Code], [Orders Table].ID, Customers.[Phone Number] FROM Customers RIGHT JOIN [Orders Table] ON Customers.ID = [Orders Table].[Customer ID] WHERE (((Customers.City) Not In ("Raleigh")) AND ((Customers.[Phone Number]) Like ("919*")));

SELECT [Orders Query].[Pickup Date], [Orders Query].[First Name], [Orders Query].[Last Name], [Orders Query].[Phone Number], [Orders Query].Paid FROM [Orders Query] WHERE ((([Orders Query].[Pickup Date]) Between #12/1/2010# And #12/31/2010#)) ORDER BY [Orders Query].[Pickup Date];

SELECT Customers.[First Name], Customers.[Last Name], Customers.[Street Address], Customers.[City], Customers.[State], Customers.[Zip Code], Customers.[Email], Customers.[Phone Number] FROM Customers WHERE (((Customers.[First Name]) In (SELECT [First Name] FROM [Customers] As Tmp GROUP BY [First Name],[Last Name] HAVING Count(*)>1 And [Last Name] = [Customers].[Last Name]))) ORDER BY Customers.[First Name], Customers.[Last Name];

SELECT Categories.[Product Types], [Products Table].[Product Name], [Sales Unit].[Product Name], [Order Items].Quantity FROM [Sales Unit] RIGHT JOIN (Categories RIGHT JOIN (([Products Table] RIGHT JOIN [Menu Items] ON [Products Table].[ID] = [Menu Items].[Product ID]) RIGHT JOIN [Order Items] ON [Menu Items].[ID] = [Order Items].[Menu Item ID]) ON Categories.ID = [Products Table].[Category ID]) ON [Sales Unit].ID = [Menu Items].[Sales Unit ID];

SELECT [Menu Items].ID, (Categories.[Product Types] & " - " & [Products Table].[Product Name] & " - $" & [Menu Items].Price & "/" & [Sales Unit].[Product Name]) AS Item FROM [Sales Unit] INNER JOIN ((Categories INNER JOIN [Products Table] ON Categories.ID=[Products Table].[Category ID]) INNER JOIN [Menu Items] ON [Products Table].ID=[Menu Items].[Product ID]) ON [Sales Unit].[ID] = [Menu Items].[Sales Unit ID];

SELECT [Order Items].[Order ID], Categories.[Product Types] AS Category, [Products Table].[Product Name] AS Product, [Order Items].Quantity, [Sales Unit].[Product Name] AS ["Unit"], [Menu Items].Price, ([Order Items].[Quantity]*[Menu Items].[Price]) AS Subtotal FROM ((Categories INNER JOIN [Products Table] ON Categories.ID=[Products Table].[Category ID]) INNER JOIN ([Sales Unit] INNER JOIN [Menu Items] ON [Sales Unit].[ID] = [Menu Items].[Sales Unit ID]) ON [Products Table].ID=[Menu Items].[Product ID]) INNER JOIN [Order Items] ON [Menu Items].[ID] = [Order Items].[Menu Item ID];

SELECT Customers.[Last Name], Customers.[First Name], Customers.[Phone Number], [Orders Table].Paid, [Orders Table].[Pickup Date], Customers.[Zip Code] FROM Customers RIGHT JOIN [Orders Table] ON Customers.ID = [Orders Table].[Customer ID];

SELECT Customers.[Last Name], Customers.[First Name], Customers.[Phone Number], [Orders Table].Paid, [Orders Table].[Pickup Date], Customers.[Zip Code] FROM Customers RIGHT JOIN [Orders Table] ON Customers.ID = [Orders Table].[Customer ID] WHERE ((([Orders Table].[Pickup Date]) Between [Enter the start date:] And [Enter the ending date:]));

SELECT [Products Table].ID, Categories.[Product Types] & " - " & [Products Table].[Product Name] & " - " & [Products Table].Description AS Product FROM [Products Table] LEFT JOIN Categories ON Categories.ID=[Products Table].[Category ID];

SELECT DISTINCTROW * FROM [Orders Table];