AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Contents · Computing

The library database and queries

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.

CriteriaMeaning
"10A"text – in quotation marks
>=4a number condition
Between 4 And 5includes the limits
Like "G*"starts with G
💡 Where will I use this?
In real life

A PE teacher with a table of hundreds of running results wants to see only the results in a certain range. The teacher put on a filter, hid the rest for the moment and sorted the fastest to the top. No record was deleted, and removing the filter brings everything back.

Who uses it

Online shops offer filters by price; librarians, analysts and help desks pick out the records they need with queries.

Try it today

Write 6 invented items and their prices in your notebook; underline in colour those cheaper than a chosen limit – this is a filter done by hand.

Worked examples
NameClassMark
Anvar10A5
Bobur10B4
Dilnoza10A3
Eldor10A5
Farida10B5
Gulnora10A4
Hasan10B3
Iroda10A5
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.
NameClassMark
Anvar10A5
Bobur10B4
Dilnoza10A3
Eldor10A5
Farida10B5
Gulnora10A4
Hasan10B3
Iroda10A5
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?
2
How many records in the table above match Class = “10B”?
3
Write a condition for names starting with G.
4
Why can a parameter query be more convenient than an ordinary one?
Story of the Week · The Khwarizmi WayWe blew between two sheetsClose the week: all of this week’s subjects in one storyRead →