Familiarization with SQL instructions; creating simple SQL queries in Access using the SELECT command using the IN, BETWEEN, LIKE operators. sql query in ms access

Inserting, deleting, updating records in a database

The ExecuteReader() method retrieves a data reader object that allows you to view the results of the SQL Select statement using a forward read-only stream of information. However, if you need to execute SQL statements that modify the data table, then you need to call the method ExecuteNonQuery() of this command object. This single method is designed to perform inserts, changes, and deletions, depending on the format of the command text.

Concept nonquery means an SQL statement that does not return a result set. Therefore, Select statements are queries, but Insert, Update, and Delete statements are not. Accordingly, the ExecuteNonQuery() method returns an int containing the number of rows affected by these statements, rather than a new set of records.

To show how to modify the contents of an existing database using only the ExecuteNonQuery() query, the next step is to create your own data access library that encapsulates the AutoLot database process.

In a real production environment, your ADO.NET logic will almost certainly be isolated in a .NET .dll assembly for one simple reason - code reuse! This was not done in previous articles so as not to distract you from the tasks at hand. But it would be a waste of time to develop the same connection logic, the same data reading logic, and the same command execution logic for every application that needs to work with the AutoLot database.

By isolating data access logic in a .NET code library, different applications with any user interface (console-style, desktop-style, web-style, etc.) can access the existing library, even regardless of language. And if you develop a data access library in C#, then other .NET programmers will be able to create their user interfaces in any language (for example, VB or C++/CLI).

Our data access library (AutoLotDAL.dll) will contain a single namespace (AutoLotConnectedLayer) that will interact with the AutoLot database using ADO.NET connected types.

Start by creating a new C# Class Library project called AutoLotDAL (short for "AutoLot Data Access Layer"), and then change the original C# code file name to AutoLotConnDAL.cs.

Then rename the scope of the namespace to AutoLotConnectedLayer and change the name of the original class to InventoryDAL, because this class will define various members designed to interact with the Inventory table of the AutoLot database. Finally, import the following .NET namespaces:

Using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; namespace AutoLotConnectedLayer ( public class InventoryDAL ( ) )

Adding connection logic

Our first task is to define methods that allow the calling process to connect to and disconnect from the data source using a valid connection string. Because our AutoLotDAL.dll assembly will be hard-coded to use System.Data.SqlClient class types, define a private SqlConnection variable that will be allocated when the InventoryDAL object is created.

Additionally, define a method OpenConnection() and then another CloseConnection() that will interact with this variable:

Public class InventoryDAL ( private SqlConnection connect = null; public void OpenConnection(string connectionString) ( connect = new SqlConnection(connectionString); connect.Open(); ) public void CloseConnection() ( connect.Close(); ) )

For brevity, the InventoryDAL type will not check for all possible exceptions, and will not throw custom exceptions when encountered various situations(for example, when the connection string is malformed). However, if you were building a production data access library, you would likely have to use structured exception handling techniques to account for any anomalies that might occur at runtime.

Adding insertion logic

Insert new entry to the Inventory table comes down to formatting the SQL statement Insert(depending on user input) and calling the ExecuteNonQuery() method using the command object. To do this, add a public InsertAuto() method to the InventoryDAL class that takes four parameters that correspond to the four columns of the Inventory table (CarID, Color, Make, and PetName). Based on these arguments, generate a line to add a new entry. Finally, execute the SQL statement using the SqlConnection object:

Public void InsertAuto(int id, string color, string make, string petName) ( // SQL statement string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values(@CarId, @ Make, @Color, @PetName)"); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( // Add parameters cmd.Parameters.AddWithValue("@CarId", id); cmd.Parameters.AddWithValue ("@Make", make); cmd.Parameters.AddWithValue("@Color", color); cmd.Parameters.AddWithValue("@PetName", petName); cmd.ExecuteNonQuery(); ) )

Defining classes that represent records in a relational database is a common way to create a data access library. In fact, the ADO.NET Entity Framework automatically generates strongly typed classes that allow you to interact with database data. By the way, the standalone layer of ADO.NET generates strongly typed DataSet objects to represent data from a given table in a relational database.

Creating an SQL statement using string concatenation can be a security risk (think of SQL insertion attacks). It is better to create the command text using a parameterized query, which will be described a little later.

Adding Delete Logic

Deleting an existing record is no more difficult than inserting a new record. Unlike the InsertAuto() code, one important try/catch area will be shown that handles the possible situation where an attempt is made to remove a car that someone has already ordered from the Customers table. Add the following method to the InventoryDAL class:

Public void DeleteCar(int id) ( string sql = string.Format("Delete from Inventory where CarID = "(0)"", id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( try ( cmd.ExecuteNonQuery(); ) catch (SqlException ex) ( Exception error = new Exception("Sorry, this machine is on back order!", ex); throw error; ) ) )

Adding change logic

When it comes to updating an existing record in the Inventory table, the obvious question immediately arises: what exactly can the calling process be allowed to change: the color of the car, the friendly name, the model, or all three? One of the methods maximum increase flexibility - defining a method that takes a parameter of type string, which can contain any SQL statement, but this is risky to say the least.

Ideally, it is better to have a set of methods that allow the calling process to modify records different ways. However, for our simple data access library, we will define a single method that allows the calling process to change the friendly name of the specified car:

Public void UpdateCarPetName(int id, string newpetName) ( string sql = string.Format("Update Inventory Set PetName = "(0)" Where CarID = "(1)"", newpetName, id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( cmd.ExecuteNonQuery(); ) )

Adding sampling logic

Now we need to add a method to select records. As shown earlier, a specific data provider's data reader object allows you to select records using a read-only cursor. By calling the Read() method, you can process each record one at a time. This is all great, but now we need to figure out how to return these records to the calling application layer.

One approach would be to retrieve the data using the Read() method and then populate and return a multidimensional array (or another object like the generic List ).

Another way is to return a System.Data.DataTable object, which actually belongs to the standalone ADO.NET layer. DataTable is a class that represents a tabular block of data (like a paper or spreadsheet).

The DataTable class contains data as a collection of rows and columns. These collections can be populated programmatically, but the DataTable type has a Load() method that can populate them automatically using a data reader object! Here's an example where data from the Inventory table is returned as a DataTable:

Public DataTable GetAllInventoryAsDataTable() ( DataTable inv = new DataTable(); string sql = "Select * From Inventory"; using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( SqlDataReader dr = cmd.ExecuteReader(); inv .Load(dr); dr.Close(); ) return inv; )

Working with Parameterized Command Objects

So far, in the insert, update, and delete logic for the InventoryDAL type, we have used hard-coded string literals for each SQL query. You're probably aware of the existence of parameterized queries, which allow you to treat SQL parameters as objects rather than just a piece of text.

Working with SQL queries in a more object-oriented manner not only helps reduce typos (with strongly typed properties), but parameterized queries are typically much faster than string literal queries because they are parsed only once (rather than every time). as happens when the CommandText property is set to an SQL string). Additionally, parameterized queries protect against SQL injection attacks (a well-known data access security problem).

To support parameterized queries, ADO.NET command objects maintain a collection of individual parameter objects. By default, this collection is empty, but you can add any number of parameter objects that match placeholder parameters in a SQL query. If you need to associate a SQL query parameter with a member of the parameters collection of some command object, precede the SQL parameter with the @ symbol (at least when working with Microsoft SQL Server, although not all DBMSs support this notation).

Setting parameters using the DbParameter type

Before we begin creating parameterized queries, let's become familiar with the DbParameter type (the base class for provider parameter objects). This class has a number of properties that allow you to specify the name, size, and type of the parameter, as well as other characteristics, such as the viewing direction of the parameter. Some important properties of DbParameter type are given below:

DbType

Gets or sets the data type from a parameter, represented as a CLR type

Direction

Returns or sets the type of parameter: input-only, output-only, input and output, or parameter to return a value

IsNullable

Returns or sets whether a parameter can accept empty values

ParameterName

Gets or sets the DbParameter name

Size

Returns or sets the maximum data size for a parameter (useful for text data only)

Value

Returns or sets the value of a parameter

To demonstrate how to populate a collection of command objects with DBParameter-compatible objects, let’s rewrite the InsertAuto() method so that it will use parameter objects (all other methods can be remade similarly, but the present example will be enough for us):

Public void InsertAuto(int id, string color, string make, string petName) ( // SQL statement string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values("(0) ","(1)","(2)","(3)")", id, make, color, petName); // Parameterized command using (SqlCommand cmd = new SqlCommand(sql, this.connect)) ( SqlParameter param = new SqlParameter(); param.ParameterName = "@CarID"; param.Value = id; param.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(param); param = new SqlParameter(); param. ParameterName = "@Make"; param.Value = make; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@Color "; param.Value = color; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@PetName"; param.Value = petName; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); ) )

Note that the SQL query here also contains four placeholder characters, each preceded by an @ symbol. Using the ParameterName property on the SqlParameter type, you can describe each of these placeholders and specify various information (value, data type, size, etc.) in a strongly typed manner. After all parameter objects are prepared, they are added to the command object collection using the Add() call.

Various properties are used here to design parameter objects. However, note that parameter objects support a number of overloaded constructors that allow you to set the values ​​of various properties (which results in a more compact code base). Also note that Visual Studio 2010 has various graphical designers that will automatically generate a lot of this tedious parameter-manipulating code for you.

Creating a parameterized query often results in more code, but the result is a more convenient way to programmatically tune SQL statements, as well as better performance. This technique can be used for any SQL query, although parameterized queries are most useful if you need to run stored procedures.

The SELECT statement instructs the Microsoft Access database engine to return information from the database as a set of records.

Syntax

SELECT [ predicate] { * | table.* | [table.]field1 [, [table.]field2 [, ...]]}
FROM table_expression [, ...]




The SELECT statement includes the following elements.

Element

Description

predicate

One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. Predicates are used to limit the number of records returned. If no predicate is given, the default is ALL.

Indicates that all fields are selected from the specified table or tables.

table

The name of the table containing the fields with the selected records.

field1, field2

The names of the fields containing the data to be retrieved. If multiple fields are specified, the data will be retrieved in the order their names are listed.

nickname1, nickname2

Names that are used as column headings instead of the original column names in table.

table_expression

One or more table names containing the data to be retrieved.

external_database

The name of the database containing the tables specified in the component table_expression, if they are not in the current database.

Notes

To perform this operation, the Microsoft Access database engine searches for the specified table or tables, retrieves the specified columns, selects the rows that meet the condition, and sorts the resulting rows in the specified order.

SELECT statements do not change data in the database.

SELECT is usually the first word in an SQL statement. SELECT and SELECT...INTO are the most common SQL statements.

The minimum syntax for a SELECT statement is as follows:

SELECT fields FROM table

You can use an asterisk (*) to select all fields in a table. The example below shows that the Employees table has all fields selected.

SELECT * FROM Employees;

If the field name is included in multiple tables in the FROM clause, precede it with the table name and the statement . (dot). In the following example, the “Department” field appears in two tables at once: “Employees” and “Managers”. You can use an SQL statement to select departments from the Employees table and executive names from the Executives table.

SELECT Employees.Department, Supervisors.SupvName FROM Employees INNER JOIN Supervisors WHERE Employees.Department = Supervisors.Department;

When a RecordSet object is created, the table field name is used by the Microsoft Access Database Engine as the name of the "Field" object in the object Recordset. If the field name needs to be changed or is not provided by the expression that generates the field, use the reserved word AS. The following example shows how the BirthDate header is used to name the returned object Field in the received object Recordset.

SELECT BirthDate AS Birth FROM Employees;

When using aggregate functions or queries that return ambiguous or duplicate object names Field, you must use the AS clause to assign the object Field another name. In the example below, the returned object Field in the received object Recordset the name "Number_of_employees" is assigned.

SELECT COUNT(EmployeeID) AS HeadCount FROM Employees;

When working with a SELECT statement, you can use additional clauses to further restrict and organize the data retrieved. For more information, see the help topic for the offer you are using.

This lesson is dedicated to SQL queries to the database on VBA Access. We will look at how INSERT, UPDATE, DELETE queries are made to the database in VBA, and we will also learn how to get a specific value from a SELECT query.

Those who program in VBA Access and while working with a SQL server database, very often they are faced with such a simple and necessary task as sending an SQL query to the database, be it INSERT, UPDATE or a simple SQL SELECT query. And since we are novice programmers, we should also be able to do this, so today we will do just that.

We have already touched on the topic of obtaining data from a SQL server, where we wrote code in VBA to obtain this data, for example, in the article about Uploading data into a text file from MSSql 2008, or we also touched on it a little in the material Uploading data from Access to a Word and Excel template. but one way or another, we looked at this superficially, and today I propose to talk about this in a little more detail.

Note! All the examples below are considered using the Access 2003 ADP project and the MSSql 2008 database. If you don’t know what an ADP project is, then we looked at this in the material How to create and configure an Access ADP project

Source data for examples

Let's say we have a table test_table, which will contain the numbers and names of the months of the year (queries are executed using Management Studio)

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

As I already said, we will use an ADP project configured to work with MS SQL 2008, in which I created a test form and added a start button with a signature "Run", which we will need to test our code, i.e. We will write all the code in the event handler " Button press».

Queries to the database INSERT, UPDATE, DELETE in VBA

In order not to delay too long, let's get started right away, let's say we need to add a row to our test table ( code commented)/

Private Sub start_Click() "Declare a variable to store the query string Dim sql_query As String "Write the query we need into it sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Execute it DoCmd. RunSQL sql_query End Sub

IN in this case the request is executed using the current database connection parameters. We can check whether the data has been added or not.

As you can see, the data has been inserted.

In order to delete one line we write the following code.

Private Sub start_Click() "Declare a variable to store the query string Dim sql_query As String "Write a delete query into it sql_query = "DELETE test_table WHERE id = 6" "Run it DoCmd.RunSQL sql_query End Sub

If we check, we will see that the desired line has been deleted.

To update the data, write to the sql_query variable update request, I hope the meaning is clear.

SELECT query to a database in VBA

Here things are a little more interesting than with other SQL constructs.

First, let's say we need to get all the data from the table, and, for example, we will process it and display it in a message, and you, of course, can use it for other purposes, for this we write the following code

Private Sub start_Click() "Declare variables "For a set of records from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for displaying the final data in the message Dim str As String "Create a new object for records set RS = New ADODB .Recordset "Query line sql_query = "SELECT id, name_mon FROM test_table" "Run the query using the current project connection settings RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Loop through the records While Not (RS.EOF) "Fill the variable to display the message str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "go to the next record RS.MoveNext Wend "Output the message msgbox str End Sub

Here we are already using VBA Access loops to iterate through all the values ​​in our recordset.

But quite often it is necessary to obtain not all values ​​from a set of records, but just one, for example, the name of the month by its code. And to do this, it’s somehow expensive to use a loop, so we can simply write a query that will return just one value and access it, for example, we’ll get the name of the month using code 5

Private Sub start_Click() "Declare variables" For a set of records from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String to display the final value Dim str As String "Create a new object for records set RS = New ADODB.Recordset "Query line sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Run the query using the current project connection settings RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Get our value str = RS.Fields(0) msgbox str End Sub

For universality, here we have already addressed not by the cell name, but by its index, i.e. 0, and this is the very first value in Recordset, in the end we got the value "May".

As you can see, everything is quite simple. If you often need to get a specific value from the database ( as in the last example), then I recommend outputting all the code into a separate function (How to write a function in VBA Access 2003) with one input parameter, for example, the month code ( if we consider our example) and simply, where it is necessary to display this value, call the function we need with the required parameter and that’s it, by doing this we will significantly reduce the VBA code and improve the perception of our program.

That's all for today. Good luck!

Sample SQL queries can be used to learn and practice writing SQL queries in MS Access.

One SQL query can be nested within another. A subquery is nothing more than a query within a query. Typically, a subquery is used in the WHERE clause. But there are other ways to use subqueries.

Query Q011. Information about products from the m_product table is displayed, the codes of which are also in the m_income table:

SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);

Query Q012. A list of products from the m_product table is displayed, the codes of which are not in the m_outcome table:

SELECT *
FROM m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);

Request Q013. This SQL query displays a unique list of product codes and names that are in the m_income table but not in the m_outcome table:

SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Query Q014. A unique list of categories whose names begin with the letter M is displayed from the m_category table:

SELECT DISTINCT title
FROM m_product
WHERE title LIKE "M*";

Query Q015. An example of performing arithmetic operations on fields in a query and renaming fields in a query (alias). This example calculates expense = quantity*price and profit for each item expense entry, assuming profit is 7 percent of sales:


amount*price/100*7 AS profit
FROM m_outcome;

Query Q016. Having analyzed and simplified arithmetic operations, you can increase the speed of query execution:

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
outcome_sum*0.07 AS profit
FROM m_outcome;

Request Q017. You can use the INNER JOIN statement to join data from multiple tables. In the following example, depending on the value of ctgry_id, each entry in the m_income table is matched with the name of the category from the m_category table to which the product belongs:

SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;

Request Q018. Such functions as SUM - sum, COUNT - quantity, AVG - arithmetic average, MAX - maximum value, MIN – the minimum value is called aggregate functions. They accept many values ​​and after processing them return a single value. An example of calculating the sum of the product of the amount and price fields using the aggregate function SUM.

Rules: square brackets indicate an [optional part] of a construction. A vertical bar indicates a choice between options (var1|var2). The ellipsis means possible repetition several times - 1 time, 2 times [, …]

SELECT statement

Instructs the database kernel Microsoft data Access returns information from a database as a recordset.

Syntax

SELECT [ predicate] { * | table.* | [table.]field1

[, [table.]field2 [, ...]]}
FROM table_expression [, ...]




The SELECT statement includes the following elements.

Element

Description

Predicate

One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. Predicates are used to limit the number of records returned. If no predicate is given, the default is ALL.

Indicates that all fields are selected from the specified table or tables

Table

The name of the table from whose fields the records are selected

field1, field2

The names of the fields containing the data to be retrieved. If multiple fields are specified, the data will be retrieved in the order their names are listed

nickname1, nickname2

Names used as column headings instead of original column names tables

table_expression

One or more table names containing the data to be retrieved.

external_database

The name of the database containing the tables specified in the component table_expression if they are not in the current database

Notes

To perform this operation, the Microsoft Access database engine searches the specified table(s), retrieves the desired columns, selects the rows that meet the specified conditions, and sorts or groups the resulting rows in the specified order.

SELECT statements do not change database data.

The SELECT statement is usually the first word of the SQL statement (SQL statement (string). Expression that defines SQL command, such as SELECT, UPDATE, or DELETE, and including clauses such as WHERE or ORDER BY. SQL statements/strings are commonly used in queries and statistical functions.) Most SQL statements are either SELECT statements or SELECT...INTO statements.

The minimum syntax for a SELECT statement is as follows:

SELECT fields FROM table

You can use an asterisk (*) to select all fields in a table. The following example selects all fields in the Employees table.

SELECT * FROM Employees;

If the field name is included in multiple tables in the FROM clause, precede it with the table name and the statement «.» (dot). In the following example, the "Department" field is present in the "Employees" and "Supervisors" tables. The SQL statement selects departments from the Employees table and supervisor names from the Supervisors table.

SELECT Employees. Department, Heads. Executive Name FROM Employees INNER JOIN Executives WHERE Employees. Department = Managers. Department;

When you create a RecordSet object, the table field name is used by the Microsoft Access database engine as the name of the "Field" object in the object RecordSet. If the field name needs to be changed or is not provided by the expression that generates the field, use a reserved word (Reserved word. A word that is an element of a language, such as Visual Basic. Reserved words include names of instructions, built-in functions and data types, methods, operators, and objects.) AS. The following example shows how the "Day" header is used to name the returned object Field in the received object RecordSet.

SELECT Birthday AS Day FROM Employees;

When working with aggregate functions or queries that return ambiguous or identical object names Field, you should use the AS clause to create a different object name Field. In the following example, the returned object Field in the received object RecordSet is given the name "Census".

SELECT COUNT(EmployeeCode) AS Census FROM Employees;

When working with a SELECT statement, you can use additional clauses to further restrict and organize the data retrieved. For more information, see the help topic for the offer you are using.

FROM clause

Specifies tables and queries that contain the fields listed in the SELECT statement.

Syntax

SELECT field_list
FROM table_expression

A SELECT statement containing a FROM clause includes the following elements:

Element

Description

field_list

table_expression

An expression defining one or more tables - data sources. The expression can be a table name, a stored query name, or a result expression constructed using an INNER JOIN, LEFT JOIN, or RIGHT JOIN operator

external_database

The full path to the external database containing all the tables specified in table_expression

Notes


The presence of a FROM clause after a SELECT statement is required.

The order in which tables are listed in table_expression doesn't matter.

Using linked tables (Linked table. A table that is saved in a file that is not part of the open database but is accessible from Microsoft Access. The user can add, delete, and change records in the linked table, but cannot change its structure.) instead of the clause IN, you can make the process of retrieving data from an external database easier and more efficient.

The example below shows how to retrieve data from the Employees table.

SELECT Last name, First name

FROM Employees;

Indicates the records selected for SQL queries (SQL (Structured Query Language). A structured query and database programming language widely used for accessing, querying, updating, and manipulating data in relational DBMSs.)

Syntax

SELECT ]]
FROM table

The SELECT statement containing these predicates includes the following components:

Component

Description

Implied if no predicates are included. The Microsoft Access database engine selects all records that match the conditions of an SQL statement (SQL statement (string). An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses, such as WHERE or ORDER BY. SQL statements/strings are typically used in queries and statistical functions). The following two identical examples show how to return all records from the Employees table.

FROM Employees

ORDER BY EmployeeCode;

FROM Employees

ORDER BY EmployeeCode;

Excludes records that contain duplicate data in the selected fields. Only the unique values ​​of each of the fields listed in the SELECT statement are included in the query results. For example, some employees listed in the Employees table may have the same last name. If two records contain the last name "Ivanov" in the Last Name field, the following SQL statement returns only one record containing the last name "Ivanov".

SELECT DISTINCT LastName

If the DISTINCT component is omitted, the query returns both records with the last name "Ivanov".

If the SELECT clause contains multiple fields, the combination of all field values ​​is included in the query results only if it is unique for that record.

The results of a query that uses the DISTINCT component are not updated to reflect subsequent changes made by other users.

Excludes data from records that are repeated in their entirety rather than containing individual fields with the same data. Let's assume that a query has been created that connects the “Customers” and “Orders” tables using the “Customer Code” field. The Customers table does not contain duplicate Customer ID fields, but they do exist in the Orders table because each customer can have multiple orders. The following SQL statement shows how to use the DISTINCTROW component to list organizations that have made at least one order, without mentioning the details of those orders.

SELECT DISTINCTROW Title FROM Customers INNER JOIN Orders

ON Clients. CustomerId = Orders. Client code

ORDER BY Title;

If the DISTINCTROW component is omitted, the query results in multiple rows for each organization that ordered multiple times.

The DISTINCTROW component only takes effect when selecting fields from some of the tables used in the query. The DISTINCTROW component is ignored if the query includes only one table or if fields are retrieved from all tables.

TOP n

Returns the specified number of records that are among the first or last records in the range specified by the ORDER BY clause. Let's say you want to display the names of the top 25 students from the class of 1994.

FirstName, LastName

WHERE GraduationYear = 2003

ORDER BY GradePointAverage DESC;

If you do not include the ORDER BY clause, the query will return a random set of 25 records from the Students table that satisfies the WHERE clause.

The predicate TOP does not involve a choice between equal values. If the 25th and 26th records in the previous example had the same GPA, the query would return 26 records.

You can also use the PERCENT reserved word to retrieve some percentage of the first or last records in the range specified by the ORDER BY clause. Suppose that instead of the top 25, you want to display the bottom 10% of students in the graduating class.

SELECT TOP 10 PERCENT

FirstName, LastName

WHERE GraduationYear = 2003

ORDER BY GradePointAverage ASC;

The ASC predicate specifies the output of values ​​from the lower part of the range. The value that follows the TOP predicate must be a value of type Integer (Integer data type. The basic data type used to store integer values. Type variable Integer is stored as a 64-bit (8-byte) number in the range -32768 to 32767.) unsigned.

The TOP predicate does not affect whether the query can be updated.

table

The name of the table from which records are retrieved.

see also

SELECT statement

FROM clause

WHERE clause

Determines which records from the tables listed in the FROM clause are processed by SELECT, UPDATE, or DELETE statements.

Syntax

SELECT field_list
FROM table_expression
WHERE selection_conditions

A SELECT statement containing a WHERE clause includes the following parts.

Part

Description

field_list

The name of the field or fields that are retrieved along with any aliases (Alias ​​(SQL). An alternative name for a table or field in an expression. Aliases are typically used as shorter table or field names for ease of subsequent reference in programs, to prevent ambiguous references, and to obtaining more descriptive names when displaying query results.), predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or with any other parameter of the SELECT statement.

table_expression

The name of the table or tables from which data is retrieved.

selection_conditions

Expression (Expression. A combination of mathematical and logical operators, constants, functions, field names, controls, and properties that results in a single value. The expression can perform calculations, process text, or validate data.) that must match the records included in query results.

Notes

The Microsoft Access database engine selects records that meet the conditions listed in the WHERE clause. If the WHERE clause is not specified, the query returns all rows in the table. If a query specifies multiple tables but does not specify a WHERE or JOIN clause, the query produces a Cartesian product (Cartesian product. Is the result of executing an SQL SELECT statement that has a FROM clause that references two or more tables and no WHERE or JOIN clause that specifies method of joining.) tables.

The WHERE clause is not required, but if used, it must follow the FROM clause. For example, you can select all employees from the sales department (WHERE Department = "Sales") or all customers between the ages of 18 and 30 (WHERE Age Between 18 And 30).

If a JOIN clause is not used for a SQL join operation on multiple tables, the resulting object Record set it will be impossible to update.

The WHERE clause is similar to the HAVING clause and specifies the selected records. After the records are grouped by the GROUP BY clause, the HAVING clause also determines the record to be displayed.

The WHERE clause is used to exclude records that do not need to be grouped using the GROUP BY clause.

Use various expressions to determine which records are returned by the SQL statement. For example, the following SQL statement selects all employees whose salary exceeds RUR.

SELECT Last name, Salary FROM Employees WHERE Salary > 21000;

The WHERE clause can contain up to 40 expressions connected by logical operators (for example, AND And OR).

If you enter a field name that contains spaces or punctuation, you must enclose it in square brackets (). For example, a customer details table might contain information about specific customers.

SELECT [Customer's favorite restaurant]

Specifying an argument selection_conditions, date literals (Date literal. Any sequence of characters in a valid format, enclosed in number signs (#). Valid formats are the date format specified in the Language and Standards settings, and universal format dates.) should be presented in US format, even if you are using a non-US version of the Microsoft Access database engine. For example, the date "May 10, 1996" is written as 10/5/96 in the UK and as 05/10/1996 in Russia. Remember to enclose date literals in number signs (#), as shown in the examples below.

To find records for 10 May 1996 in the UK database, use the following instructions SQL:

SELECT * FROM Orders WHERE Shipment Date = #10.05.1996#;

You can also use the function DateValue, recognizing the international parameters set Microsoft Windows®. For example, for Russia use this code:

SELECT * FROM Orders WHERE Shipment Date = DateValue("05/10/1996");

And the following code is for the UK:

SELECT * FROM Orders WHERE Shipment Date = DateValue("10/5/96");

Note. If the column specified in the selection criteria row is of type GUID (Replica ID (GUID). A 16-byte field in a Microsoft Access database used to uniquely identify replication. GUIDs are used to identify replicas, replica sets, tables, records and other objects. In Microsoft Access databases, GUID codes are called replica codes.), the selection conditions use a slightly different syntax.

WHERE ReplicaID = (GUID (AB-CDEF0ABCDEF))

Make sure nested parentheses and hyphens are positioned correctly.

Source page: http://office. /ru-ru/access/HA.aspx? pid=CH

GROUP BY clause

Combines records with the same values ​​that are in the specified list of fields into one record. A summary value is created for each record if an SQL aggregation function is included in the SELECT statement, such as Sum or Count.

Syntax

SELECT field_list
FROM table
WHERE selection_condition

A SELECT statement containing a GROUP BY clause includes the following elements:

Element

Description

field_list

The names of the fields that are retrieved along with any aliases (Alias ​​(SQL). An alternative name for a table or field in an expression. Aliases are typically used as shorter table or field names for ease of subsequent reference in programs, to prevent ambiguous references, and to obtain more informative names when displaying query results.) and statistical SQL functions, predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other parameters of the SELECT statement

table

selection_conditions

Selection condition. If the statement contains a WHERE clause, then after it is applied to the records, the values ​​will be grouped by the Microsoft Access database engine.

group_field_list

group_field_list

Notes

The GROUP BY clause is optional.

If SQL statistical functions are not included in the SELECT statement, summary values ​​are not calculated.

GROUP BY field values ​​that are Null (Null. A value that can be entered into a field or used in expressions and queries to indicate missing or unknown data. In Visual Basic keyword Null specifies a Null value. Some fields, such as primary key fields, cannot contain Null values.) are grouped and not omitted. However, the values Null are not evaluated by any of the SQL statistical functions.

The WHERE clause is used to exclude rows that do not need to be grouped. The HAVING clause is used to filter records after grouping.

Fields from the GROUP BY field list that do not contain Memo data type (MEMO Field data type. A field data type in a Microsoft Access database. A MEMO field can contain up to 65535 characters.) or OLE Object (OLE Object Field data type). A field data type used to save objects from other applications linked to or embedded in a Microsoft Access database.) can reference any field in any table specified in the FROM clause, even if the field is not included in the SELECT statement. To do this, it is enough to have at least one SQL statistical function in the SELECT statement. The Microsoft Access database engine does not allow grouping by fields containing MEMO Field or OLE Object data.

All fields in the SELECT field list must either be contained in a GROUP BY clause or be arguments to an SQL aggregation function.

see also

SELECT statement

SELECT...INTO statement

Predicates ALL, DISTINCT, DISTINCTROW, TOP

FROM clause

HAVING offer

ORDER BY clause

WHERE clause

SQL statistical functions

Source page: http://office. /ru-ru/access/HA.aspx? pid=CH

HAVING offer

Defines grouped records that should appear in a SELECT statement with a GROUP BY clause. After the records have been grouped by the GROUP BY clause, the HAVING clause will show those that meet its conditions.

Syntax

SELECT field_list
FROM table
WHERE selection_conditions
GROUP BY group_field_list

A SELECT statement containing a HAVING clause includes the following elements:

Element

Description

field_list

The names of the fields that are loaded along with any aliases (Alias ​​(SQL). An alternative name for a table or field in an expression. Aliases are typically used as shorter table or field names for ease of subsequent reference in programs, to prevent ambiguous references, and to obtain more informative names when displaying query results.) and SQL statistical functions, predicates (ALL, DISTINCT, DISTINCTROW, or TOP) or with other parameters of the SELECT statement.

table

Name of the table from which records are loaded

selection_condition

Selection condition. If the statement contains a WHERE clause, the Microsoft Access database engine will group the values ​​after it is applied to the records.

group_field_list

Names of fields (up to 10) used to group records. The order of names in group_field_list determines the level of grouping - from highest to lowest

group_condition

An expression that specifies the records to be displayed

Notes

The HAVING clause is optional.

The HAVING clause is similar to the WHERE clause that determines the selection of records. After grouping records with the GROUP BY clause, the HAVING clause determines the records to be displayed.

SELECT TypeCode,

Sum(InStock)

FROM Products

GROUP BY TypeCode

HAVING Sum(InStock) > 100 And Like "TEL*";

The HAVING clause can contain up to 40 expressions linked by logical operators such as And And Or.

Source page: http://office. /ru-ru/access/HA.aspx? pid=CH

ORDER BY clause

Sorts the records returned by the query in ascending or descending order of the values ​​of the specified field(s).

Syntax

SELECT field_list
FROM table
WHERE selection_condition
[, field2 ][, ...]]]

A SELECT statement that contains an ORDER BY clause includes the following elements.

Element

Description

field_list

The names of the fields that are retrieved along with any aliases (Alias ​​(SQL). An alternative name for a table or field in an expression. Aliases are typically used as shorter table or field names for ease of subsequent reference in programs, to prevent ambiguous references, and to obtain more informative names when displaying query results.) and SQL statistical functions, predicates (ALL, DISTINCT, DISTINCTROW, or TOP) or with other parameters of the SELECT statement.

table

Name of the table from which records are retrieved

selection_conditions

Selection conditions. If the statement contains a WHERE clause, then after it is applied to the records, the Microsoft Access database engine will order the values ​​of the records

field1, field2

Names of the fields by which records are sorted.

Notes

The ORDER BY clause is optional. It should be used when you need to display data in sorted form.

The default sort order is (Sort Order. A way to arrange data based on its values ​​and type. Data can be sorted alphabetically, by numeric values, or by date. The sort order can be ascending (0 to 100, A to Z) or descending (from 100 to 0, from Z to A).) ascending (from A to Z, from 0 to 9). The examples below demonstrate sorting employee names by last name.

SELECT Last name, First name

FROM Employees

ORDER BY Last name;

SELECT Last name, First name

FROM Employees

ORDER BY Last name ASC;

To sort fields in descending order (Z to A, 9 to 0), append the reserved word DESC to the name of each field. The following example demonstrates sorting in descending order based on employee salaries.

SELECT Last name, Salary

FROM Employees

ORDER BY Salary DESC, Last Name;

If you specify fields in the ORDER BY clause that contain data of type MEMO Field (Memo Field data type. A field data type in a Microsoft Access database. A MEMO field can contain up to 65,535 characters.) or OLE Object Field (OLE Object Field data type "A field data type used to save objects from other applications linked to or embedded in a Microsoft Access database.), this will generate an error. The Microsoft Access database engine cannot sort these field types.

The ORDER BY clause is typically the last clause in a SQL statement (SQL statement (string). An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses, such as WHERE or ORDER BY. SQL statements/strings are commonly used in queries and statistical functions.).

You can include additional fields in the ORDER BY clause. Records are first sorted by the field specified first in the ORDER BY clause. Records with the same values ​​in the first field are then sorted by the field specified by the second, and so on.
see also

SELECT statement

SELECT...INTO statement

Predicates ALL, DISTINCT, DISTINCTROW, TOP

FROM clause

GROUP BY clause

HAVING offer

WHERE clause

SQL statistical functions

Source page: http://office. /ru-ru/access/HA.aspx? pid=CH

INNER JOIN operation

Joins records from two tables if the connecting fields of these tables contain the same values.

Syntax

FROM Table 1 INNER JOIN table 2 ON Table 1.field1 comparison_operator table2.field2

The INNER JOIN operation consists of the following elements:

Element

Description

Table 1, table 2

Names of the tables containing the records to be joined

field1, field2

Names of the fields to be linked. Non-numeric fields must be of the same data type (Data Type. A characteristic of a field that defines the type of data that the field can contain. There are following types data: Boolean, Integer, Long, Currency, Single, Double, Date, String and Variant (default).) and contain data of the same type. However, the names of these fields may be different

comparison_operator

Any comparison operator: (=,<, >, <=, >= or<>)