Sunday, December 11, 2011
SELECT Customers.FirstName, Customers.LastName, Accounts.Balance, Branch.Location
FROM Branch INNER JOIN (Customers INNER JOIN Accounts ON Customers.[CID] = Accounts.[CID]) ON Branch.BID = Accounts.BID
WHERE (((Branch.Location)="Central"))
ORDER BY Customers.LastName;
SELECT * From Accounts, Customers
This will give me the Cartesian Product of the
two tables
SELECT * From Accounts, Customers
WHERE Accounts.CID = Customers.CID
This is called an INNER JOIN
SELECT * From Accounts, Customers, Branch
WHERE Accounts.CID = Customers.CID
AND
Branch.BID = Accounts.BID
[SalePrice]/[SqFeet] AS PricePerSqFt
as part of the field list in a Select Query
[SaleSPrice]/[SqFeet] AS PricePerSqFt
Instead of
WhERE BID="b5"
Where BID=[TheBranchIDISupply]
Serial Numbers in Excel, Access?
the way we handle dates and times
dates are whole number offsets from Jan
0, 1900
times are fractions of whole numbers
Test 2:
Query 4:
Select Title, PublDate, LastName, PubName
From Book, Author, Publisher
WHERE Book.AuthorCode = Author.AuthorID
And Book.PubID = Publisher.PubID
And PubName="Wiley"
Order By Title Asc
And PubID = "WI"
Diff types of wildcards
?
*
Begins with: G*
Ends with: *ing
Contains: *Excel 2007*
SELECT [Waxman Commissions].LastName, Sum([Waxman Commissions].SalePrice) AS Fred, Avg([Waxman Commissions].MarketDays) AS AvgOfMarketDays, Sum([Waxman Commissions].Commission) AS SumOfCommission
FROM [Waxman Commissions]
GROUP BY [Waxman Commissions].LastName;
http://office.microsoft.com/en-us/access/
Sunday, December 4, 2011
To create a query, you can use a Wizard + Design View to further modify the query.
OR, you can just write it directly in
SQL.
http://w3schools.com/sql/default.asp
SELECT * From Books
Where Clause
SELECT Title, Year, ListPrice, Publisher
From Books
Where Publisher="Prentice Hall"
wildcards
?
*
SELECT Title, Year, ListPrice, Publisher
From Books
Where Publisher Like "Prentice?Hall"
Order By ListPrice Desc
First use the wizard to choose Table(s)
and fields. Then, add sorting and criteria
in design view.
SELECT Books.Title, Books.Year, Books.ListPrice, Books.Publisher
FROM Books
WHERE (((Books.Publisher)="Prentice Hall"))
ORDER BY Books.ListPrice DESC;
Query 2:
Select Title, Author, Year, ListPrice
From Books
Where Author Like "G*"
Begins with. G*
Ends with. *ing
Contains. *Excel*
SELECT Books.[ISBN], Books.[Title], Books.[Author], Books.[Year], Books.[ListPrice]
FROM Books
WHERE (((Books.[Author]) Like "G*"));
Query 3:
Select Title, Year, ListPrice, Publisher
From Books
Where Year >= 1995 Or ListPrice > 25
Order By Year Desc
Query 4:
Select ProductName, UnitPrice, UnitsOnOrder
From Products
Where (UnitsOnOrder=0) And (UnitPrice>=50 And UnitPrice<=100)
Select ProductName, UnitPrice, UnitsOnOrder
From Products
Where UnitsOnOrder=0 And UnitPrice Between 50 100
OR, you can just write it directly in
SQL.
http://w3schools.com/sql/default.asp
SELECT * From Books
Where Clause
SELECT Title, Year, ListPrice, Publisher
From Books
Where Publisher="Prentice Hall"
wildcards
?
*
SELECT Title, Year, ListPrice, Publisher
From Books
Where Publisher Like "Prentice?Hall"
Order By ListPrice Desc
First use the wizard to choose Table(s)
and fields. Then, add sorting and criteria
in design view.
SELECT Books.Title, Books.Year, Books.ListPrice, Books.Publisher
FROM Books
WHERE (((Books.Publisher)="Prentice Hall"))
ORDER BY Books.ListPrice DESC;
Query 2:
Select Title, Author, Year, ListPrice
From Books
Where Author Like "G*"
Begins with. G*
Ends with. *ing
Contains. *Excel*
SELECT Books.[ISBN], Books.[Title], Books.[Author], Books.[Year], Books.[ListPrice]
FROM Books
WHERE (((Books.[Author]) Like "G*"));
Query 3:
Select Title, Year, ListPrice, Publisher
From Books
Where Year >= 1995 Or ListPrice > 25
Order By Year Desc
Query 4:
Select ProductName, UnitPrice, UnitsOnOrder
From Products
Where (UnitsOnOrder=0) And (UnitPrice>=50 And UnitPrice<=100)
Select ProductName, UnitPrice, UnitsOnOrder
From Products
Where UnitsOnOrder=0 And UnitPrice Between 50 100
Subscribe to:
Posts (Atom)