home › Internet › Select queries in Access. Creating Queries in Microsoft Access

Select queries in Access. Creating Queries in Microsoft Access

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 .

I would like to be able to create a parameterized query in MS Access 2003 and pass the values of certain form elements into that query, and then get the corresponding result set and do some basic calculations with them. I'll briefly walk you through how to get query parameters to populate with form elements. If I need to use VBA, that's fine.

5 answers

References to controls on a form can be used directly in Access queries, although it is important to define them as parameters (otherwise the results in latest versions Access can be unpredictable if it was once reliable).

For example, if you wanted to filter a query by the LastName control in MyForm, you would use this as the criterion:

LastName = Forms!MyForm!LastName

PARAMETERS [!MyForm!] Text (255); SELECT tblCustomers.* FROM tblCustomers WHERE tblCustomers.LastName=!;

However, I would like to ask why you need a stored query for this purpose. What do you do with the results? Display them in a form or report? If this is the case, you can do this in the form/report record source and leave the saved query unchanged with the parameters so that it can be used in other contexts without displaying a prompt to populate the parameters.

On the other hand, if you're doing something in code, just write the SQL on the fly and use the literal value of the form control to create the WHERE clause.

Here is a code snippet. Updates the table using the txtHospital parameter:

Set db = CurrentDb Set qdf = db.QueryDefs("AddHospital") qdf.Parameters!txtHospital = Trim(Me.HospName) qdf.ReturnsRecords = False qdf.Execute dbFailOnError intResult = qdf.RecordsAffected

Here's an example SQL:

PARAMETERS txtHospital Text(255); INSERT INTO tblHospitals() VALUES()

There are three traditional ways to get around this problem:

Set the parameter to a value so that the user is prompted for a value when running the query.
Link field on the form (possibly hidden)
Create the query on the fly and don't use parameters.

I think it's just wrong for me that you would have to enter something like [?enter ISO code of the country] or links to fields in your form like: !! .

This means that we can't reuse the same query in more than one place because different fields provide data, or we have to rely on the user not confusing the data entry when running the query. As far as I remember, it can be difficult to use the same value more than once with a user-entered parameter.

Typically I would choose the latter option, create the query on the fly, and update the query object as needed. However, this is a common SQL injection attack (either by accident or on purpose knowing my users) and it's just obscene.

"Ed. Start - for completion of the example dim qryStartDate as date dim qryEndDate as date qryStartDate = #2001-01-01# qryEndDate = #2010-01-01# "Ed. End "QUOTEING "stallion": To pass parameters to a query in VBA " is really quite simple: "First we"ll set some variables: Dim qdf As Querydef Dim rst As Recordset "then we"ll open up the query: Set qdf = CurrentDB.QueryDefs(qryname) "Now we"ll assign values to the query using the parameters option: qdf.Parameters(0) = qryStartDate qdf.Parameters(1) = qryEndDate "Now we"ll convert the querydef to a recordset and run it Set rst = qdf.OpenRecordset "Run some code on the recordset "Close all objects rst.Close qdf.Close Set rst = Nothing Set qdf = Nothing

(I haven't tested this myself, just something I've collected on my travels because every now and then I've wanted to do this but ended up using one of my previously mentioned kludges)

edit I finally had a reason to use this. Here's the actual code.

"... Dim qdf As DAO.QueryDef Dim prmOne As DAO.Parameter Dim prmTwo As DAO.Parameter Dim rst as recordset "... "open up the query: Set qdf = db.QueryDefs("my_two_param_query") "params called param_one and "param_two "link your DAP.Parameters to the query Set prmOne = qdf.Parameters!param_one Set prmTwo = qdf.Parameters!param_two "set the values of the parameters prmOne = 1 prmTwo = 2 Set rst = qdf.OpenRecordset(dbOpenDynaset , _ dbSeeChanges) "... treat the recordset as normal "make sure you clean up after your self Set rst = Nothing Set prmOne = Nothing Set prmTwo = Nothing Set qdf = Nothing

DoCmd.SetParameter "frontMthOffset", -3 DoCmd.SetParameter "endMthOffset", -2 DoCmd.OpenQuery "QryShowDifference_ValuesChangedBetweenSELECTEDMonths"

Where SQL query Access actually includes SQL. For example

"select blah from mytable where dateoffset="

Everything just works!

Let's take an example. the parameterized request looks like this:

Select Tbl_Country.* From Tbl_Country WHERE id_Country = _ [?enter ISO code of the country]

and you would like to be able to get this value ([? enter... country] one) from a form where you have controls and some data in it. Well... it might be possible, but it requires some normalization of the code.

One solution would be to assign some logic to the form controls, such as fid_Country for the control, which would hold the id_Country value. Your query can then be represented as a string:

Qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = "

Once you have entered all the requested data into the form, click the “request” button. The logic will go through all the controls and check if they are present in the request, eventually replacing the parameter with the control's value:

Dim ctl as Control For each ctl in Me.controls If instr(qr,"[" & ctl.name & "]") > 0 Then qr = replace(qr,"[" & ctl.name & "]",ctl .value) End if Next i

In this case, you will have a completely updated query, in which the parameters are replaced with real data. Depending on the fid_country type (string, GUID, date, etc.) you may need to add additional double quotes or not to get the final request like:

Qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = ""GB"""

This is a fully Access-compatible query that you can use to open a recordset:

Set rsQuery = currentDb.openRecordset(qr)

I think you're done here.

This topic is critical when your goal is to develop Access applications. You must offer users standard way request data from them GUI not only for running queries, but also for filtering continuous forms (just like Excel does with the AutoFilter option) and managing report options. Good luck!

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

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

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

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.

Rice. 22. Communication context menu

Requests

We will develop requests in the mode Designer .

IN Access You can create the following types of queries:

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 .

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 MS-based databases 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 – software Microsoft company, 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 Microsoft DBMS SQL Server. 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 request type selection window 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 request based on the constructor, so to speak in graphic 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. This 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 sql statements, 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 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 all these objects are created, 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.

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

Before execution laboratory work You need to 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 context menu Rename item.

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 Selection Condition line 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 new request using tables Faculty, Group, Student.

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.

Popular in the category: