SQL SELECT Statement
Useful with Calibration Management Software Reports
SQL (pronounced sequel) stands for Structured Query Language. SQL is the basic language of most common databases, including MS Access and MS SQL Server, the two databases that Calibration Control can use. For the purpose of report writing, the part of the SQL language we need to focus on is the SELECT statement, which retrieves information from the calibration management software (Calibration Control). Although SELECT statements are used by reports, web pages, on screen displays, and even moving data between applications, we will need SQL primarily for reports.
A Primer on Tables
Before we get started, let’s make sure we understand the source of our data when we use a SELECT statement. Within a database, information is stored in tables that look something like a spreadsheet with columns and rows. Unlike a spreadsheet, a table uses records (horizontal rows) and fields (vertical columns).
Think of each record as if it were a photocopied standard form that you use for keeping track of your test equipment and that you keep all these forms in a file. The file may have a hundred forms, each with information describing a specific piece of test equipment. Just like a table, you can sort your forms in a different order and you can find records by scanning a single field on each form; it just takes a bit longer with paper compared to a database table.
The Equipment Master View
Within Calibration Control, you will probably derive most of your reports from the qryEquipmentMaster view (also called a query) because it has most of the fieldsyou need for your test equipment records. The fields in the view will also be easier to read than their corresponding codes in the root table. Think of a view as a way to pre-package part of the SQL complexity that makes your data easier to work with.
As an example, if you look at the contents of the tblEquipmentMaster table (where your data is actually stored) you’ll see fields like ModelNumberID with meaningless numbers in the fields (see image below). On the other hand, if you look at the qryEquipmentMaster, you’ll see the actual model numbers and descriptions that you’re familiar with. This is because the view has SQL code in it that looks up and displays the meaning of the ModelID code so you don’t need to.
The SQL SELECT Statement
There are four main parts of the SELECT statement that we need to cover.
- SELECT – ( IDs fields from a table that will be included.)
- FROM – ( IDs the table where the fields come from.)
- WHERE – ( Defines the filter that includes only the records you want to include.)
- ORDER BY – ( Defines the sort order of the records.)
While the SELECT and FROM clauses are always required, you will only need to include the WHERE and ORDER BY clauses when you want to filter and sort. Otherwise, your results will include all records and in no particular order other than the physical order of the underlying table.
Here is an example of a common SQL statement you would use to return all the fields from the qryEquipmentMaster view:
SELECT * FROM qryEquipmentMaster;
Pretty easy, right? Notice the asterisk (*)? The asterisk is a wildcard that includes all fields. With the relatively easy SELECT statements that we need in our calibration management software, using the wildcard is usually your best bet because you don’t need to worry about whether you forgot a field or not when you’re designing your report.
Now let’s assume that we want to filter and sort our results in the following example:
SELECT * FROM qryEquipmentMaster WHERE DepartmentCode = 'QA' ORDER BY Location;
You can even add multiple filters and multiple sorts with the following MS Access:
SELECT * FROM qryEquipmentMaster WHERE (DepartmentCode = 'QA') AND (CalibrationDue <> #5/1/2011#) ORDER BY Location DESC, SerialNumber;
Note that the date value has number signs (#) around it rather than the single quotes of the text values? You need to use the # sign when you are using MS Access and the single quote (‘) when using SQL Server. Although both databases use SQL Server, there are still slight differences.
Here's the same code for MS SQL Server:
SELECT * FROM qryEquipmentMaster WHERE (DepartmentCode = 'QA') AND (CalibrationDue <> '5/1/2011') ORDER BY Location DESC, SerialNumber;
The final example deals with filtering for numeric values and is compliant with both MS Access and MS SQL Server. Note that the value (1) uses neither the single quote (‘) or the number sign (#)?
SELECT * FROM qryEquipmentMaster WHERE FrequencyUnits = 1;
Last updated: 7 Jul 2017