Using sql in access. SQL query language. Basic SQL Commands for Changing Data

Previous articles discussed the issues. The technology for creating the structure of database tables “sql_training_st.mdb” based on SQL queries is considered. In addition, using SQL queries, the ACCESS DBMS tables "sql_training_st.mdb" were filled.

It is known that in relational databases, the SQL language is designed to manipulate data, define the structure of the database and its components, control user access to the database, and manage transactions or manage changes in the database.

The SQL language consists of four groups:

• data manipulation language DML;
• DDL Data Definition Language;
• data management language DCL;
• TCL transaction control language.

The DML group includes four main types SQL queries:

• INSERT - designed to add one or more records to the end of the table;
• UPDATE - intended to change already existing records in table columns or modifications of data in the table;
• DELETE - designed to delete records from the table;
• SELECT - designed to select data from tables.

The first three types of SQL queries (INSERT, UPDATE, DELETE), which relate to corrective queries to the database, were discussed on the page

In this article we will look at queries for retrieving data from Access database tables.

To retrieve information stored in an Access 2003 or 2007 database, you can use a SELECT query to select data from tables.

Let's compose the following SQL query (SQL statement) for sampling; to do this, select the SQL mode by executing the View/SQL Mode command. Enter the following SQL statement from the keyboard:

SELECT *
FROM Students;

This statement consists of two clauses "SELECT *" and "FROM Students". The first clause contains a SELECT statement and an identifier * ("identifier *" means listing all columns of the table). The second clause contains the FROM statement and the identifier "Students".

FROM - Defines the Students table, which contains the fields specified in the SELECT clause. It should be noted that a selection query always contains two statements: SELECT and FROM. Depending on the selection conditions, other operators may be present in the selection request. Figure 1 shows a screenshot of a request to retrieve data.

Rice. 1. SQL SELECT query to select data

In this example, a data sample is generated from all columns of the Students table.

Save the request with the name "Students-query1". As a result of executing the “Save” command, an object will appear in the “Navigation Area” - “Requests: Students-request1”.

After saving the selection request, you must execute this request by clicking on the “Run” icon. The results of the “Run” command are shown in Fig. 2.

Rice. 2. Selecting data from all columns of the table Students

The SQL language does not have the functions of a full-fledged development language, but is focused on data access, so it is included in program development tools. In this case it is called embedded SQL. The SQL language standard is supported by modern implementations of the following programming languages: PL/1, Ada, C, COBOL, Fortran, MUMPS and Pascal.

In specialized application development systems of the client-server type, the programming environment is, in addition, usually supplemented with communication tools (establishing and disconnecting connections with database servers, detecting and processing errors occurring on the network, etc.), development tools user interfaces, design and debugging tools.

There are two main methods for using embedded SQL: static and dynamic.

At static language use (static SQL) The program text contains calls to SQL language functions, which are rigidly included in the executable module after compilation. Changes in called functions can be at the level of individual call parameters using programming language variables.

At dynamic language use (dynamic SQL) it is assumed that calls to SQL functions are dynamically constructed and interpretation of these calls, for example, accessing data from a remote database, during program execution. The dynamic method is usually used in cases where the application does not know the type of SQL call in advance and it is built in dialogue with the user.

The main purpose of the SQL language (as well as other languages ​​for working with databases) is to prepare and execute queries. As a result of sampling data from one or more tables, a set of records can be obtained, called presentation.

Performance is essentially a table formed as a result of executing a query. We can say that it is a type of stored query. You can build multiple views using the same tables. The view itself is described by specifying the view identifier and the request that must be made to obtain it.

For the convenience of working with views, the concept of a cursor was introduced into the SQL language. Cursor is a kind of pointer used to move through sets of records when processing them.

The description and use of a cursor in SQL is as follows. In the descriptive part of the program, a variable of type cursor (CURSOR) is associated with SQL statement(usually with SELECT statement). In the executing part of the program, the cursor is opened (OPEN<имя курсора», перемещение курсора по записям (FETCI-1 <имя курсора>...), followed by appropriate processing, and finally closing the cursor (CLOSE<имя курсора>).

In relational DBMSs, to perform operations on relations, two groups of languages ​​are used, which have as their mathematical basis theoretical query languages ​​proposed by E. Codd:

Relational algebra;

Relational calculus.

In relational algebra The operands and results of all actions are relations. Relational algebra languages ​​are procedural because the relation resulting from a query on a relational database is evaluated by executing a sequence of relational operators applied to the relations. Operators consist of operands, which are relations, and relational operations.

Codd relational algebra operations can be divided into two groups: basic set-theoretic and special relational. The first group of operations includes the classical operations of set theory: union, difference, intersection and product. The second group represents the development of ordinary set-theoretic operations towards real problems of data manipulation; it includes the operations: projection, selection, division and connection.

Calculus languages are non-procedural (descriptive or declarative) and allow queries to be expressed using a first-order predicate (function statements) that tuples or relational domains must satisfy. A database query executed using such a language contains only information about the desired result. These languages ​​are characterized by the presence of sets of rules for writing queries. In particular, SQL belongs to the languages ​​of this group.

Features of application SQL queries

An SQL query is a query created using an SQL statement. Examples of SQL queries are join queries, server queries, control queries, and subordinate queries.

A join query is a query that combines fields (columns) from one or more tables or queries into a single field or column in the result set of records. For example, six salespeople provide management with an inventory of their inventory every month. By creating a join query, you can combine these inventories into a result set of records, and then develop a create table query based on the join query.

A request to the server transmits SQL commands via ODBC to a server, for example, Microsoft SQL Server. Server queries allow you to directly work with tables on the server instead of joining them. The result of a request to the server can be loading records or changing data.

A control query creates or modifies database objects, such as Access or SQL Server tables.

A subquery consists of an SQL SELECT statement inside another select or change query. These instructions are entered in the “Field” line of the request form to define a new field or in the “Selection Condition” line to define the field selection condition. Subqueries are used to do the following:

Checking in a subquery for the existence of some results using the reserved words EXISTS or NO EXISTS;

Searches the main query for any values ​​that are equal to, greater than, or less than the values ​​returned in the subquery (using the ANY, IN, or ALL reserved words);

Creating subqueries within subqueries (nested subqueries).

The SQL language in Access can be used to develop screen forms, reports, as well as to create macros and VBA programs.

Relationship between QBE and SQL languages

In Access, there is a close relationship between the QBE and SQL languages. Query tables (forms) in the QBE language, filled out by the user, are converted into SQL expressions before immediate execution. That is, the SQL language is an internal standard for executing queries. This mechanism has the advantage of allowing the Access system to unify the preparation of queries for execution on local and remote computers. In the latter case, the SQL message is actually transmitted to the computer - the request server.

In Access, a query can be in one of three modes (states): Design, SQL, and Table. Design mode is used to develop a new query from scratch (without using Wizards or other tools) or to change the layout of an existing query. SQL mode is used to enter or view SQL statements. Table mode is used to work with query results.

SQL in Forms and Reports

The main sources of records in displays and reports are tables and queries. In the second case, the request can be a ready-made database query or one created when developing a form or report.

SQL in macros

Macro commands are part of macros that are used to automate the execution of frequently repeated actions in working with a database. A macro is one or more macro commands with arguments.

Macros are called from the database window or automatically when certain events occur. The event on which the macro is called can be, for example, the click of a button in the form area or the opening of a database window. Along with performing certain actions on database objects, macros can call other macros, Visual Basic programs, and external applications.

Of the many macro commands, two macro commands are directly related to SQL: Run SQL Query (Run SQL) and Open Query (OpenQuery)

Macro command Run SQL Query runs an Access change or control query using the appropriate SQL statement. This macro makes it possible to perform actions in a macro without first saving queries. You can also execute saved queries using the macro.

Change requests are SQL statements that implement the following functions: adding (INSERT INTO), deleting (DELETE), creating a table (SELECT...INTO) and updating (UPDATE)

Control requests are SQL statements that perform the following functions: create a table (CREATE TABLE), modify a table (ALTER TABLE), drop a table (DROP TABLE), create an index (CREATE INDEX), and drop an index (DROP INDEX)

The only and required argument of the macro command Running a SQL Query is a SQL statement. Macro argument as SQL text - instructions are entered manually in the macro input window or copied from the SQL window, which is often more convenient.

Macro command OpenRequest allows you to open a select or cross-query (in table, design, and preview modes), run a query to change or enter data.

The macro specifies three arguments: request name, mode, and data mode. The first argument specifies the name of the request to open and is required. The second argument specifies the query opening mode (Table, Design, and View). The third argument describes the mode of data entry into the request ("Add", "Change" and "Read Only")

SQL in VBA programs

VBA, like macros, is designed to automate repetitive operations on Access database objects.

In Access, there are the following ways to run VBA programs:

Including a program in the event processing procedure;

Calling a function in an expression;

Calling a Sub procedure in another procedure or in a debugging window;

Executing the macro command RunCode (RunCode) in a macro.

Functions are used in expressions that define calculated fields in forms, reports, or queries. Expressions are used to specify conditions in queries and filters, as well as in macros, VBA statements and methods, and SQL statements. A Sub procedure can include public VBA subroutines that are called from other procedures.

Let's look at running a database query using SQL statements in a Visual Basic for Applications program.

The request selects records from the database that satisfy certain conditions (selection request), or issues instructions to perform specified actions with records that satisfy certain conditions (change request).

There are the following ways to execute queries:

Calling the Execute method (to execute SQL queries for changes);

Creation and execution of a special QueryDef object;

Using an SQL statement as an argument to the OpenRecordset method;

Execute the OpenRecordset method on an existing QueryDef object;

Calling RunSQL and OpenQuery methods.

Execute method used if you need to make a change in the database that does not return records. For example, operations of inserting or deleting records.

QueryDef object represents a saved query definition in the database. It can be thought of as a compiled SQL statement.

OpenRecordset method used to open an object of type Recordset for performing subsequent operations on it.

RunSQL method executes a macro Running a SQL Query in VBA program

OpenQuery method executes the OpenQuery macro command in a VBA program. With it, you can open a query in Datasheet, Design, or View mode. This sets one of the following modes for working with data: adding, changing or reading only.

The choice of query execution option is determined by the programmer, taking into account the characteristics of the problem being solved.

Every web developer needs to know SQL to write database queries. And, although phpMyAdmin has not been canceled, it is often necessary to get your hands dirty to write low-level SQL.

That's why we've prepared a short tour of the basics of SQL. Let's get started!

1. Create a table

The CREATE TABLE statement is used to create tables. The arguments must be the names of the columns, as well as their data types.

Let's create a simple table named month. It consists of 3 columns:

• id– Month number in the calendar year (integer).
• name– Month name (string, maximum 10 characters).
• days– Number of days in this month (integer).

This is what the corresponding SQL query would look like:

CREATE TABLE months (id int, name varchar(10), days int);

Also, when creating tables, it is advisable to add a primary key for one of the columns. This will keep records unique and speed up fetch requests. In our case, let the name of the month be unique (column name)

CREATE TABLE months (id int, name varchar(10), days int, PRIMARY KEY (name));

date and time

Data type	Description
DATE	Date values
DATETIME	Date and time values ​​accurate to the minute
TIME	Time values

2. Inserting rows

Now let's fill out our table months useful information. Adding records to a table is done using the INSERT statement. There are two ways to write this instruction.

The first method is not to specify the names of the columns where the data will be inserted, but to specify only the values.

This recording method is simple, but unsafe, since there is no guarantee that as the project expands and the table is edited, the columns will be in the same order as before. A safe (and at the same time more cumbersome) way of writing an INSERT statement requires specifying both the values ​​and the order of the columns:

Here is the first value in the list VALUES matches the first specified column name, etc.

3. Extracting data from tables

The SELECT statement is our best friend when we want to retrieve data from a database. It is used very often, so pay very close attention to this section.

The simplest use of the SELECT statement is a query that returns all columns and rows from a table (for example, tables by name characters):

SELECT * FROM "characters"

The asterisk (*) symbol means we want to get data from all columns. Since SQL databases usually consist of more than one table, it is necessary to specify the FROM keyword, followed by the table name, separated by a space.

Sometimes we don't want to get data from not all columns in a table. To do this, instead of an asterisk (*), we must write down the names of the desired columns, separated by commas.

SELECT id, name FROM month

Additionally, in many cases we want the resulting results to be sorted in a specific order. In SQL we do this using ORDER BY. It can accept an optional modifier - ASC (default) sorting in ascending order or DESC, sorting in descending order:

SELECT id, name FROM month ORDER BY name DESC

When using ORDER BY, make sure it comes last in the SELECT statement. Otherwise an error message will be displayed.

4. Data filtering

You've learned how to select specific columns from a database using an SQL query, but what if we also need to retrieve specific rows? The WHERE clause comes to the rescue here, allowing us to filter the data depending on the condition.

In this query we are selecting only those months from the table month, in which there are more than 30 days using the greater than (>) operator.

SELECT id, name FROM month WHERE days > 30

5. Advanced data filtering. AND and OR operators

Previously, we used data filtering using a single criterion. For more complex data filtering, you can use the AND and OR operators and comparison operators (=,<,>,<=,>=,<>).

Here we have a table containing the four best-selling albums of all time. Let's choose the ones that are classified as rock and have sold less than 50 million copies. This can be easily done by placing an AND operator between these two conditions.

SELECT * FROM albums WHERE genre = "rock" AND sales_in_millions<= 50 ORDER BY released

6. In/Between/Like

WHERE also supports several special commands, allowing you to quickly check the most frequently used queries. Here they are:

• IN – serves to indicate a range of conditions, any of which can be met
• BETWEEN – checks if a value is within the specified range
• LIKE – searches for specific patterns

For example, if we want to select albums with pop And soul music, we can use IN("value1","value2") .

SELECT * FROM albums WHERE genre IN ("pop","soul");

If we want to get all the albums released between 1975 and 1985, we have to write:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

7. Functions

SQL is packed with functions that do all sorts of useful things. Here are some of the most commonly used:

• COUNT() – returns the number of rows
• SUM() - returns the total sum of a numeric column
• AVG() - returns the average of a set of values
• MIN() / MAX() – Gets the minimum/maximum value from a column

To get the most recent year in our table, we must write the following SQL query:

SELECT MAX(released) FROM albums;

8. Subqueries

In the previous paragraph, we learned how to do simple calculations with data. If we want to use the result from these calculations, we cannot do without nested queries. Let's say we want to output artist, album And release year for the oldest album in the table.

We know how to get these specific columns:

SELECT artist, album, released FROM albums;

We also know how to get the earliest year:

SELECT MIN(released) FROM album;

All that is needed now is to combine the two queries using WHERE:

SELECT artist,album,released FROM albums WHERE released = (SELECT MIN(released) FROM albums);

9. Joining tables

In more complex databases, there are multiple tables related to each other. For example, below are two tables about video games ( video_games) and video game developers ( game_developers).

In the table video_games there is a developer column ( developer_id), but it contains an integer, not the name of the developer. This number represents the identifier ( id) of the corresponding developer from the table of game developers ( game_developers), logically linking two lists, allowing us to use the information stored in both of them at the same time.

If we want to create a query that returns everything we need to know about games, we can use an INNER JOIN to link columns from both tables.

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;

This is the simplest and most common JOIN type. There are several other options, but these apply to less common cases.

10. Aliases

If you look at the previous example, you will notice that there are two columns called name. This is confusing, so let's set an alias to one of the repeating columns, like this name from the table game_developers will be called developer.

We can also shorten the query by aliasing the table names: video_games let's call games, game_developers - devs:

SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;

11. Data update

Often we need to change the data in some rows. In SQL this is done using the UPDATE statement. The UPDATE statement consists of:

• The table in which the replacement value is located;
• Column names and their new values;
• The rows selected using WHERE that we want to update. If this is not done, all rows in the table will change.

Below is the table tv_series with TV series and their ratings. However, a small error crept into the table: although the series Game of Thrones and is described as a comedy, it really isn't. Let's fix this!

Table data tv_series UPDATE tv_series SET genre = "drama" WHERE id = 2;

12. Deleting data

Deleting a table row using SQL is a very simple process. All you need to do is select the table and row you want to delete. Let's delete the last row in the table from the previous example tv_series. This is done using the >DELETE instruction.

DELETE FROM tv_series WHERE id = 4

Be careful when writing the DELETE statement and make sure the WHERE clause is present, otherwise all rows in the table will be deleted!

13. Delete a table

If we want to delete all rows but leave the table itself, then use the TRUNCATE command:

TRUNCATE TABLE table_name;

In the case when we actually want to delete both the data and the table itself, then the DROP command will be useful to us:

DROP TABLE table_name;

Be very careful with these commands. They cannot be canceled!/p>

This concludes our SQL tutorial! There's a lot we haven't covered, but what you already know should be enough to give you some practical skills for your web career.

The MS Access DBMS application is a full-fledged assistant for creating and maintaining databases enclosed in tables and arrays. If the database is too large, it is quite difficult to quickly find the necessary values.

This is why Access has a feature called queries. Let's look at what it is, how it works, and what features it has.

Creating Queries in Microsoft Access

To figure out how to create queries in Access, you need to know the basics of working with a DBMS.

There are two ways to do this procedure:

• Query builder.
• Query Wizard.

The first method allows you to create any of all available queries manually, but with the small caveat that the user has experience working with an Access application. He must also understand at least its basic tasks. As for the second method, it needs to be considered in more detail.

Easy way for beginners

A knowledgeable person, with a few clicks of the mouse, selects those components that the user will need to complete the request, and then quickly creates a registry in accordance with the collected key values. If this is the first acquaintance with the DBMS, and the user has no idea how to create queries in Access, then the Wizard program is selected.

In this mode, you can familiarize yourself with and understand the following types of requests:

• Simple.
• Cross.
• Records without subordinates.
• Duplicate entries.

This choice is made already at the first stage of working with the Master. And in the future, following clear instructions, even a novice user can easily create a request. Let's get acquainted with its varieties.

Simple request

This spreadsheet tool collects the required data from user-specified fields. The name alone shows that this is the most popular type of request for beginners. Its convenience lies in the fact that this procedure opens in a new tab. Therefore, the answer to the question of how to create a query in Access 2010 becomes obvious after opening the first Wizard menu.

Cross request

This type of sampling is more complex. To figure out how to create a cross-query in Access using the “Wizard” in this mode, you need to click on this function in the first window.

A table will appear on the screen in which you can select up to three columns located in the original.

One of the remaining unselected fields can be used as query table headers. At the third stage of the procedure (intersection), another value is selected with a variability function (average value, sum, first, last).

The photo shows that the cross-request has been created, and that given parameters the necessary actions have been taken.

Duplicate entries

As the name implies, the main purpose of this query is to select all identical rows in the table according to the specified parameters. It looks like this:

In addition, you can select additional fields to match several lines at once.

To select duplicate entries, you need to expand the list of requests and create a new folder there. Next, in the “New Query” window, select the line “Search for duplicate records.” Next you need to follow the instructions of the Master.

Records without subordinates

This is the last type of query available in the Master - Records without Slaves mode.

In this case, only those values ​​are selected that are not involved in any field of tables and queries, but which have already been created.

This type is relevant only in cases where there are several databases.

All four of these query types are a starting point for working with complex elements, but make it easy to understand how to create a query in the database Access data.

Query functions in MS Access

Let's figure out why you need to perform the steps described above. The task of all simple and complex queries V Access DBMS is as follows:

• Collecting the necessary data in tables, then viewing, editing, adding new values.
• Excellent source material for preparing all kinds of reporting forms.
• Carrying out mathematical and statistical counting procedures on entire data arrays with the results displayed on the screen (average value, sum, deviation, totals).

Sample request

This type of database work is complex because it requires the participation of multiple tables.

It is necessary that all tables have common key fields. Otherwise, the operation will not be possible.

Let's review how to create a selection query in Access. First you need to create a simple query with the selection of the required fields. Here you can edit the data to bring it into the desired form. By the way, the changes made will be transferred to the source tables, so this point must be taken into account.

In the designer window that opens, the “Add tables” window is filled in. Here you need to add those tables or queries from which you need to extract the original values.

After adding, you can start filling out the request conditions. For this we need the "Field" string. In it you need to select those values ​​from the tables that will be displayed during the request.

To complete the operation, you need to click on the “Run” button.

Request with parameters

This is another type of complex procedure that will require the user to have certain database skills. One of the main areas of this action is preparing for the creation of reports with large-scale data, as well as obtaining summary results. How to create queries in Access 2007 using the designer will be discussed below.

You need to start this data sampling procedure by creating a simple query to select the required fields. Next, through the Designer mode, you must fill in the “Selection Condition” field and, based on the entered value, the selection will be carried out.

Thus, to the question of how to create a query with a parameter in Access, the answer is simple - enter the initial parameters for the selection. To work with the Designer, you must use the Query Wizard. There, primary data for filtering is created, which serves as the basis for further work.

Advanced Cross Query

We continue to complicate the situation. Even more difficult to understand is information about how to create queries in Access if there are multiple tables of data. Cross-request has already been discussed above as one of the options for working with the Master. However, you can create a similar request in Design mode.

To do this, click “Query Builder” - “Cross”.

A menu for adding source tables opens, as well as the ability to fill in selected fields. The only things you should pay attention to are the “group operation” and “cross tabulation” items. They must be filled out correctly, otherwise the procedure will not be completed correctly.

Cross queries are the easiest way to search and retrieve information from multiple data sources, plus the ability to generate charts and graphs.

Moreover, when using this procedure, the search is completed faster, even with several development options.

Of course, there are also pitfalls that can interfere with your work. For example, when creating a query to sort a database by column values, the system generates an error. That is, only sorting by standard items is available - “ascending and descending”.

To summarize, it must be said that the user must decide how to create queries in Access - using the Wizard or the Designer. Although, for most people who use the MS Access DBMS, the first option is more suitable. After all, the Wizard will do all the work himself, leaving only a few mouse clicks for the user to select the request conditions.

To use advanced settings, professional-level database experience is clearly required. If large databases are involved in the work, it is best to contact specialists in order to avoid disruption of the DBMS and possible data loss.

There is one point that is available only to programmers. Since the main language of the DBMS is SQL, the desired query can be written in the form of program code. To work in this mode, just click on the line of the already created request, and in the opened context menu select "SQL Mode".

In the simplest case, the query implements the selection from one table of the required fields, records that meet the specified selection conditions, and viewing the results of the query.

Constructing selection queries with selection conditions

Let's look at selection queries in Access using the example of retrieving information from the PRODUCT table of the Supply of Goods database.

Problem 1. Suppose you need to select a number of characteristics of a product by its name.

1. To create a query in the database window, select the ribbon tab - Creation(Create) and in the group Requests(Queries) click the button Query Builder(Query Design). An empty selection request window will open in design mode - RequestN(QueryN) and dialog box Adding a table(Show Table) (Fig. 4.2).
2. In the window Adding a table(Show Table) select the PRODUCT table and click the button Add(Add). The selected table will be displayed in the query data schema area. Close the window Adding a table(Show Table) by pressing the button Close(Close).

As a result of the actions performed in the query designer window (Fig. 4.1), top panel The query data schema appears, which includes the tables selected for the query. In this case, one table PRODUCT. The table is represented by a list of fields. The first row in the list of table fields, marked with an asterisk (*), denotes the entire set of table fields. The bottom panel is a request form that must be filled out.

In addition, a new tab (Query Tools | Design) appears on the ribbon and is automatically activated (a part of this tab is shown in Fig. 4.3), on which the type of query created is highlighted in color - Sample(Select). Therefore, by default, a select query is always created. The commands on this tab provide tools for executing necessary actions when creating a request. This tab opens when you are creating a new request or an existing one is edited.

1. To remove any table from the query data schema, place the mouse cursor on it and press a key. To add - click the button Show table(Show Table) in group Query setup(Query Setup) tab Working with requests | Constructor(Query Tools | Design) or run the command Add table(Show Table) in the context menu called on the request data diagram.
2. In the designer window (Fig. 4.4), sequentially drag the fields NAME_IT, PRICE, AVAILABILITY_IT from the list of fields of the PRODUCT table into the columns of the request form into the row Field(Field).
3. To include the desired fields from the table in the corresponding query columns, you can use the following techniques:
   • in the first line of the request form Field(Field) click the mouse to make the list button appear and select the desired field from the list. The list contains the fields of the tables represented in the query data schema;
   • double-click the table field name in the query data schema;
   • To include all table fields, you can drag or double-click the * (asterisk) symbol in the list of table fields in the query data schema.
4. If you accidentally dragged an unnecessary field onto the request form, delete it. To do this, move the cursor to the column label area at the top, where it will appear as a black arrow pointing down, and click the mouse button. The column will be highlighted. Press a key or run a command Remove Columns(Delete Columns) in group Query setup(Query Setup).
5. In line Output on display(Show) check the fields, otherwise they will not be included in the query table.
6. Write on the line Selection conditions(Criteria) name of the product, as shown in the request form in Fig. 4.4. Since the expression in the selection condition does not contain an operator, the operator = is used by default. Used in expression text value is entered in double quotes, which are added automatically.
7. Run the query by clicking the Run button or the View button in the Results group. A query window will appear on the screen in table mode with an entry from the PRODUCT table that meets the specified selection conditions.

COMMENT
The query window in table view is similar to the database table view window. Through some query tables, changes can be made to the data in the base table underlying the query. A query viewed in Datasheet view, unlike an Access 2010 database table, does not have a column Click to add(Click to Add), designed to change the structure of the table. In this mode, on the ribbon tab home(Home) the same buttons are available as when opening a database table.

1. If you made an inaccuracy when entering a complex product name, the product will not be found in the table. Using the wildcard operators - asterisk (*) and question mark (?) (ANSI-89 standard, used for default queries) or percent sign (%) and underscore (_) (ANSI-92, recommended as the standard for SQL Server), simplifies the search for the required strings and avoids many mistakes. Enter Corpus* or Corpus% instead of the full product name. Complete the request. If in the product name field one value begins with the word “Case”, the result of the request will be the same as in the previous case. After executing the query, the entered expression will be supplemented with the operator Like “Body*”. This operator allows wildcard characters to be used when searching in text fields.
2. If you need to find multiple products, use the In operator. It allows you to check for equality with any value from the list, which is specified in parentheses. Write In in the selection conditions line (“MiniTower case”; “HDD Maxtor 20GB”; “FDD 3.5″). The query table will display three rows. The In statement does not allow wildcard characters.
3. Save your request by clicking on the tab File(File) and running the command Save(Save). In the window Preservation(Save As) enter the request name Example1. Note that the query name should not coincide not only with the names of existing queries, but also with the names of tables in the database.
4. Close the current request using the context menu command Close(Close) or by clicking the query window button Close(Close).
5. Execute a saved query by highlighting the query in the navigation pane and selecting the command from the context menu Open(Open).
6. To edit a request, select it in the navigation area and execute the command in the context menu Constructor(Design View).

Task 2. Suppose you need to select goods whose price is no more than 1000 rubles, and VAT is no more than 10%, and also select goods whose price is more than 2500 rubles. The result must contain the name of the product (NAIM_TOV), its price (PRICE) and VAT (RATE_VAT).

1. Create a new query in design mode, add the PRODUCT table. In the designer window (Fig. 4.5), sequentially drag the fields NAME_IT, PRICE, RATE_VAT from the list of fields of the PRODUCT table to the request form.
2. Write it down Selection conditions(Criteria), as shown in the request form in Fig. 4.5. A logical AND operation is performed between conditions written on the same line. A logical OR operation is performed between conditions written on different lines.
3. Complete the request, click the button Execute(Run) in the group results(Results). A query window will appear on the screen in table mode with records from the PRODUCT table that meet the specified selection conditions.
4. Save the request by executing the appropriate command in the request context menu, which is called when you place the cursor on the request header. Give it a name Example2.

Problem 3. Suppose we need to select all invoices for a given period. The result must contain the invoice number (NOM_NAK), warehouse code (CODE_SC), date of shipment (DATE_SHIP) and the total cost of the shipped goods (SUMMA_INKL).

1. Create a new query in Design view, add the INVOICE table. In the designer window, sequentially drag all the necessary fields from the list of fields of the INVOICE table to the request form.
2. For the DATE_OTGR field in the line Selection conditions(Criteria) write Between #01/11/2008# And #03/31/2008#. The Between operator specifies a date interval (in ANSI-92, the # sign is used instead single quotes‘). Additionally, this operator allows you to specify an interval for a numeric value.

To reinforce this, watch the video tutorial:

You can create databases, tables, forms and other reports in MS Access. This article will help the user to run SQL queries in MS Access. You can run the same queries that are used in SQL to retrieve data from a database. This article is intended for users who have just started learning MS Access and want to run SQL queries in MS Access. The only condition that is necessary before you begin is that you have access to the database used in the organization.

Steps

What you will need

• The user must have access to the organization's database
• The user can contact technology support before running queries via MS Access

Article information

This page has been viewed 4443 times.

Was this article helpful?

Access DBMS

Microsoft Access is a relational type DBMS, in which all the tools and capabilities typical for modern systems database management. A relational database makes it easier to find, analyze, maintain, and protect data because it is stored in one place. Access translated from English means "access". MS Access is one of the most powerful, flexible and easy to use DBMS. You can create most applications in it without writing a single line of program, but if you need to create something very complex, then MS Access provides a powerful programming language - Visual Basic Application.

The popularity of the Microsoft Access DBMS is due to the following reasons:

Accessibility and clarity allow Access to be one of the best systems quickly create database management applications;

Possibility of using OLE technology;

Integration with Microsoft package Office;

Full support for Web technologies;

Visual technology allows you to constantly see the results of your actions and correct them;

Availability of a large set of “masters” for developing objects.

The main types of objects that the program works with are: table, query, form, report, page, macro, module.

A table is an object that is used to store data. Each table includes information about an object of a certain type. The table contains fields (columns) that store various types of data, and records (rows). Each table must have a primary key defined (one field that has a unique value for each record, or multiple fields whose combined value is unique for each record), which is a unique identifier for each record in the table.

To increase the speed of data access, individual table fields (or a set of them) can be declared indexes. An index is a tool that speeds up searching and sorting in a table by using key values ​​to ensure the uniqueness of table rows. The table's primary key is automatically indexed. It is not allowed to create indexes on fields with some data types.

A query is an object that allows the user to retrieve desired data from one or more tables. Queries can also create new tables using data from one or more tables that already exist. The most common type of query is a fetch query. A select query selects data from one or more tables based on specified conditions, and then displays them in the desired order.

A form is an object designed primarily to enter data, display it on the screen, or control the operation of an application.

A report is an object designed to create a document that can subsequently be printed or included in a document in another application.

visual development programming base

Page - Used to access data in the current Access database.

A macro is an object that is a structured description of one or more actions that Access should perform in response to a specific event.

A module is an object containing Microsoft Visual Basic programs that allow you to break a process into smaller actions and detect errors that could not be found using macros.

The DBMS is launched from Start - Programs - Microsoft Access. Execute the command File - New.

The interface for working with database objects is unified. For each of them, standard operating modes are provided: Create (creating an object structure); Constructor (changing the structure of objects); Open (View, Run - designed to work with database objects).

SQL Query Language

SQL (Structured Query Language) since 1986. is a standard relational database language. In particular, it is used in Access and Excel applications.

SQL is an information-logical language designed to describe stored data, retrieve stored data and modify data. Initially, SQL was the main way a user worked with a database and was a small set of commands (operators) that allowed creating tables, adding new records to tables, retrieving records from tables, deleting records and changing table structures. As SQL has become more complex, it has become a more application-oriented programming language, and users are now able to use visual query builders.

The SQL language is a collection of operators:

Data Definition Language (DDL) operators;

data manipulation operators (Data Manipulation Language, DML);

data access definition operators (Data Control Language, DCL);

Transaction Control Language (TCL) operators.

Queries in MS Access are saved and implemented using the SQL language. Although most queries can be created graphically (pattern queries), they are stored as SQL statements. In some cases (for example, in subqueries), you can only use SQL.

SQL is a non-procedural language. It simply declares what needs to be done, and the execution is left to the DBMS (database management system).

SQL uses three-valued logic. Along with the traditional Boolean values ​​TRUE and FALSE, NULL (UNKNOWN or NO DATA) is used.

Operations are performed on entire sets of data, rather than on individual elements, as in other programming languages.

A SQL query consists of statements. Each instruction can contain several sentences.