1 all powder board and ski microsoft access workbook chapter 4: queries jerry post copyright © 2007
Post on 30-Dec-2015
214 Views
Preview:
TRANSCRIPT
3
Action
ActionChoose File/Get External Data/ImportBrowse to the appropriate csv fileSet comma delimitedCheck the box to include column namesIf your table matches the file, check the box to
read into an existing table
4
File/Get External Data/Import
The sample files include column names
Comma delimited is common
Make sure columns are correct
5
Starting a Query
Select tables
Show table list
Display fields/columns
Conditions
Switch to SQL or view results
6
Action
ActionCreate a new query in Design viewAdd the ItemModel tableSelect columns for Category, ListPrice, WeightMax,
Color, and GraphicsEnter conditions for Board, ListPrice, and WeightRun the query
7
Sample Query
Display snowboards with a list price under $300 and max weight over 150 pounds.
SELECT ItemModel.Category, ItemModel.ListPrice, ItemModel.WeightMax, ItemModel.Color, ItemModel.Graphics
FROM ItemModel
WHERE (((ItemModel.Category)="Board") AND ((ItemModel.ListPrice)<300) AND ((ItemModel.WeightMax)>150));
8
Action
ActionCreate a new query in Design viewAdd the ItemModel tableAdd columns: Category, Color, ItemMaterial,
Style, and ListPriceSet requested conditionsCheck the SQLRun the query
9
More Complex Query
Ski for jumping.Composite material.Red or Yellow main color.Yellow skis must be under $300.Red skis must be under $400
Three main conditions
12
SQL VersionsSELECT ItemModel.Category, ItemModel.Color, ItemModel.ItemMaterial, ItemModel.Style, ItemModel.ListPrice
FROM ItemModel
WHERE (((ItemModel.Category)="Ski") AND ((ItemModel.Color)="Yellow") AND ((ItemModel.ItemMaterial)="Composite") AND ((ItemModel.Style)="Jump") AND ((ItemModel.ListPrice)<300)) OR (((ItemModel.Category)="Ski") AND ((ItemModel.Color)="Red") AND ((ItemModel.ItemMaterial)="Composite") AND ((ItemModel.Style)="Jump") AND ((ItemModel.ListPrice)<400));
SELECT Category, Color, ItemMaterial, Style, ListPrice
FROM ItemModel
WHERE (Category="Ski" AND ItemMaterial="Composite" AND Style="Jump")AND ( ( Color="Yellow" AND ListPrice<300)OR (Color="Red" AND ListPrice<400) );
14
Action
ActionCreate a new query in Design viewChoose the Sale tableSelect columns: SaleID, SaleDate, CustomerID, and PaymentMethodSet conditions for Cash sales in MayChoose Query/Show Table (or button)Add the Customer tableAdd columns: LastName, FirstNameView the SQLRun the query
16
JOIN: SQL
SELECT Sale.SaleID, Sale.SaleDate, Sale.CustomerID, Customer.LastName, Customer.FirstName, Sale.PaymentMethod
FROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID
WHERE (Sale.SaleDate Between #5/1/2004# And #5/31/2004# AND Sale.PaymentMethod="Cash");
17
Building a More Complex Query
Which customers bought Atomic skis in January or February?
What do you want to see? Customer names, SaleDate
What do you know? Manufacturer name, SaleDate range, Category is Ski
What tables are involved?
How are they joined?
Customer … Sale … ItemModel, Manufacturer
SELECTLastName, FirstName, SaleDateFROM Customer, …, Sale, …, ItemModel, ManufacturerJOIN WHERE Manufacturer.Name=“Atomic” AND Sale.SaleDate BETWEEN 1/1/2004 AND 2/29/2004 AND ItemModel.Category = “Ski”
19
SQL: Many Table Joins
SELECT Customer.LastName, Customer.FirstName, ItemModel.Category, Manufacturer.Name, Sale.SaleDate
FROM Manufacturer INNER JOIN (ItemModel INNER JOIN (Inventory INNER JOIN ((Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID) ON Inventory.SKU = SaleItem.SKU) ON ItemModel.ModelID = Inventory.ModelID) ON Manufacturer.ManufacturerID = ItemModel.ManufacturerID
WHERE (((ItemModel.Category)="Ski") AND ((Manufacturer.Name)="Atomic") AND ((Sale.SaleDate) Between #1/1/2004# And #2/29/2004#));
20
Action
ActionCreate a new query in Design viewAdd the ItemModel tableSelect columns: Category, ItemMaterial, and ListPriceCreate new column as Profit: [ListPrice]-[Cost]Run the query
21
Calculations
Calculated column
SELECT Category, ItemMaterial, ListPrice, ListPrice-Cost AS ProfitFROM ItemModelORDER BY Category, ListPrice DESC;
22
Common Functions
Lcase To lower caseLen Length/number of charactersMid Get substringTrim Remove leading and trailing spacesUcase To upper case
Date Current dateDateAdd Add days, months, years to a dateDateDiff Subtract two datesFormat Highly detailed formattingNow Current date and time
Abs Absolute valueCos Cosine, all common trig functionsInt Integer, drop decimal valuesSgn SignumRound Round-off
24
Action
ActionCreate a new query in Design viewAdd the Sale tableSelect columns: SaleID and SaleDateCreate new column as LateDate: [SaleDate]+30Another new column as LateMonth: DateAdd(“m”,1,[SaleDate])Run the query
26
Action
ActionCreate a new query in Design viewAdd the Sale tableSelect columns: ShipState and SalesTaxView/Totals (or Totals button)Select “Where” for Total row in StateEnter “CA” as a criteriaSelect “Sum” for SalesTax Total rowRun the query
29
Action
ActionCreate a new query in Design viewAdd the Sale tableSelect columns: ShipState and SalesTaxView/Totals (or Totals button)Select “Sum” for SalesTax Total rowRun the query
31
SQL: Group By
SELECT Sale.ShipState, Sum(Sale.SalesTax) AS SumOfSalesTax
FROM Sale
GROUP BY Sale.ShipState;
top related