The following examples show how SQL syntax can be used to produce a variety of database analysis. The syntax has been tested with VBA for Access2000, and should work well for any related database formats. Copy the SQL by holding down and dragging the mouse over the statement you want to use, then type Ctrl-C. You also may want to copy the sample data table located at the bottom of this page.
SELECT (FirstName & ' ' & LastName) AS Names
FROM tblPeople
WHERE Salary > 50000 AND Hire > #12/31/95#
ORDER BY LastName;
Result: one field in alphabetical order of recent hires making more than $50,000/year.
SELECT IIF(Sex='F', 'Ms. ', 'Mr. ') & LastName AS Salutation
FROM tblPeople
ORDER BY Sex, Salary;
Result: one salutation field arranged from lowest to highest Salary for females first, then males. Related syntax.
SELECT TOP 2 FirstName, LastName, Sex, Salary
FROM tblPeople
ORDER BY Salary;
Result: four fields for the two lowest paid employees.
SELECT TOP 1 FirstName, LastName, Sex, Salary
FROM tblPeople
WHERE Sex="F"
ORDER BY Salary DESC
UNION SELECT TOP 1 FirstName, LastName, Sex, Salary
FROM tblPeople
WHERE Sex="M"
ORDER BY Salary DESC;
Result: four fields for the two highest paid employees; one female and one male.
SELECT DISTINCT FirstName FROM tblPeople;
Result: a list of people's first names with no names duplicated.
SELECT Avg([Salary]) AS AvgSalary FROM tblPeople;
Result: one field (and one record) holding average Salary. Other Aggregate Functions are Sum(), Count(), First(), Last(), Min(), Max(), StDev(), StDevP, and Var().
SELECT FirstName, LastName, ([Salary]) AS [High Salary]
FROM tblPeople
WHERE (([Salary])) > (SELECT AVG([Salary]) FROM [tblPeople]);
Result: three fields for those employees with above-average salaries.
SELECT FirstName, LastName, Sex, Salary AS [High Salary]
FROM tblPeople
WHERE ((Salary) > (SELECT AVG(Salary) FROM tblPeople
WHERE Sex = 'F'))
UNION SELECT FirstName, LastName, Sex, Salary AS [High Salary]
FROM tblPeople
WHERE ((Salary) > (SELECT AVG(Salary) FROM tblPeople
WHERE Sex = 'M'))
ORDER BY Sex, [High Salary] DESC;
Result: four fields for females with above average salaries (among all females) and males with above average salaries (among all males). Arranged from highest- to lowest-salaries for females, then for males.
UPDATE tblContribution SET IsSelected = No;
Result: Set the IsSelected field of all records to No.
This is very useful when you have a form to let users check off the records they want to work with. First you must use table design to give the Boolean field IsSelected the properties Yes/No and format "Check Box", then it can be displayed on a form as a check box. Use the following VB to activate the SQL above.
Dim MySQL as String
MySQL = "UPDATE tblPeople SET IsSelected = No;"
CurrentDB.Execute MySQL
INSERT INTO tblPeople (pkPeopleID,LastName,FirstName,Hire,Salary,Sex)
VALUES (101,'Smith-Kline','Mary', #11/01/00#,50000,'F')
Result: A new record is added to tblPeople.
SELECT DISTINCTROW [CompanyID],[CompanyName] FROM [tblCompany]
UNION SELECT "0","< None >" FROM [tblCompany]
ORDER BY [CompanyName]
Result: two fields for use in a combo or listbox. The list would have company names to pick from, with < None > at the top of the list for users to signify that they are not picking a name. Another example illustrates using more than one Union to provide more user choices.
SELECT DISTINCTROW [CompanyID],[CompanyName] FROM [tblCompany]
UNION SELECT "0","< No companies >" FROM [tblCompany]
UNION SELECT "-1", "< Non-American companies >" FROM [tblCompany]
UNION SELECT "-2", "< American companies >" FROM [tblCompany]
ORDER BY [CompanyName]
Without aliasing
SELECT tblRosterLink.SSN, tblRosterLink.ID, tblRosterLink.Name,
tblRosterLink.SEX, tblRosterLink.[Q3%], tblRosterLink.[Q4%]
FROM tblRosterLink;
With aliasing
SELECT L.SSN, L.ID, L.Name, L.SEX, L.[Q3%], L.[Q4%]
FROM tblRosterLink AS L;
Result: the same identical query. You can alias in SQL as shown, or in QBE use the Properties of the Table/Query source by right clicking the table. Useful only for multi-table syntax. In the case of a single table, this syntax has little or no advantage, because the table name is optional then.
SELECT PrettyName([StudentName]), Right([Section],2)
FROM [tblRoster] ORDER BY [StudentName]
Result: two fields. The function PrettyName would reformat the UT roster format for student names (example: POYNOR; HUGH W.) so that the query list would have names formatted to appear as Hugh W. Poynor. The built in VB function Right() is used to advantage, too. Related syntax.
SELECT DISTINCTROW Party, Sum(Amount) AS [SumP]
FROM tblContribution GROUP BY Party
UNION SELECT 'Total=', Sum(Amount) AS [SumT]
FROM tblContribution;
Result: This SQL syntax summarizes contributions by political party. It is based on records of people's contributions in five political parties. The resulting datasheet would look like this.
| Party | SumP |
|---|---|
| Democrat | $449.00 |
| DK | $99.00 |
| Independent | $493.00 |
| Reform | $100.00 |
| Republican | $226.00 |
| Total | $1,367.00 |
How do you program SQL statements so they use variables instead of constants? This issue arises when you want to re-use SQL, or program general-purpose selection forms for your users. For example, what would be necessary to change the hard-coded SQL below to a SQL statement that selects any salaries and dates?
Look at the following hard-coded SQL to open a recordset.
MySQL = "SELECT pkPeopleID, Salary, Hire FROM tblPeople " & _
"WHERE (Salary > 20000 AND Salary < 100000) " & _
"AND (Hire > #12/31/95# AND Hire < #12/31/00#) " & _
"AND (Sex = 'F');"
The first step in moving away from hard coding is to provide five variables for the salary and date ranges, and sex. The variables could be given values by the user on a query-by-form interface. We will use txtLowSalary, txtHighSalary, txtLowDate, txtHighDate, and txtSex.
txtLowSalary = 20000
txtHighSalary = 100000
txtLowDate = #12/31/95#
txtHighDate = #12/31/00#
txtSex = "F"
Here is the rewritten SQL with the hard-coded values replaced with variables. Because the contents of the variables were chosen to be identical to those in the hard-coded example above, the resulting SQL string created below will be identical to the SQL string above.
MySQL = "SELECT pkPeopleID, Salary, Hire FROM tblPeople WHERE " & _
"(Salary > " & txtLowSalary & " AND Salary < " & txtHighSalary & ") AND " & _
"(Hire > #" & txtLowDate & "# " & "AND Hire < #" & txtHighDate & "#) AND " & _
"(Sex = " & "'" & txtSex & "'" & ");"
There are a number of ampersands, and single and double quotes in this large variable SQL statement. Use the following general rules for creating number variables, date variables and string variables. Each has different syntax requirements.
You might want to read about how to execute the SQL in a program. There is more information available in your program's Help system about using SQL in recordsets and how to use it without recordsets as the data source of controls. SQL is also very useful for Web authors using ASP.
pkPeople ID FirstName LastName Hire Review Salary Sex IsSelected 1 James Carlile 2/2/2002 10/13/2001 $23,200.00 M Yes 2 Andrew Frank 2/9/1997 2/9/1999 $46,276.92 M Yes 3 Janet Lydell 6/25/1994 6/25/1999 $68,674.72 F No 4 Margo ONiell 11/16/1994 11/16/1999 $56,834.25 F No 5 Edward Jones 11/17/1998 11/17/1999 $62,088.20 M Yes 6 Harry Jones 9/22/1978 10/1/1999 $43,920.23 M Yes 7 Jimmy Smith 2/24/2000 2/24/2001 $55,703.70 M No 8 Hugh Poynor 9/12/1989 9/30/1999 $28,923.08 M Yes 9 Edward Smith 3/6/2000 2/24/2001 $25,000.00 M Yes ' this alternate format can be saved as a flat text file 1,James,Carlile,2/2/2002,10/13/2001,23200.00,M,1 2,Andrew,Frank,2/9/1997,2/9/1999,46276.92,M,1 3,Janet,Lydell,6/25/1994,6/25/1999,68674.72,F,0 4,Margo,ONiell,11/16/1994,11/16/1999,56834.25,F,0 5,Edward,Jones,11/17/1998,11/17/1999,62088.20,M,1 6,Harry,Jones,9/22/1978,10/1/1999,43920.23,M,1 7,Jimmy,Smith,2/24/2000,2/24/2001,55703.70,M,0 8,Hugh,Poynor,9/12/1989,9/30/1999,28923.08,M,1 9,Edward,Smith,3/6/2000,2/24/2001,25000.00,M,1