How to send a query to a database using VBA Access. sql query in ms access

11.05.2021

Registration

Rules square brackets mean [optional part] of the construction. vertical bar denotes a choice between options (var1|var2). An ellipsis means a possible repetition several times - times 1, times 2 [, ...]

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 specified, the default is ALL.
	Specifies that all fields are selected from the specified table or tables
table	The name of the table from whose fields records are selected
field1, field2	The names of the fields containing the retrieved data. If multiple fields are specified, the data will be retrieved in the order in which their names are listed.
alias1, alias2	Names used as column headings instead of original column names tables
table_expression	One or more table names containing the data to retrieve.
external_database	The name of the database containing the tables specified in the component table_expression if they are not in the current database

Remarks

To perform this operation, the Microsoft Access database engine searches for the specified table(s), extracts 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 an SQL statement (SQL statement (string). An 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 in aggregate functions.). Most SQL statements are either SELECT statements or SELECT...INTO statements.

The minimal 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 more than one table 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 Bosses tables. The SQL statement selects the departments from the Employees table and the names of the bosses from the Bosses table.

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

When creating 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. record set. 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 a language element, such as the Visual Basic language. operators and objects.) AS. The following example shows how the "Day" header is used to name the returned object Field in the received object record set.

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 another object name Field. In the following example, the returned object Field in the received object record set named "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 received data. For more information, see the help topic for the offer you are using.

FROM clause

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

Syntax

SELECT field_list
FROM table_expression

The SELECT statement that contains the FROM clause includes the following elements:

Element	Description
field_list
table_expression	An expression that defines one or more tables - data sources. The expression can be a table name, a stored query name, or a result expression composed using the INNER JOIN, LEFT JOIN, or RIGHT JOIN operations
external_database	Full path to external database containing all tables specified in table_expression

Remarks

The presence of the FROM clause after the SELECT statement is mandatory.

Listing order of tables in table_expression irrelevant.

Using linked tables (Linked table. A table that is stored in a file that does not belong to an open database, but is accessible from Microsoft Access. The user has the ability to add, delete, and modify records in the linked table, but cannot change its structure.) instead of a sentence 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;

Specifies the records selected for SQL queries (SQL (Structured Query Language). A structured query and database programming language widely used to access, query, update, and manipulate data in relational DBMS.).

Syntax

SELECT ]]
FROM table

The SELECT statement containing these predicates includes the following components:

Component	Description
	Implied if none of the 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 commonly used in queries and in aggregate functions.). The following two identical examples show how to return all records in 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 of the 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 SQL statement below returns only one record that contains the last name Ivanov. SELECT DISTINCT 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 the values of all fields 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 record data that is entirely repeated and does not contain individual fields with the same data. Let's assume that a query has been created that joins the "Customers" and "Orders" tables by the "CustomerCode" field. The Customers table does not contain duplicate CustomerID 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 placed at least one order without mentioning the details of those orders. SELECT DISTINCTROW Name FROM Customers INNER JOIN Orders ON Clients. CustomerCode = Orders. Client code ORDER BY Title; If the DISTINCTROW component is omitted, the query returns multiple rows for each organization that ordered more than once. The DISTINCTROW component only works 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 a specified number of records that are among the first or last records of the range specified by the ORDER BY clause. Suppose you want to display the names of the top 25 students in the 1994 class. FirstName, LastName WHERE GraduationYear = 2003 ORDER BY GradePointAverage DESC; If you do not include an ORDER BY clause, the query will return a random set of 25 records from the Students table that satisfies the WHERE clause. The TOP predicate does not require a choice between equal values. If the 25th and 26th records of the previous example had the same GPA, the query would return 26 records. You can also use the PERCENT reserved word to return some percentage of the first or last entries in the range specified by the ORDER BY clause. Let's assume that instead of the top 25, you want to display the bottom 10% of students in a graduation. 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 main data type used to store integer values. An Integer variable is stored as a 64-bit (8-byte) number in the range -32768 to 32767. ) unsigned. The TOP predicate does not affect the ability to update the query.
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 the SELECT, UPDATE, or DELETE statements.

Syntax

SELECT field_list
FROM table_expression
WHERE selection_conditions

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

Part	Description
field_list	The name of the field or fields retrieved along with any aliases (Alias (SQL). An alternative name for a table or field in an expression. Aliases are usually used as shorter table or field names for ease of later reference in programs, to prevent ambiguous references, and to more descriptive names when displaying query results.), predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or with any other option of the SELECT statement.
table_expression	The name of the table or tables from which the 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 the records included in the query results.

Remarks

The Microsoft Access database engine selects records that meet the conditions listed in the WHERE clause. If no WHERE clause is 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 an SQL SELECT statement that has a FROM clause that references two or more tables and no WHERE or JOIN clause that specifies join method.) tables.

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

If the JOIN clause is not used for a multi-table SQL join operation, the resulting object Recordset it will be impossible to update.

The WHERE clause is similar to the HAVING clause and defines the selected records. After the records are grouped by the GROUP BY clause, the HAVING clause also determines which record to display.

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 is greater than Rs.

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).

When entering a field name with spaces or punctuation marks, enclose it in square brackets (). For example, a customer details table might contain information about specific customers.

SELECT [Customer's favorite restaurant]

By giving an argument selection_conditions, date literals (A date literal. Any sequence of valid format characters enclosed in number signs (#). Valid formats are the date format specified in the language and standards settings and the universal date format.) must be represented in US format, even if non-US format is used. 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 entries for May 10, 1996 in the UK database, use following instruction SQL:

SELECT * FROM Orders WHERE ShipDate = #10.05.1996#;

You can also use the function DateValue, which recognizes international parameters, installed by Microsoft Windows®. For example, for Russia, use the following code:

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

And the following code is for UK:

SELECT * FROM Orders WHERE ShippingDate = 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 for unique identifiers in replication. GUIDs are used to identify replicas, replica sets, tables, records and other objects.In Microsoft Access databases, GUIDs are called replica IDs.), the syntax used in filter conditions is slightly different.

WHERE ReplicaID=(GUID(AB-CDEF0ABCDEF))

Make sure nested brackets and hyphens are placed correctly.

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

GROUP BY offer

Merges records with the same value that are in the specified list of fields into a single record. A summary value is generated for each record if a SQL aggregate function is included in the SELECT statement, for example sum or Count.

Syntax

SELECT field_list
FROM table
WHERE selection_condition

The SELECT statement that contains the GROUP BY clause includes the following elements:

Element	Description
field_list	Names of fields retrieved along with any aliases (Alias (SQL). Alternative name for a table or field in an expression. Aliases are usually used as shorter table or field names for ease of later references 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 options of the SELECT statement
table
selection_conditions	selection condition. If the statement contains a WHERE clause, then after it is applied to records, the values will be grouped by the Microsoft Access database engine.
group_field_list	group_field_list

Remarks

The GROUP BY clause is optional.

If SQL aggregates are not included in the SELECT statement, summary values are not calculated.

GROUP BY field values that are null may contain Null values.) are grouped and not omitted. However, the values Null none of the SQL statistical functions are evaluated.

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 list of GROUP BY fields that do not contain data of type Memo (Data type "MEMO Field". Data type of the field in the Microsoft Access database. A Memo field can contain up to 65535 characters.) or OLE Object (Data type "OLE Object Field". A field data type used to store objects from other applications that are linked to or embedded in a Microsoft Access database.) can refer to 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 aggregate function in the SELECT statement. The Microsoft Access database engine does not allow grouping by fields that contain data of the "MEMO field" or "OLE object" type.

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

see also

SELECT Statement

SELECT...INTO statement

Predicates ALL, DISTINCT, DISTINCTROW, TOP

FROM clause

HAVING offer

ORDER BY offer

WHERE clause

SQL Statistical Functions

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

HAVING offer

Specifies grouped records to be displayed in a SELECT statement with a GROUP BY clause. After the records are grouped by the GROUP BY clause, the HAVING clause will show those records that match its conditions.

Syntax

SELECT field_list
FROM table
WHERE choice_conditions
GROUP BY group_field_list

A SELECT statement that contains a HAVING clause includes the following elements:

Element	Description
field_list	Names of fields loaded with any aliases (Alias (SQL). An alternative name for a table or field in an expression. Aliases are usually used as shorter table or field names for ease of later references in programs, to prevent ambiguous references, and to obtain more meaningful names when displaying query results.) and SQL aggregates, predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are loaded
selection_condition	selection condition. If the statement contains a WHERE clause, then after it is applied to records, the Microsoft Access database engine will group the values.
group_field_list	Field names (maximum 10) used to group records. Name order in group_field_list determines the grouping level - from highest to lowest
group_condition	An expression that specifies which records to display

Remarks

The HAVING clause is optional.

The HAVING clause is similar to the WHERE clause, which specifies the selection of records. After grouping records with the GROUP BY clause, the HAVING clause determines which records to display.

SELECTTypeCode,

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. /en-us/access/HA.aspx? pid=CH

ORDER BY offer

Sorts the records returned by the query in ascending or descending order by 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	Names of fields retrieved along with any aliases (Alias (SQL). Alternative name for a table or field in an expression. Aliases are usually used as shorter table or field names for ease of later references in programs, to prevent ambiguous references, and to obtain more meaningful names when displaying query results.) and SQL aggregates, predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are retrieved
selection_conditions	selection conditions. If the statement contains a WHERE clause, then after applying it to the records, the Microsoft Access database engine will order the values of the records
field1, field2	The names of the fields by which the records are sorted.

Remarks

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 order data based on its value and type. Data can be sorted alphabetically, by numeric value, or by date. Sort order can be ascending (0 to 100, A to Z) or descending (100 to 0, Z to A).) ascending (A to Z, 0 to 9). The examples below demonstrate sorting employee names by last name.

SELECT Last name, First name

FROM Employees

ORDER BY Surname;

SELECT Last name, First name

FROM Employees

ORDER BY Surname ASC;

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

SELECT Surname, Salary

FROM Employees

ORDER BY Salary DESC, Surname;

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

The ORDER BY clause is usually the last clause in 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/rows are commonly used in queries and statistical functions.).

Additional fields can be included in the ORDER BY clause. Records are first sorted by the field listed first in the ORDER BY clause. The records with the same value in the first field are then sorted by the field specified in the second field, and so on.
see also

SELECT Statement

SELECT...INTO statement

Predicates ALL, DISTINCT, DISTINCTROW, TOP

FROM clause

GROUP BY offer

HAVING offer

WHERE clause

SQL Statistical Functions

Source page: http://office. /en-us/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 tables containing joined records
field1, field2	Linked field names. Fields that are not numeric must be of the same data type (Data type. A field characteristic that determines the type of data that this field can contain. The following data types are available: Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and contain data of the same kind. However, the names of these fields can be different.
comparison_operator	Any comparison operator: (=,<, >, <=, >= or<>)

Description of the educational project "Shop"

Table link scheme

Description of tables

m_category - product categories

m_income - receipt of goods

m_outcome - consumption of goods

m_product - directory, product description

m_supplier - directory; supplier information

m_unit - directory; units

To practically test the examples given in this tutorial, you need to have the following software available:

Microsoft Access 2003 or later.

SQL query in MS Access. Start

To see the contents of a table, double-click on the table name in the left pane:

To switch to the table field editing mode, select Design mode on the top panel:

To display the result of an SQL query, double click on the query name in the left pane:

To switch to the SQL query editing mode, select the SQL mode on the top panel:

SQL query. Examples in MS Access. SELECT: 1-10

In an SQL query, the SELECT statement is used to select from database tables.

SQL query Q001. An example SQL query to get only the required fields in the desired sequence:

SELECT dt, product_id, amount

FROM m_income;

SQL query Q002. In this SQL query example, the asterisk character (*) is used to display all the columns of the m_product table, in other words, to get all the fields of the m_product relation:

SELECT *
FROM m_product;

InquirySQLQ003. The DISTINCT statement is used to eliminate duplicate records and get many unique records:

SELECT DISTINCT product_id

FROM m_income;

SQL query Q004. The ORDER BY statement is used to sort (order) records by the values of a particular field. The field name follows the ORDER BY clause:

SELECT *
FROM m_income

ORDER BY price;

SQL query Q005. The ASC statement is used in addition to the ORDER BY statement and is used to define an ascending sort. The DESC statement is used in addition to the ORDER BY statement and is used to define a descending sort. In the case when neither ASC nor DESC are specified, the presence of ASC (default) is assumed:

SELECT *
FROM m_income

ORDER BY dt DESC , price;

SQL query Q006. To select the necessary records from the table, various logical expressions are used that express the selection condition. The boolean expression comes after the WHERE clause. An example of getting from the m_income table all records for which the amount value is greater than 200:

SELECT *
FROM m_income

WHERE amount>200;

SQL query Q007. To express complex conditions, the logical operations AND (conjunction), OR (disjunction) and NOT (logical negation) are used. An example of getting from the m_outcome table all records for which the amount value is 20 and the price value is greater than or equal to 10:

price

FROM m_outcome
WHERE amount=20 AND price>=10;

SQL query Q008. To join data from two or more tables, use the INNER JOIN, LEFT JOIN, RIGHT JOIN statements. The following example retrieves the dt, product_id, amount, price fields from the m_income table and the title field from the m_product table. The record of the m_income table is connected to the record of the m_product table when the value of m_income.product_id is equal to the value of m_product.id:

ON m_income.product_id=m_product.id;

SQL query Q009. There are two things you need to pay attention to in this SQL query: 1) the search text is enclosed in single quotes ("); 2) the date is given in the format #Month/Day/Year#, which is correct for MS Access. In other systems, the date format may be different.An example of displaying information about the receipt of milk on June 12, 2011. Pay attention to the date format #6/12/2011#:

SELECT dt, product_id, title, amount, price

FROM m_income INNER JOIN m_product

WHERE title="(!LANG:Milk" And dt=#6/12/2011#; !}

SQL query Q010. The BETWEEN instruction is used to test whether a range of values belongs to it. An example SQL query displaying information about goods received between June 1st and June 30th, 2011:

SELECT *
FROM m_income INNER JOIN m_product

ON m_income.product_id=m_product.id
WHERE dt BETWEEN #6/1/2011# And #6/30/2011#;

SQL query. Examples in MS Access. SELECT: 11-20

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 a WHERE clause. But there are other ways to use subqueries.

Request Q011. Displays information about products from the m_product table, the codes of which are also in the m_income table:

SELECT *
FROM m_product

WHERE id IN (SELECT product_id FROM m_income);

Request 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 returns a unique list of codes and product names that have codes 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);

Request Q014. A unique list of categories is displayed from the m_category table, the names of which begin with the letter M:

SELECT DISTINCT title

FROM m_product
WHERE title LIKE "M*";

Request 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 consumption record, assuming profit is 7 percent of sales:

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

Request Q016. By analyzing and simplifying 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. Using the INNER JOIN statement, you can combine 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. Functions such as SUM - sum, COUNT - quantity, AVG - arithmetic mean, MAX - maximum value, MIN - minimum value are called aggregate functions. They take multiple values and return a single value when processed. An example of calculating the sum of the product of the fields amount and price using aggregate function SUM:

SELECT SUM(amount*price) AS Total_Sum

FROM m_income;

Request Q019. An example of using multiple aggregate functions:

SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,

MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
FROM m_income;

Request Q020. In this example, the sum of all items with code 1 received in June 2011 is calculated:

SELECT Sum(amount*price) AS income_sum

FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.

Request Q021. The following SQL query calculates how much the goods with code 4 or 6 were sold for:

SELECT Sum(amount*price) as outcome_sum

FROM m_outcome
WHERE product_id=4 OR product_id=6;

Request Q022. It is calculated for what amount was sold on June 12, 2011 of goods with code 4 or 6:

SELECT Sum(amount*price) AS outcome_sum

FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;

Request Q023. The task is this. Calculate the total amount for which the goods of the category "Baked products" were credited.

To solve this problem, you need to operate on three tables: m_income, m_product and m_category, because:

- the quantity and price of credited goods are stored in the m_income table;
- the category code of each product is stored in the m_product table;
- the name of the category title is stored in the m_category table.

To solve this problem, we use the following algorithm:

- determination of the category code "Baked products" from the table m_category by means of a subquery;
- joining the m_income and m_product tables to determine the category of each credited product;
- calculation of the receipt amount (= quantity * price) for goods, the category code of which is equal to the code defined by the above subquery.
SELECT
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="(!LANG:Baked goods"); !}

Request Q024. The problem of calculating the total amount of credited goods of the category "Baked products" will be solved by the following algorithm:
- each record of the m_income table, depending on the value of its product_id, from the m_category table, match the name of the category;
- select records for which the category is equal to "Baked products";
- calculate the amount of income = quantity * price.

FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)

WHERE c.title="(!LANG:Baked goods"; !}

Request Q025. This example calculates how many items were consumed:

SELECT COUNT(product_id) AS product_cnt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Request Q026. The GROUP BY clause is used to group records. Typically, records are grouped by the value of one or more fields, and an aggregate operation is applied to each group. For example, the following query generates a report on the sale of goods. That is, a table is generated that will contain the names of the goods and the amount for which they are sold:

SELECT title, SUM(amount*price) AS outcome_sum

FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY title;

Request Q027. Sales report by category. That is, a table is generated that will contain the names of product categories, the total amount for which the goods of these categories are sold, and the average amount of sales. The ROUND function is used to round the mean value to the nearest hundredth (the second decimal place after the decimal separator):

SELECT c.title, SUM(amount*price) AS outcome_sum,

ROUND(AVG(amount*price),2) AS outcome_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;

Request Q028. For each product, the total and average number of its receipts is calculated and displays information about the goods, the total number of receipts of which is at least 500:

SELECT product_id, SUM(amount) AS amount_sum,

Round(Avg(amount),2) AS amount_avg
FROM m_income
GROUP BY product_id
HAVING Sum(amount)>=500;

Request Q029. This query computes for each item the sum and average of its receipts made in the second quarter of 2011. If the total amount of receipt of goods is not less than 1000, then information about this product is displayed:

SELECT title, SUM(amount*price) AS income_sum

FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt BETWEEN #4/1/2011# AND #6/30/2011#
GROUP BY title
HAVING SUM(amount*price)>=1000;

Request Q030. In some cases it is necessary to match each record of some table with each record of another table; what is called a Cartesian product. The table resulting from such a join is called a Descartes table. For example, if some table A has 100 entries and table B has 15 entries, then their Cartesian table will consist of 100*15=150 entries. The following query joins each entry in the m_income table with each entry in the m_outcome table:
FROM m_income, m_outcome;

Request Q031. An example of grouping records by two fields. The following SQL query calculates for each supplier the amount and quantity of goods received from him:

SUM(amount*price) AS income_sum

Request Q032. An example of grouping records by two fields. The following query calculates, for each supplier, the amount and quantity of their products sold by us:

SELECT supplier_id, product_id, SUM(amount) AS amount_sum,

GROUP BY supplier_id, product_id;

Request Q033. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries are merged into one report using the LEFT JOIN method. The following query displays a report on the number and amount of products received and sold for each supplier. You should pay attention to the fact that if some product has already arrived, but has not yet been sold, then the outcome_sum cell for this record will be empty. that this query is only an example of using relatively complex queries as a subquery. Performance given request SQL with a large amount of data is questionable:

SELECT *
FROM

SUM(amount*price) AS income_sum

ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LEFT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Request Q034. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries are combined into one report using the RIGTH JOIN method. The following query prints out a report on the amount of each customer's payments for the payment systems they used and the amount of investments they made. The following query displays a report on the number and amount of products received and sold for each supplier. Note that if a product has already been sold but not yet received, then the income_sum cell for this entry will be empty. The presence of such empty cells is an indicator of an error in accounting for sales, since before the sale, it is first necessary that the corresponding product arrives:

SELECT *
FROM

(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Request Q035. A report on the amount of income and expenses by product is displayed. To do this, a list of products is created according to the m_income and m_outcome tables, then for each product from this list, the sum of its income from the m_income table and the sum of its expenses from the m_outcome table are calculated:

SELECT product_id, SUM(in_amount) AS income_amount,

SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Request Q036. The EXISTS function returns TRUE if the set passed to it contains elements. The EXISTS function returns FALSE if the set passed to it is empty, that is, it has no elements. The following query returns the product codes that are contained in both the m_income table and the m_outcome table:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Request Q037. Product codes are displayed that are contained in both the m_income table and the m_outcome table:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Request Q038. Product codes are displayed that are contained as in the m_income table, but are not contained in the m_outcome table:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Request Q039. A list of products with the highest sales amount is displayed. The algorithm is this. For each product, the sum of its sales is calculated. Then, the maximum of these sums is determined. Then, for each product, the sum of its sales is again calculated, and the code and the sum of sales of goods are displayed, the sum of sales of which is equal to the maximum:

SELECT product_id, SUM(amount*price) AS amount_sum

FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));

Request Q040. The reserved word IIF (conditional operator) is used to evaluate a logical expression and perform an action depending on the result (TRUE or FALSE). In the following example, the delivery of an item is considered "small" if the quantity is less than 500. Otherwise, that is, the receipt quantity is greater than or equal to 500, the delivery is considered "large":

SELECT dt, product_id, amount,

IIF(amount FROM m_income;

SQL query Q041. In the case where the IIF statement is used more than once, it is more convenient to replace it with the SWITCH statement. The SWITCH operator (multiple choice operator) is used to evaluate a logical expression and perform an action depending on the result. In the following example, the delivered lot is considered "small" if the quantity of goods in the lot is less than 500. Otherwise, that is, if the quantity of goods is greater than or equal to 500, the lot is considered "large":

SELECT dt, product_id, amount,

SWITCH(amount =500,"big") AS mark
FROM m_income;

Request Q042. In the next query, if the quantity of goods in the incoming batch is less than 300, then the batch is considered "small". Otherwise, i.e. if the condition amount SELECT dt, product_id, amount,
IIF(amount IIF(amount FROM m_income;

SQL query Q043. In the next query, if the quantity of goods in the incoming batch is less than 300, then the batch is considered "small". Otherwise, i.e. if the condition amount SELECT dt, product_id, amount,
SWITCH(amount amount amount>=1000,"big") AS mark
FROM m_income;

SQL query Q044. In the following query, sales are divided into three groups: small (up to 150), medium (from 150 to 300), large (300 and more). Next, for each group, the total amount is calculated:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total

FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price IIf(amount*price FROM m_outcome) AS t
GROUP BY Category;

SQL query Q045. The DateAdd function is used to add days, months, or years to a given date and get a new date. Next request:
1) adds 30 days to the date from the dt field and displays the new date in the dt_plus_30d field;
2) add 1 month to the date from the dt field and display the new date in the dt_plus_1m field:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m

FROM m_income;

SQL query Q046. The DateDiff function is designed to calculate the difference between two dates in different units (days, months, or years). The following query calculates the difference between the date in the dt field and the current date in days , months and years:

SELECT dt, DateDiff("d",dt,Date()) AS last_day,

DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) AS last_years
FROM m_income;

SQL query Q047. The number of days from the date of receipt of goods (table m_income) to current date using the DateDiff function and the expiration date is compared (table m_product):

DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id;

SQL query Q048. The number of days from the date of receipt of the goods to the current date is calculated, then it is checked whether this number exceeds the expiration date:

SELECT a.id, product_id, dt, lifedays,

DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

SQL query Q049. The number of months from the date of receipt of the goods to the current date is calculated. The month_last1 column calculates the absolute number of months, the month_last2 column calculates the number of complete months:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1,

DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;

SQL query Q050. A quarterly report on the quantity and amount of goods received for 2011 is displayed:

SELECT kvartal, SUM(outcome_sum) AS Total

FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m =10.4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY block;

Request Q051. The following query helps to find out if users managed to enter into the system information about the consumption of goods for an amount greater than the amount of receipt of the goods:

SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum

FROM (SELECT product_id, amount*price as in_sum, 0 as out_sum
from m_income
UNION ALL
SELECT product_id, 0 as in_sum, amount*price as out_sum
from m_outcome) AS t
GROUP BY product_id
HAVING SUM(in_sum)
Request Q052. The numbering of the lines returned by the query is implemented in different ways. For example, you can renumber the lines of a report prepared in MS Access using MS Access itself. You can also renumber using programming languages, for example, VBA or PHP. However, sometimes it needs to be done in the SQL query itself. So, the following query will number the rows of the m_income table in accordance with the ascending order of the values of the ID field:

SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price

FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;

Request Q053. The top five among the products by the amount of sales are displayed. The output of the first five records is carried out using the TOP instruction:

SELECT TOP 5, product_id, sum(amount*price) AS summa

FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC;

Request Q054. The top five among the products by the amount of sales are displayed, and the lines are numbered as a result:

SELECT COUNT(*) AS N, b.product_id, b.summa

FROM

FROM m_outcome GROUP BY product_id) AS a
INNER JOIN
(SELECT product_id, sum(amount*price) AS summa,
summa*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)ORDER BY COUNT(*);

Request Q055. The following SQL query shows the use of the mathematical functions COS, SIN, TAN, SQRT, ^ and ABS in MS Access SQL:

SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k,

2*pi*(k-1)/N as x, COS(x) as COS_, SIN(x) as SIN_, TAN(x) as TAN_,
SQR(x) as SQRT_, x^3 as "x^3", ABS(x) as ABS_
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;

SQL query. Examples in MS Access. UPDATE: 1-10

Request U001. The following SQL change query increases the prices of items with code 3 in the m_income table by 10%:

UPDATE m_income SET price = price*1.1

WHERE product_id=3;

Request U002. The following SQL update query increases the quantity of all products in the m_income table by 22 units whose names begin with the word "Butter":

UPDATE m_income SET amount = amount+22

WHERE product_id IN (SELECT id FROM m_product WHERE title LIKE "Oil*");

Request U003. The following SQL change query in the m_outcome table reduces the prices of all goods produced by OOO Sladkoe by 2 percent:

UPDATE m_outcome SET price = price*0.98

WHERE product_id IN
(SELECT a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="(!LANG:OOO"Сладкое"");. !}

SQL - Lesson 4. Selecting data - SELECT statement

So, in our forum database there are three tables: users (users), topics (topics) and posts (messages). And we want to see what data they contain. To do this, there is an operator in SQL SELECT. The syntax for using it is as follows:

SELECT select_what FROM select_from;

Instead of "what_to_choose", we must specify either the name of the column whose values we want to see, or the names of several columns separated by commas, or the asterisk character (*), meaning the selection of all columns of the table. Instead of "from_choose" you should specify the name of the table.

Let's first look at all the columns from the users table:

SELECT * FROM users;

That's all our data that we entered into this table. But suppose we only want to look at the id_user column (for example, in the last lesson, we needed to know what id_users are in the users table to populate the topics table). To do this, we will specify the name of this column in the query:

SELECT id_user FROM users;

Well, if we want to see, for example, the names and e-mails of our users, then we will list the columns of interest separated by commas:

SELECT name, email FROM users;

Similarly, you can see what data our other tables contain. Let's see what topics we have:

SELECT * FROM topics;

Now we have only 4 topics, and if there are 100 of them? I would like them to be displayed, for example, alphabetically. There is a keyword for this in SQL. ORDER BY followed by the name of the column by which sorting will occur. The syntax is the following:

SELECT column_name FROM table_name ORDER BY sort_column_name;

The default sorting is ascending, but this can be changed by adding the keyword DESC

Now our data is sorted in descending order.

You can sort by several columns at once. For example, the following query will sort data by the topic_name column, and if there are several identical rows in this column, then the id_author column will be sorted in descending order:

Compare the result with the result of the previous query.

Very often we do not need all the information from the table. For example, we want to know which topics were created by the user sveta (id=4). There is a keyword for this in SQL. WHERE, the syntax for such a request is as follows:

For our example, the condition is the user ID, i.e. we only want rows that have 4 in the id_author column (user ID sveta):

Or we want to know who created the "bicycles" theme:

Of course, it would be more convenient to display the author's name instead of the author's id, but the names are stored in another table. In later lessons, we will learn how to select data from multiple tables. In the meantime, let's learn what conditions can be specified using the WHERE keyword.

Operator	Description
= (equal)	Selected values equal to the specified Example: SELECT * FROM topics WHERE id_author=4; Result:
> (more)	Values greater than the specified are selected Example: SELECT * FROM topics WHERE id_author>2; Result:
< (меньше)	Values less than specified are selected Example: SELECT * FROM topics WHERE id_author Result:
>= (greater than or equal to)	Values greater than or equal to the specified value are selected. Example: SELECT * FROM topics WHERE id_author>=2; Result:
<= (меньше или равно)	Values less than or equal to the specified value are selected. Example: SELECT * FROM topics WHERE id_author Result:
!= (not equal)	Values not equal to the specified are selected Example: SELECT * FROM topics WHERE id_author!=1; Result:
IS NOT NULL	Rows that have values in the specified field are selected Example: SELECT * FROM topics WHERE id_author IS NOT NULL; Result:
IS NULL	Rows that do not have a value in the specified field are selected Example: SELECT * FROM topics WHERE id_author IS NULL; Result: Empty set - no such strings.
BETWEEN (between)	Values between the specified values are selected. Example: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Result:
IN (value contained)	The values corresponding to the specified Example: SELECT * FROM topics WHERE id_author IN (1, 4); Result:
NOT IN (value not contained)	Selected values other than those specified Example: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Result:
LIKE (match)	Sample values are selected Example: SELECT * FROM topics WHERE topic_name LIKE "vel%"; Result: Possible metacharacters of the LIKE operator will be discussed below.
NOT LIKE	Values that do not match the sample are selected Example: SELECT * FROM topics WHERE topic_name NOT LIKE "vel%"; Result:

LIKE operator metacharacters

Metacharacter searches can only be performed in text fields.

The most common metacharacter is % . It means any characters. For example, if we want to find words that start with the letters "vel", then we will write LIKE "vel%", and if we want to find words that contain the characters "club", then we will write LIKE "% club%". For instance:

Another commonly used metacharacter is _ . Unlike %, which denotes few or no characters, underscore denotes exactly one character. For instance:

Pay attention to the space between the metacharacter and "fish", if you skip it, the request will not work, because metacharacter _ stands for exactly one character, and a space is also a character.

It's enough for today. In the next lesson, we will learn how to query two or more tables. In the meantime, try to make your own queries against the posts table (messages).

SQL query examples 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 a WHERE clause. But there are other ways to use subqueries.

Request Q011. Displays information about products from the m_product table, the codes of which are also in the m_income table:

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

Request 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 returns a unique list of codes and product names that have codes 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);

Request Q014. A unique list of categories is displayed from the m_category table, the names of which begin with the letter M:

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

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

Request Q016. By analyzing and simplifying 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;

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;

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

Those who program VBA Access and while working with a SQL server database, they often face 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 an article about Uploading data to a text file from MSSql 2008 or also touched a little in the material Uploading data from Access to a Word and Excel template. but one way or another there we considered it superficially, and today I propose to talk about it in a little more detail.

Note! All examples below are discussed using an Access 2003 ADP project and an MSSql 2008 database.

Initial data for examples

Let's say we have a table test_table that will contain the numbers and names of the months in a year (queries are made using management studio)

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

As I 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 caption "Run", which we will need to test our code, i.e. we will write all the code in the event handler " Button press».

Database queries INSERT, UPDATE, DELETE in VBA

In order not to drag it out for a long time, let's get started, let's say we need to add a row to our test table ( code is 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 with DoCmd. RunSQL sql_query End Sub

In this case, the query is executed using the current database connection settings. We can check if 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" "Execute it DoCmd.RunSQL sql_query End Sub

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

To update the data, we write an update query to the sql_query variable, I hope the meaning is clear.

SELECT query to 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() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for outputting total data in the message Dim str As String "Creating a new object for records set RS = New ADODB .Recordset "Query string sql_query = "SELECT id, name_mon FROM test_table" "Execute query using current project connection settings to output a message str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "go to next record RS.MoveNext Wend "Output message msgbox str End Sub

Here we are already using VBA Access Loops to loop through all the values in our recordset.

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

Private Sub start_Click() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for displaying the final value Dim str As String "Creating a new object for records set RS = New ADODB.Recordset "Query string sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Execute query using 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 name of the cell, but by its index, i.e. 0, which 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 that you output all the code in a separate function (How to write a function in VBA Access 2003) with one input parameter, for example, the month code ( considering our example) and simply, where it is necessary to display this value, call the function we need with the necessary parameter, and that's it, we will significantly reduce the VBA code and improve the perception of our program.

That's all for today. Good luck!