Inserting, deleting, updating records in the database. How to send a query to a database using VBA Access Creating sql queries in access namesakes
Laboratory work No. 1
SQL: DATA EXTRACT - commandSELECT
Goal of the work:
- become familiar with SQL statements;
- learn how to create simple SQL queries in Access using the SELECT command;
· use of operators IN, BETWEEN, LIKE, IS NULL.
Exercise№1. Create a query to select in SQL mode all values of the FIRST NAME and LAST NAME fields from the STUDENTS table.
SELECT FIRST NAME, LAST NAME
FROM STUDENTS;
Exercise№2 . Create a query to select in SQL mode all columns of the STUDENTS table.
SELECT *
FROM STUDENTS;
Task No. 3. Create a query to select in SQL mode the names of cities where students live, information about which is in the PERSONAL DATA table.
SELECT DISTINCT CITY
FROM [PERSONAL DATA];
Task No. 4. Create a selection query in SQL mode that retrieves the names of all students with the last name Ivanov, information about which is in the STUDENTS table.
SELECT LAST NAME, FIRST NAME
FROM STUDENTS
WHERE LAST NAME="Ivanov";
Task No. 5. Create a selection query in SQL mode to obtain the first and last names of students studying in the UIT-22 group on a budgetary form of education.
SELECT LAST NAME, FIRST NAME
FROM STUDENTS
WHERE GROUP="UIT-22" AND BUDGET=true;
Task No. 6. Create a query in SQL mode. for a sample from the EXAMINATION table, information about students who have grades only 4 and 5.
SELECT *
FROM [CHANGEEXAMINATIONS]
WHEREGRADEIN(4,5);
Task No. 7. Create a zanpoc and SQL mode to select information about students who have an exam grade of 3 in the subject IOSU.
SELECT *
FROM [CHANGEEXAMINATIONS]
WHEREITEM=" IOSU"AndGRADENot In (4,5);
Task No. 8. Create a query in SQL mode to select records for items whose hours are between 100 and 130.
SELECT *
FROMITEMS
WHEREWATCHBETWEEN 100 AND 130;
Task No. 9. Create a query in SQL mode to select from the STUDENTS table information about students whose last names begin, for example, with the letter “C”.
SELECT *
FROMSTUDENTS
WHERESURNAMELIKE"WITH*";
Conclusion: During laboratory work got acquainted with SQL instructions, learned how to create simple SQL queries in Access using the SELECT command using the IN, BETWEEN, LIKE operators.
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 the database SQL data servers are very often faced with such a simple and necessary task as sending an SQL query to a 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 SQL server, where they wrote code in VBA to obtain this data, for example, in the article about Uploading data into a text file from MSSql 2008 or 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 it 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 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 the update request to the sql_query variable, 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 summary data in a 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!
Description of the educational project "Shop"Table link diagram
Description of tables
m_category - product categories
m_income - goods receipt
m_outcome - consumption of goods
m_product - directory, product descriptions
m_supplier - directory; supplier information
m_unit - directory; units
To practically test the examples given in this training material, you must have the following software:
Microsoft Access 2003 or newer.
SQL Query in MS Access. Start
To see the contents of the table, double-click on the table name in the left panel:
To switch to table field editing mode, click top panel select Design mode:
To display the result of an SQL query, double-click the query name in the left pane:
To switch to SQL query editing mode, select SQL mode in 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. 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 example SQL query, the asterisk (*) character is used to list all columns of the m_product table, in other words, to get all the fields of the m_product relation:
SELECT *
FROM m_product;
RequestSQL Q003. The DISTINCT statement is used to eliminate duplicate entries and obtain multiple unique entries:
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 specific field. The field name is specified after the ORDER BY statement:
SELECT *
FROM m_income
ORDER BY price;
SQL Query Q005. The ASC statement is used as a complement to the ORDER BY statement and serves to specify ascending sorting. The DESC statement is used in addition to the ORDER BY statement and is used to specify descending sorting. In the case where 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 appears after the WHERE statement. An example of getting all records from the m_income table for which the amount value is greater than 200:
SELECT *
FROM m_income
WHERE amount>200;
SQL Query Q007. For expression difficult conditions use the logical operators AND (conjunction), OR (disjunction) and NOT (logical negation). 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 instructions. 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 m_income table record is joined to the m_product table record 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 to note in this SQL query: 1) the text you are looking for is enclosed in single quotes("); 2) the date is given in the format #Month/Day/Year#, which is true for MS Access. In other systems, the format for writing the date may be different. An example of displaying information about the receipt of milk on June 12, 2011. Please note date format #6/12/2011#:
SELECT dt, product_id, title, amount, price
FROM m_income INNER JOIN m_product
WHERE title="Milk" And dt=#6/12/2011#; !}
SQL Query Q010. The BETWEEN instruction is used to test whether a value belongs to a certain range. An example SQL query that displays information about products 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 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);
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 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. 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. You can use the INNER JOIN statement to join data from multiple tables. In the following example, depending on the ctgry_id value, 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 average, MAX - maximum value, MIN - minimum value are 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 SUM aggregate function:
SELECT SUM(amount*price) AS Total_Sum
FROM m_income;
Query Q019. An example of using several 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 amount of all goods with code 1, capitalized 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#;.
Query Q021. The following SQL query calculates the amount of sales of items with code 4 or 6:
SELECT Sum(amount*price) as outcome_sum
FROM m_outcome
WHERE product_id=4 OR product_id=6;
Query Q022. It is calculated how much goods with code 4 or 6 were sold on June 12, 2011:
SELECT Sum(amount*price) AS outcome_sum
FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;
Query Q023. The task is this. Calculate the total amount of goods in the “Bakery Products” category that were capitalized.
To solve this problem, you need to operate with three tables: m_income, m_product and m_category, because:
- the quantity and price of capitalized 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 title category is stored in the m_category table.
To solve this problem we will use the following algorithm:
- determining the category code "Bakery products" from the m_category table using a subquery;
- connecting the m_income and m_product tables to determine the category of each purchased product;
- calculation of the receipt amount (= quantity*price) for goods whose category code 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="Bakery products"); !}
Query Q024. We will solve the problem of calculating the total amount of capitalized goods in the “Bakery Products” category using the following algorithm:
- for each entry in 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 “Bakery Products”;
- calculate the amount of receipt = quantity*price.
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Bakery products";
!}
Query Q025. This example calculates how many items of goods were consumed:
SELECT COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;
Query Q026. The GROUP BY statement is used to group records. Typically, records are grouped by the value of one or more fields, and some 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 containing the names of the goods and the amount for which they were 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 contains the names of product categories, the total amount for which products of these categories were sold, and the average sales amount. The ROUND function is used to round the average value to the nearest hundredth (the second digit 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;
Query Q028. The total and average number of its receipts is calculated for each product and displays information about products whose total receipts are 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;
Query Q029. This query calculates for each product the amount and average of its receipts made in the second quarter of 2011. If the total amount of the product receipt is at least 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;
Query Q030. In some cases, you need to match each record of some table with each record of another table; which is called the Cartesian product. The table resulting from such a connection is called Descartes' table. For example, if some table A has 100 records and table B has 15 records, then their Cartesian table will consist of 100*15=150 records. The following query joins each record in the m_income table with each record in the m_outcome table:
FROM m_income, m_outcome;
Query 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
Query 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;
Query Q033. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries using the LEFT JOIN method are combined into one report. The following query displays a report on the quantity and amount of products received and sold for each supplier. Please note that if some product has already been received, but has not yet been sold, then the outcome_sum cell for this entry will be empty. , that this request serves only as an example of use regarding complex queries as a subquery. The performance of this SQL query 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);
Query Q034. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries using the RIGTH JOIN method are combined into one report. The following query displays a report on the amount of payments of each client according to the payment systems he used and the amount of investments he made. The following query displays a report on the quantity and amount of products received and sold for each supplier. Please note that if some product has already been sold, but has not yet arrived, then the income_sum cell for this entry will be empty. The presence of such empty cells is an indicator of an error in sales accounting, since before a sale it is first necessary for the corresponding product to arrive:
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);
Query Q035. A report is displayed showing the amount of income and expenses by product. 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 is calculated according to the m_income table and the amount of its expenses according to the m_outcome table:
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;
Query 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 contains no elements. The following query displays the product codes that are contained in both the m_income and m_outcome tables:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
Query Q037. Product codes that are contained in both the m_income and m_outcome tables are displayed:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)
Query Q038. Product codes are displayed that are contained 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);
Query Q039. A list of products with the maximum sales amount is displayed. The algorithm is as follows. For each product, the amount of its sales is calculated. Then, the maximum of these amounts is determined. Then, for each product, the sum of its sales is calculated again, and the code and the sales sum of goods whose sales sum is equal to the maximum are displayed:
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));
Query Q040. Reserved word IIF ( conditional operator) is used to evaluate a Boolean expression and perform an action depending on the result (TRUE or FALSE). In the following example, the item delivery 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 operator is used several times, it is more convenient to replace it with the SWITCH operator. The SWITCH operator (multiple selection 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,"large") AS mark
FROM m_income;
Query Q042. In the next request, if the quantity of goods in the received batch is less than 300, then the batch is considered “small”. Otherwise, that is, if the condition amount SELECT dt, product_id, amount,
IIF(amount IIF(amount FROM m_income;
SQL Query Q043. In the next request, if the quantity of goods in the received batch is less than 300, then the batch is considered “small”. Otherwise, that is, if the condition amount SELECT dt, product_id, amount,
SWITCH(amount amount amount>=1000,"large") 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 or more). Next, the total amount is calculated for each group:
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 obtain 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) adds 1 month to the date from the dt field and displays 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 the goods (table m_income) to the current date is calculated 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 quantity 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. Column month_last1 calculates the absolute number of months, column month_last2 calculates the number of full 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 is displayed on the quantity and amount of goods purchased for 2011:
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 quarter;
Query Q051. The following query helps to find out whether users were able to enter into the system information about the consumption of goods in an amount greater than the amount of goods received:
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)
Query Q052. The numbering of rows returned by a 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 this needs to be done in the SQL query itself. So, the following query will number the rows of the m_income table according to the ascending order of the ID field values:
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;
Query Q053. The top five products among the products by sales amount are displayed. The first five records are printed 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;
Query Q054. The top five products among products by sales amount are displayed, and the rows 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(*);
Query 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 goods 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 “Oil”:
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 query for a change in the m_outcome table reduces the prices of all goods manufactured by Sladkoe LLC 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="LLC"Сладкое"");. !}
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 object teams. 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. Hence, 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 own 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 various situations occur (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 Removal existing record
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 order!", ex); throw error; ) )
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 way to maximize flexibility is to define 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 designation).
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:
DbTypeGets or sets the data type from a parameter, represented as a CLR type
DirectionReturns or sets the type of parameter: input-only, output-only, input and output, or parameter to return a value
IsNullableReturns or sets whether a parameter can accept empty values
ParameterNameGets or sets the DbParameter name
SizeIssues or installs maximum size data for the parameter (useful for text data only)
ValueReturns 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.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.
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.
Rules square brackets stand for [optional part] of a construct. 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 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 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 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 a reserved word (Reserved word. A word that is an element of a language, such as Visual Basic. Reserved words include names of statements, 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 the INNER JOIN, LEFT JOIN, or RIGHT JOIN operators |
external_database | Full path to an 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. 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 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 the Universal Date Format.) must be represented in US format, even if non-US date 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 records for May 10, 1996 in the UK database, use the following SQL statement:
SELECT * FROM Orders WHERE Shipment Date = #10.05.1996#;
You can also use the function DateValue, recognizing international parameters, installed by 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, the Null keyword specifies a Null value. Some fields, such as primary key fields, do not may contain Null values.), are grouped and are 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 (Field data type OLE object" 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 a GROUP BY clause, the HAVING clause determines which records are 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 " 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 typically 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 field characteristic that defines the type of data that the field can contain. Data types include: 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<>) |
