Lessons 28–29 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
29
Searching and processing data by a given pattern in MS Access
Textbook: pp. 62–66
GoalSearches, sorts and filters data in a table and builds select queries with criteria and a parameter.
New words
query: a stored question that selects data from tables · so‘rovcriteria: the condition a record must meet to appear in the result · shartparameter query: a query that asks for a value each time it runs · parametrli so‘rovfilter: a temporary hiding of records that do not match · filtr
Explanation
For quick searching in a table use Home – Find (Ctrl+F); sort and filter buttons are also on the Home tab, and a column is sorted by choosing A→Z or Z→A. A filter temporarily shows only the matching records and deletes no data; Toggle Filter cancels a filter and Remove Sort cancels a sort. For a permanent question, add the table in Create – Query Design, move the fields into the grid and write a condition in the Criteria row: text in quotation marks (“10A”), numbers as >=4 or Between 4 And 5, and a pattern as Like “G*” (starting with G). Conditions in one row work as “And” (all must be met), those in different rows as “Or” (at least one). In a parameter query the condition holds a question in square brackets, for example [Enter a name]; the query asks for the value every time it runs. The result is viewed with Run, and saving the query lets you reuse it later.
Worked examples
A Students table (Name, Class, Mark): Anvar 10A 5; Bobur 10B 4; Dilnoza 10A 3; Eldor 10A 5; Farida 10B 5; Gulnora 10A 4; Hasan 10B 3; Iroda 10A 5. Class = “10A” and Mark >= 4 in one row: Anvar, Eldor, Gulnora, Iroda – 4 records.
In the same table, Like “*a” in Name gives names ending in a: Dilnoza, Farida, Gulnora, Iroda – 4 records. Between 4 And 5 in Mark includes the limits and returns everybody except Hasan and Dilnoza – 6 people.
Class activity
“The search workshop”: a pair makes an 8-row table, builds queries with three conditions (a text pattern, a range, And) and checks the results against their own prediction.
Practice
1
In which row of the query grid is a condition written?
Criteria
2
How many records in the table above match Class = “10B”?
3
3
Write a condition for names starting with G.
Like "G*"
4
Why can a parameter query be more convenient than an ordinary one?
The condition can be typed in each time it runs, without editing the query.