Create a simple select query. Using the Query Wizard

Twitter

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.

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).
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), a query data schema will appear in the top panel, which includes the tables selected for this 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 performing the necessary actions when creating a request. This tab opens when you create a new query or edit an existing one in Design view.

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.
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).
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.
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).
In line Output on display(Show) check the fields, otherwise they will not be included in the query table.
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. The text value used in the expression is entered in double quotes, which are added automatically.
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.

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.
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.
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.
Close the current request using the context menu command Close(Close) or by clicking the query window button Close(Close).
Execute a saved query by highlighting the query in the navigation pane and selecting the command from the context menu Open(Open).
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).

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

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.
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 (ANSI-92 uses single quotes ' instead of the # sign). Additionally, this operator allows you to specify an interval for a numeric value.

To reinforce this, watch the video tutorial:

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 perform 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 the necessary actions have been taken according to the specified parameters.

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 provide a basic starting point for working with complex elements, but make it easy to understand how to create a query in an Access database.

Query functions in MS Access

Let's figure out why you need to perform the steps described above. The purpose of all simple and complex queries in the 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 query and select “SQL Mode” in the context menu that opens.

If you need to select specific data from one or more sources, you can use a select query. A pull query allows you to get only the information you need and also helps you combine information from multiple sources. You can use tables and other similar queries as data sources for selection queries. This section briefly reviews select queries and provides step-by-step instructions for creating them using the Query Wizard or Designer.

If you want to learn more about how queries work using the Northwind database as an example, check out the article Understanding Queries.

General information

When there is a need for some data, it is rare that all the contents of one table are needed. For example, if you need information from a contacts table, it's usually about a specific entry or just a phone number. Sometimes it is necessary to combine data from several tables at once, for example, to combine information about clients with information about customers. Select queries are used to select the required data.

A select query is a database object that displays information in table view. The query does not store data, but does contain data that is stored in tables. A query can display data from one or more tables, from other queries, or a combination of both.

Benefits of queries

The request allows you to perform the following tasks:

View values only from fields that interest you. When you open a table, all fields are displayed. You can save a query that only returns a few of them.

Note: The query only returns data, but does not store it. When you save a request, you are not saving a copy of the associated data.

Combine data from multiple sources. In a table, you can usually only see the information that is stored in it. The query allows you to select fields from different sources and specify how exactly you want to combine the information.

Use expressions as fields. For example, a field can be a function that returns a date, and using the formatting function you can control the format of values from fields in the query results.

View posts that meet the criteria you specify. When you open a table, all records are displayed. You can save a query that only returns a few of them.

Basic steps for creating a select query

You can create a select query using the wizard or query designer. Some elements are not available in the wizard, but can be added later from the designer. Although these are different methods, the basic steps are similar.

Select the tables or queries you want to use as data sources.

Specify the fields from your data sources that you want to include in your results.

You can also set conditions that limit the set of record requests that are returned.

Once you've created a select query, run it to see the results. To run a select query, open it in Datasheet view. Once you save a query, you can use it later (for example, as a data source for a form, report, or other query).

Create a select query using the Query Wizard

The wizard allows you to automatically create a selection request. When you use a wizard, you don't have complete control over all the details of the process, but the request is usually created faster this way. In addition, the wizard sometimes detects simple errors in the request and prompts you to select a different action.

Preparation

If you use fields from data sources that are not related, the Query Wizard prompts you to create relationships between them. He will open relationship window, however, if you make any changes, you will need to restart the wizard. Therefore, before running the wizard, it makes sense to immediately create all the relationships that your query will require.

For more information on creating relationships between tables, see the Table Relationships Guide.

Using the Query Wizard

On the tab Creation in Group Requests click the button Query Wizard.

In the dialog box New request select item Simple request and press the button OK.

Now add the fields. You can add up to 255 fields from 32 tables or queries.

For each field, do the following two steps:

If you haven't added any numeric fields (fields that contain numeric data), skip to step 9. When you add a numeric field, you'll need to choose whether the query will return details or totals.

Do one of the following:
In the dialog box Results specify the required fields and summary data types. Only numeric fields will be available in the list.

For each numeric field, select one of the following options:
1. Sum- the query will return the sum of all values specified in the field.
  
  Avg- the query will return the average value of the field.
  
  Min- the request will return the minimum value specified in the field.
  
  Max- the request will return the maximum value specified in the field.

If you want the query results to display the number of records in the data source, select the corresponding check box Count the number of records in (data source name).

Click OK to close the dialog box Results.

If you did not add any date and time fields to your query, proceed to step 9. If you added date and time fields to your query, the Query Wizard prompts you to choose how to group date values. Let's say you added a numeric field ("Price") and a date and time field ("Transaction_Time") to the query, and then in the dialog box Results indicated that you want to display the average value for the "Price" numeric field. Because you added a date and time field, you can calculate totals for each unique date and time value, such as each month, quarter, or year.

Select the period you want to use to group date and time values, and then click Further.

On the last page of the wizard, enter a name for the request, indicate whether you want to open or edit it, and click Ready.

If you choose to open a query, it will display the selected data in Datasheet view. If you decide to edit the query, it opens in Design view.

Creating a Query in Design View

In Design view, you can manually create a select query. In this mode, you have more control over the query creation process, but it is easier to make mistakes and takes more time than in the wizard.

Create a request

Step 1: Add data sources

In design mode, data sources and fields are added at different stages because the dialog box is used to add sources Adding a table. However, you can always add additional sources later.

Automatic connection

If relationships are already defined between the data sources you add, they are automatically added to the query as connections. Joins define how data from related sources should be combined. Access also automatically creates a join between two tables if they contain fields with compatible data types and one of them is a primary key.

You can customize connections added by Access. Access selects the type of connection to create based on the relationship that matches it. If Access creates a join but does not have a relationship defined for it, Access adds an inner join.

Reusing one data source

In some cases, you can join two copies of the same table or query, which is called a self-join, and will join records from the same table if there are matching values in the joined fields. For example, let's say you have an Employees table in which the "reports to" field for each employee's record displays their manager ID instead of their name. You can use a self-join to display the manager's name on each employee's record.

When you add a data source a second time, Access will end the name of the second instance with "_1". For example, if you add the Employees table again, its second instance will be named Employees_1.

Step 2: Connect related data sources

If the data sources you add to the query already have relationships, Access automatically creates an inner join for each relationship. If data integrity is used, Access also displays a "1" above the join line to show which table is on the "one" side of the element of the one-to-many relationship and the infinity symbol ( ∞ ) to show which table is on the "many" side.

If you have added other queries to a query and have not created relationships between them, Access does not automatically create joins between them or between queries and tables that are not related. If Access doesn't create connections when you add data sources, you typically need to create them manually. Data sources that are not connected to other sources can cause problems in query results.

You can also change the join type from an inner join to an outer join so that the query includes more records.

Adding a connection

Changing the connection

Once the connections are created, you can add output fields: these will contain the data you want to display in the results.

Step 3: Add display fields

You can easily add a field from any data source you added in step 1.

To do this, drag the field from the source in the top area of the query designer window down to the row Field request form (at the bottom of the designer window).

When you add a field this way, Access automatically fills in the row Table in the designer table according to the field's data source.

Advice: To quickly add all the fields to the Field row of a query form, double-click the table or query name in the top pane to select all the fields in it, and then drag them all down onto the form at once.

Using an Expression as an Output Field

You can use an expression as an output field for calculations or to generate query results using a function. Expressions can use data from any query source, as well as functions such as Format or InStr, constants, and arithmetic operators.

In an empty column of the query table, click the row Field right-click and select from the context menu Scale.

In field Scale Type or paste the required expression. Before the expression, type the name you want to use for the result of the expression, followed by a colon. For example, to indicate the result of an expression as "Last updated", precede it with the phrase Last update:.

Note: You can use expressions to perform a variety of tasks. Their detailed consideration is beyond the scope of this article. For more information about creating expressions, see the article Creating Expressions.

Step 4: Specify conditions

It's not obligatory.

Using conditions, you can limit the number of records that a query returns, selecting only those whose field values meet specified criteria.

Defining conditions for a display field

In the query designer table in the row Selection condition field whose values you want to filter, enter an expression that the values in the field must satisfy to be included in the result. For example, to include in your query only records that have Ryazan in the City field, enter Ryazan in line Selection condition under this field.

Various examples of condition expressions for queries can be found in the article Examples of Query Conditions.

Specify alternative conditions on the line or below the line Selection condition.

When alternative conditions are specified, a record is included in the query results if the value of the corresponding field satisfies any of the specified conditions.

Conditions for multiple fields

Conditions can be set for multiple fields. In this case, all conditions in the corresponding row must be met for the entry to be included in the results Selection conditions or Or.

Setting conditions based on a field that is not included in the output

You can add a field to a query, but not include its values in the results displayed. This allows you to use the contents of a field to limit the results, but not display it.

Add a field to the query table.

Uncheck the box for it in the line Show.

Set the conditions as for the output field.

Step 5: Calculate totals

This step is optional.

You can also calculate totals for numeric data. For example, you might want to view average price or total sales.

To calculate the total values, the query uses the string Total. Default string Total does not appear in design mode.

With the query open in the designer, on the Design tab, in the Show or Hide group, click Results.

Access will display the line Total on the request form.

For each required field in a row Total select the desired function. The set of available functions depends on the type of data in the field.

For more information about the functionality of the Total row in queries, see Sum or count values in a table using the Total row.

To see the results of the query, on the Design tab, click Execute. Access displays the query results in Datasheet view.

To return to design mode and make changes to the query, click home > View > Constructor.

Customize fields, expressions, or conditions and re-run the query until it returns the data you want.

Create a select query in an Access web app

To make query results available in the browser, you need to add a query view to the table selection screen. To add a new view to the header on the table selection screen, follow these steps:

Today we will start looking at an application like − Microsoft Access 2003, which can create its own databases ( mdb format), as well as create client applications for existing databases based on MS SQL Server. The topic of today's article will be creating new queries from Access, meaning both simple queries and various functions, views and procedures. Here, a query refers to database objects.

About Microsoft Access

Microsoft Access– a software product of Microsoft, which is a relational DBMS. It has enormous capabilities when organizing a database, creating a separate application that can interact with many other DBMSs. The most common client-server solution, where the client is an application written in Access ( VBA language, forms and much more), and the server is the Microsoft SQL Server DBMS. However, Access also supports interaction with other DBMSs, for example, MySql or PostgreSQL. We can talk about Access for a long time, but the purpose of today’s article is precisely creating queries ( objects) from Access.

Let's move on to practice and start with a simple mdb database, i.e. how to create these very queries.

Creating queries in Microsoft Access 2003 - MDB database

First, open the database, then click on objects "Requests" and press the button "Create".

Note! This means that you already have a database.

And a new window for selecting the type of request will open in front of you.

Let's look at each of these request types in more detail.

Query Types in Access 2003 - MDB

Constructor- this is creating a query based on a constructor, so to speak, in a graphical editor, but in it you can switch to sql mode and write the query text as usual. Immediately after launch, a window will open for you to select the necessary tables or existing queries, since existing queries can also be used to select the necessary data, it’s as if "performance".

If you don’t like doing this in a graphical editor, you can switch to SQL mode by clicking the menu item "View", then "SQL Mode".

Simple request- this, one might say, is the same construction set, only a slightly different type and fewer possibilities.

Cross request– this is the so-called transposition of the table, in other words, the output of data that is located in horizontal columns, i.e. Each value from one column will be displayed horizontally in a separate column. This is all done with the help of a wizard, so there should be no problems.

Duplicate entries– as the name suggests, this is a search for duplicate records.

Records without subordinates– this is a search for those records that are missing in a particular table.

With mdb databases it is enough, since they are rarely used in enterprises, they usually use the following scheme - they write a separate client, and all data is stored on the server using a DBMS in our case - this is MS SQL Server, and the client is Access (.adp).

Creating queries in Microsoft Access 2003 - MS SQL Server database

Let's look at creating new queries from an Access client based on MS SQL Server ( It is assumed that you already have an adp client and a database based on MS SQL Server).

Note! This article does not imply learning sql, so by the time you read this article you should already understand the basics of sql and the concept of basic objects in the database such as: view, function, procedure. If you are completely new to this, then first, of course, it is recommended to master SQL, since many of the terms below will not be clear to you. Recommended articles:

What are VIEWS views in databases? And why are they needed?

The beginning is the same, opens the project, then click on objects "Requests" and press the button "Create".

And now in more detail.

Types of queries in Access 2003 - MS SQL Server database

Built-in function constructor- this, one might say, is a regular view, only parameters can be passed into it, then some queries are executed on the server, and a table is returned. It is a kind of function that returns data in the form of a table. It is addressed as follows ( if we talk about sql):

SELECT * FROM my_test_tabl_func(par1, par2 ……)

After clicking on "OK" To create this function, you will see the already familiar window for adding existing tables and views. But I usually close this window and write the request manually in a special field. In order for this field to be displayed, click the following on the panel:

Then, if you want to add incoming parameters, you can simply put the @ sign and the name of the variable in the condition, for example, like this:

SELECT * FROM table WHERE kod = @par

After on the panel in the function properties

on the tab "function parameters" the parameters that you specified will appear, and they must be transmitted in the order in which they are indicated here.

View constructor- this is the creation of an ordinary idea among ordinary people "Vyuha".

Stored Procedure Constructor– creating a procedure using a constructor, the principle is the same as in the above functions. Let me remind you that the procedure is a set of sql operators, both for sampling and changing data.

Entering a Stored Procedure– this is the creation of a procedure using a text editor, i.e. creation of a procedure purely manually. In the end, the same thing as using the constructor. When creating objects in a text editor, the creation template is already created automatically by access.

Entering a scalar function is creating a function that returns a value. Created using a text editor.

Entering a table function is creating a function that will return a set of records. Looks like a built-in function.

In order to distinguish them in the access client, they have different icons, the same ones that you see when creating this or that object.

When you create all these objects, they are saved on the server, and you can use them not only from your adp project, but also from other clients.

Of course, you can create all these objects on the server using, for example, Enterprise Manager ( deprecated, now SQL Server Management Studio), but today we are considering the possibility of creating these objects from the access client.

For the basics, I think this is enough, if we talk about this in more detail, it won’t fit in one article, but it seems to me that this is enough to create certain requests. But if you have questions about creating a particular function or procedure, then ask them in the comments, I will try to help.

Subject: Creating inter-table relationships. Creating a selection query, with a parameter and a final query.

Open the database created in the previous lab Dean's office of the TF .

Creating inter-table relationships

Select an object in the database window Tables . Click on the button " Scheme data " on the toolbar or execute the command " Service »  « Scheme data " using the command menu. A window will appear on the screen: Scheme data " and window " Addition tables "(Fig. 18).

Rice. 18. Data Schema Dialog Box and Add Table Dialog Box

In the window " Addition tables " on the tab " Tables » lists all database tables. To create relationships between tables, you need to move them from the " Addition tables "out the window" Scheme data " To transfer a table, select it with a mouse click and click on the button “ Add " After transferring the necessary tables, close the window " Addition tables ».

Move all tables to the window " Scheme data " Resize the table windows so that all the text is visible (Fig. 19).

To create a relationship between tables Students And Ratings in accordance with Data schema you need to move the mouse cursor to the field Student code in the table Students and with the mouse button pressed, drag this field onto the field Student code in the table Ratings , and then release the mouse button. The window “ Change connections "(Fig. 20).

Check the property " Security integrity data " by clicking on it. Select the checkboxes in the properties " Cascade update related fields " And " Cascade deletion related fields " This will allow you to edit records only in the table Students , and in the table Ratings these actions on linked records will be performed automatically.

To create a connection, click on the button " Create ».

Rice. 19. Appearance of the database data schema " Dean's office of the TF» before making connections

Rice. 20. Dialog box for specifying connection parameters between table fields

Likewise according to Data schema connections are created between the remaining tables (Fig. 21).

Rice. 21. Database Data Schema " Dean's office of the TF»

Reply when closing the data diagram window Yes to the question about saving the layout.

Created relationships between database tables can be changed.

To change connections you need to call the window " Scheme data " After this, place the mouse cursor on the connection that needs to be changed and right-click. A context menu will appear (Fig. 22):

Rice. 22. Communication context menu

If you select the command " Delete ", then after confirmation the connection will be deleted. If you need to change the connection, select the command " Change connection " After that, in the window that appears “ Change connections " (at the top of it) select the fields in the tables that you want to link and click on the button " Create ».

Requests

Queries are used to select or search data from one or more tables. You can use queries to view, analyze, and modify data from multiple tables. They are also used as a data source for forms and reports. Queries allow you to calculate totals and display them in a compact format, as well as perform calculations on groups of records.

We will develop requests in the mode Designer .

IN Access You can create the following types of queries:

Sample request . This is the most commonly used request type. This type of query returns data from one or more tables and displays it as a table. Select queries can also be used to group records and calculate sums, averages, count records, and find other types of totals. To change the selection conditions, you need to change the request.

Request with parameters . This is a prompt that, when executed, displays its own dialog box prompting you to enter the data or value you want to insert into a field. This data or value may change each time the request is made.

Cross request . Used for calculations and presentation of data in a structure that facilitates their analysis. A cross-section query calculates the sum, average, number of values, or performs other statistical calculations, and then groups the results in a table across two sets of data, one defining the column headings and the other defining the row headings.

Change request . This is a query that modifies or moves multiple records in one operation. There are four types of change requests:

1. To delete an entry. This query deletes a group of records from one or more tables.

2. To update the record. Makes general changes to a group of records in one or more tables. Allows you to change data in tables.

3. To add records. Appends a group of records from one or more tables to the end of one or more tables.

4. To create a table. Creates a new table from all or part of the data from one or more tables.

Requests SQL . Created using language instructions SQL , used in DB .

Purpose of work: Studying techniques for constructing and using queries to select data.

Before performing laboratory work, you must study the following sections:

Types of requests;

Creating queries in design mode;

Rules for recording data selection conditions;

Using built-in functions;

Creating calculated fields;

Using data input/output forms;

Creation of cross and active queries.

Task 1: Create a simple query.

1. Open the database Institute.

2. Click in the Database window on the Queries object.

3. In the Queries window, click the Create button.

4. In the New Query window, select Design and click Ok.

5. In the Add Table window, add all four tables and close the window.

6. Adjust the size and location of the table windows in the data diagram.

7. Drag the corresponding field names (faculty name, group N, specialty name, gradebook N, full name) from the table windows to the request form located under the data schema, observing their specified order. Use one form column for each field that must be included in the selection.

8. View the selection by executing the VIEW/Table Mode command or by clicking the View toolbar button.

9. Return to query designer mode if the selection contains errors and correct the query.

10. Close the request. A dialog box will appear asking you to confirm whether you want to save it. Name the query Select1.

Task 2. Create a simple query to retrieve information from the database, including the same fields as the previous query, but containing information only about commercial students. Such a request is called conditional request.

1. In the Database window, copy the Select1 query by dragging the query icon while pressing the Ctrl key.

2. Rename the query to Select by commercial. To do this, right-click on the request name and select Rename from the context menu.

3. Open the query in design view. Add the Commercial field to your request.

4. Enter the value Yes in the Selection condition field for the Commercial field.

5. Disable display when prompted for the value of the Commercial field. To do this, turn off the display checkbox for this field.

6. Browse the selection by clicking the Run button on the toolbar.

7. Close the request, saving the request layout.

Task 3. Create a query to find the last name and record number of the youngest student in one of the groups.

1. In the Database window, click the Create button and select the Design option.

2. In the Add Table window, select the Student table. Insert all fields of this table into the request form. To do this, first select all the fields in the data diagram using the Shift key, and then drag them to the first line of the request description form.

3. Enter a formula expression in the field Selection condition for the Date of Birth field in accordance with the task. Use the Access Expression Builder tool. To do this, place the cursor in the corresponding cell of the request description table and click on the Build toolbar button.

4. In the Expression Builder window, enter the name of the function DMax. To do this, expand the Functions list in the left pane of the builder and select Built-in functions. Next, in the middle subwindow, select the function category By subset, and in the right subwindow, select the DMax() function. The corresponding function will appear in the main builder window with its arguments indicated.

5. Remove the first argument of the function and insert in its place the name of the Date of Birth field, either by direct keyboard entry, or by clicking on the Tables list in the left pane, and then selecting the Student table and the field in it. In the latter case, you need to remove the unused part of the Expression line. Next, enter the values of the remaining function arguments, so that the function takes the following final form: DMax("[Date of Birth]";"Student";"=851")

6. To write the function into the request description table cell, click the Ok button. Close the request by saving the layout and renaming it Youngest Student Sample. View the result of a query by double-clicking on its name in the database window.

7. Demonstrate the result of your work to the teacher.

Task 4. Create a query to count the number of commercial students in each group.

1. Create a new query using the Faculty, Group, Student tables.

3. Set the selection condition for the 3rd column to Yes.

4. Set the 1st and 2nd columns of the form to sort in ascending order.

5. Disable display of the 3rd column data.

6. Enter the Commercial field in the 4th column and replace the column name with Number of commercial. To do this, the cell with the field name must contain: Number of commercial: Commercial (new and old column names are separated by a colon)

7. By clicking on the Group Operations toolbar button, add the Group Operation line to the form and select the Count operation from the list for the 4th column.

8. View the totaled selection by clicking the Run toolbar button or by executing the QUERY/Run command.

9. Return to Query Design mode by clicking the Dashboard View button.

10. Save the request, giving it the name Counting commercial by groups.

Task 5. Create a query that allows you to see a sample that reflects the number of commercial students for each faculty and each group. Column headings should correspond to the names of faculties, row headings should correspond to group numbers. The sample should also contain a summary column with the total number of commercial students in each department. This type of sampling can be implemented with a cross-query. To apply such a query, it is desirable to have in the database information on 5-6 groups of students studying at 3 faculties.

1. Using the designer, create a new query using the Faculty, Group, Student tables.

2. Enter the Faculty Name field in the 1st column of the request form, the Group N field in the 2nd column, and the Commercial field in the 3rd column.

3. Execute the QUERY/Cross command, or click the Query Type toolbar button and select Cross from the list.

4. Select the values in the row of the Crosstab form by expanding the list in the cells: for the 1st column Row Headings, for the 2nd column – Column Headings, for the 3rd column – Value.

5. Select the Count function for the group operation in the 3rd column.

6. View the cross sample by clicking the Run button.

7. To create a summary column, go back to design mode and insert another Commercial field into the request form. Enter the name of the Total column before the name of this field: In the Group operation row, select Count, and in the Crosstab row, select Row headings.

8. In table mode, reduce the width of the columns of the selection table. To do this, select the columns with data in groups and run the command FORMAT/Column Width/Fit to Data Width.

9. View the edited selection and save the request, giving it the name Number of commercial by groups and faculties.

10. An approximate view of a cross-sectional sample is shown in Fig. 1.

Fig.1. - Cross-sampling of the number of commercial students by groups and faculties.

Task 6. Write a query to list lists of groups, with the group number requested as part of the query. Such a request is called request with parameter. The parameter is the Group Number. The parameter value is entered in the dialog box. To create a request, you must enter the text of the selection condition in square brackets in the cell with the condition.

1. Using the designer, create a new query using one Student table.

2. Enter all the fields of the table in the 1st line of the request form.

3. Enter the text in the cell of the line Selection condition for the N group field: [Enter the group number]

4. Run the request and enter the number of one of the groups in the dialog box that appears. Browse the selection. Save the request and name it Query with Parameter.

5. Demonstrate the result of your work to the teacher.