Відбір в запитах за певним стовпцем access. Як задати умови відбору записів в запиті? Логічні операції "І", "АБО"

Для обмеження списку записів, одержуваних в результаті роботи запиту, тільки задовольняють певним умовам - в бланку запиту передбачені поля для умов відбору. Коротко про це було розказано в "Крок 22 - Створення запиту на вибірку", тепер настав час розібратися більш детально.

Найголовніше, що слід запам'ятати, це те, що для кожного поля запиту можна створити свою умову відбору. Якщо це числове поле, То можна вказати цікавить діапазон значень. Наприклад, в поле Ціна можна задати умову & gt20, що дозволить вибрати всі книги, ціни яких перевищують цифру 20.

для текстового поля задається рядок, вміст якої буде порівнюватися зі значеннями відповідного поля таблиці. Збіг значень призведе до додавання поточного запису в підсумкову таблицю. При складанні рядка знак * означає будь-яку послідовність символів, а ? один будь-який символ. Наприклад, умова "Новікон" у полі видавництво, Видасть список книг, надрукованих тільки в цьому видавництві. Умова "Нов *" відповідає значенням починається з нов, "* А *" видасть всі видавництва з буквою а в назві, "?????" відшукає всі комбінації з п'яти символів, а "??*" відповідає значенням складається не менше ніж з двох символів.

Так можна поступати, якщо умова відбору для запиту заздалегідь відомо і не виникне необхідність його зміни. На практиці, в багатьох випадках користувачу треба надати можливість самостійного вибору того, що він хоче знайти в таблицях бази даних. Для цього параметр умови відбору повинен запитуватися при кожному сеансі роботи запиту. Припустимо, що покупець хоче дізнатися про наявність в магазині книг Айзека Азімова. Всі інші книги його не цікавлять, а витрачати свій час на перегляд всієї бази в пошуках потрібної інформації він, зрозуміло не має наміру. Тоді в запиті просто необхідно передбачити можливість отримати від покупця цю інформацію і видати йому тільки записи, у яких ім'я автора Айзек, а Прізвище автора Азімов.

Для цієї мети служить спеціальна команда мови SQL, Яка виглядає так:

Like [ Текст повідомлення користувачу ]

У квадратних дужках записується текст, що виводиться в вікні введення параметра, Що з'являється на екрані, відразу після початку роботи відповідного запиту. Поле введення приймає набране на клавіатурі значення і передає його в якості умови відбору. далі СУБД переглядає всі записи бази даних в пошуках збігу значень і виводить результати пошуку в підсумковій таблиці.

За замовчуванням Access визначає тип даних, що вводяться як Текстовий. Якщо ж параметр задає умову відбору з шпальти з даними типу числовий або Дата час , То необхідно вручну призначити тип даних. Це робиться в такий спосіб:

Можна вказати кілька умов відбору для поля запиту. Друга умова міститься в рядок або, Третє на рядок нижче і так далі, поки не будуть вказані всі необхідні значення. Невиконання хоча б однієї з умов відбору, забороняє додавання запису в результуючу таблицю. Тому необхідно гранична увага під час набору значень. Помилки або неточності можуть кардинально вплинути на роботу створюваного запиту.

Порада: Перш ніж перетворювати запит будь-якого типу в запит з параметрами, повністю завершите процес конструювання і тестування запиту. Використання в запиті тестового (точно присутнього в записах таблиці) умови відбору забезпечує узгодженість процесу тестування. Крім того, якщо в запиті не застосований жоден параметр, можна більш швидко перемикатися між режимами конструювання та роботи запиту. Закінчивши тестування і перевіривши працездатність запиту, відредагуйте умови відбору і додайте необхідні тексти підказок для вікон введення значень параметра.

В Access передбачено велика кількість варіантів відбору записів з бази даних. Розберемо деякі широко поширені умови відбору записів, що використовуються в запиті.

Оператори

Оператор - це символ або слово, наприклад, \u003d або Оr, що вказують виконання операції над одним або декількома елементами. Оператори дозволяють виконувати операції над елементами формули. Access дозволяє використовувати різні типи операторів для обчислень на аркуші:

арифметичні оператори - служать для виконання арифметичних операцій над числами (таблиця);
оператори порівняння - використовуються для порівняння двох значень. Результатом порівняння може бути логічне значення: або ІСТИНА, або БРЕХНЯ;
текстовий оператор конкатенації Амперсанд (&) - використовується для об'єднання кількох текстових рядків в один рядок;
оператори посилання - застосовуються для опису посилань на діапазони осередків.

Запит записів в певному діапазоні значень може виконуватися з використанням наступних операторів порівняння: \u003d - одно; \u003e - більше, ніж;< - меньше, чем;
<> - не дорівнює;
\u003e \u003d - більше або дорівнює;
<= - меньше или равно.
Припустимо, вас цікавлять співробітники, зарплата яких перевищує 300 рублів. У рядок Умови відбору (Criteria) введіть:\u003e 300.
Як правило, оператори порівняння використовуються в числових полях або полях дат, але вони можуть застосовуватися і в текстових полях. Наприклад, якщо ввести в текстове поле умова відбору< "П", то будут выбраны значения поля, начинающиеся после буквы «П».

оператор LIKE

Якщо ви не впевнені в написанні будь-якого слова, то можете використовувати оператор LIKE і підстановлювальний знак. Нагадаємо, що знак питання замінює один символ, а зірочка * - групу символів. Наприклад, вираз: LIKE П? Л виконує пошук слів, що починаються з літери П. Вираз: LIKE виконує пошук слів, співав, упав, підлогу і т.п.
Під виразом мається на увазі будь-яка комбінація операторів, констант, значень текстових констант, функцій, імен полів (стовпців), елементів управління або властивостей, результатом якої є конкретне значення.

Виконання стандартних обчислень нал значеннями поля

Одним з широко поширених методів аналізу табличних даних є використання наступних підсумкових функцій для полів з числовими даними:

Sum - обчислення суми значень поля;
Avg - визначення середнього значення поля;
Min - знаходження мінімального значення поля;
Мах - знаходження максимального значення поля;
Count - підрахунок кількості записів поля (може застосовуватися для всіх полів);
StDav - розрахунок стандартного відхилення поля;
Var - розрахунок зміни значень поля.

Виконання групових операцій

Стовпець підсумків може містити підсумкові дані для всіх записів таблиці або згрупованих по якомусь принципом. Наприклад, нас може цікавити максимальна або середня ціна товарів (поле Ціна) кожного типу (поле Тип), представлених в таблиці. Для використання підсумкових операторів у зазначеній задачі виконайте наступні дії:

в запросной формі в рядку Вивід на екран (Show) поставте прапорці в полях: Тип і Ціна, яке буде використовуватися для обчислень;
виберіть в меню Вид (View) команду Групові операції (Totals) або jm-тисніть однойменну кнопку на панелі інструментів;
встановіть курсор в поле, над значеннями якого будуть виконуватися обчислення, і виберіть в осередку Групова операція (Total) потрібну опцію;
в меню Запит (Query) виберіть команду Запуск (Run).

За допомогою запиту можуть бути підраховані сума (Sum) і середнє арифметичне (Avg), знайдені мінімальне (Min) і максимальне (Max) значення в поле. Закінчивши роботу із запитом, можна зберегти його під яким-небудь ім'ям.

Мал. 24.11 Складання запиту з використанням групової операції

Розширення умов відбору

Наприклад, ви хочете дізнатися обсяг продажів дорогих (дорожче 2500 руб.) І дешевих (менше 500 руб.) Замовлень. Введіть в осередок Умови відбору (Criteria):\u003e 2500, в клітинку або (or)<500. Условия, заданные в ячейке или, будут восприниматься как дополнительные.

Використання обчислюваного поля

Обчислюване поле відображає дані, отримані в запиті за результатами розрахунку вираження. Значення поля перераховується при кожній зміні вираження.
Наприклад, якщо кожне замовлення коштує 5 рублів і ви хочете обчислити вартість всіх замовлень, зроблених кожним покупцем, то введіть в осередок Поле вираз: Вартість: [Кількість замовлень] * 5.

будівник виразів

Для відображення вікна будівника виразів (рис. 24.12) виконайте наступні дії:

перейдіть в режим конструктора запиту;
в рядку Условие отбора (Criteria) клацніть правою кнопкою миші стовпець, у якому ви хочете критерії відбору, і виберіть в контекстному меню команду Побудувати (Build) або натисніть однойменну кнопку на панелі інструментів.

Мал. 24.12 Будівник виразів

У верхній частині діалогового вікна будівника виразів розташовано поле, яке відображає вираження у міру його створення. Access часто поміщає в це поле прототипи, укладені в подвійні кутові лапки, замість яких користувач повинен підставити потрібні елементи. Слід або ввести відповідне значення, або виділити прототип, і замінити його на елемент з правого списку.
У середній частині вікна будівника знаходиться розділ, призначений для створення елементів вираження. У ньому розташовані кнопки з часто використовуваними операторами. При натисканні на одну з цих кнопок будівник вставить відповідний оператор в поточну позицію поля вираження. Наприклад, ви можете натиснути кнопку Like, щоб не вводити це слово з клавіатури.
У нижній частині вікна будівника знаходяться три поля. У лівому полі виводяться папки, що містять таблиці, запити, форми, об'єкти бази даних, вбудовані і певні користувачем функції, константи, оператори і загальні вирази. В середньому поле задаються певні елементи або групи продуктів для папки, заданої в лівому полі. У правому полі виводиться список значень (якщо вони існують) для елементів, наприклад, якщо вибрати в лівому полі Вбудовані функції, то в середньому полі з'явиться список всіх типів функцій Microsoft Access, в правому полі буде виведений список всіх вбудованих функцій, заданих лівим і середнім полями.
Щоб вивести повний список операторів, виберіть папку Оператори в нижньому лівому полі і потрібний тип в середньому полі. У правому полі будуть виведені всі оператори обраного типу. Можливий безпосереднє введення частини вираження в верхнє поле.
Для повернення в бланк запиту натисніть кнопку ОК, Побудоване вираження буде вставлено в те поле, де розташований курсор. Імена полів при введенні в бланк запиту слід укласти в квадратні дужки

Умови відбору записів можуть задаватися для одного або декількох полів у відповідному рядку бланка запиту.

Умовою відбору є вираз, який складається з операторів порівняння і порівнюваних операторів. В якості операторів порівняння і логічних операторів можуть використовуватися такі: \u003d,<, >, < >, Between, In, Like, And, Or, Not, які визначають операцію над одним або декількома операндами.

Якщо умова відбору не містить оператора, то за замовчуванням використовується оператор \u003d.

Як операнди можуть використовуватися літерали, константи і ідентифікатори (посилання).

Літералом є значення, сприймається буквально, а не як значення змінної або результат обчислення (наприклад, число, рядок, дата).

Константами є не змінні значення (наприклад, True, Falls, Так, Ні, Null).

[Ім'я таблиці]! [Ім'я поля]

Умови відбору, задані в одному рядку, зв'язуються за допомогою логічної операції І, а задані в різних рядках - за допомогою логічної операції АБО. Ці операції можуть бути задані явно в умови відбору за допомогою операторів AND і OR відповідно.

Сформувати умова відбору можна за допомогою будівника вираження. Перейти у вікно Будівник виразів можна, натиснувши кнопку [Будівник) на панелі інструментів або вибравши команду Побудувати в контекстно-залежному меню. При цьому курсор миші повинен бути встановлений в осередку введення умови відбору.

Після введення виразу в бланк і натискання клавіші Access виконує синтаксичний аналіз вираження і відображає його у відповідності з результатами цього аналізу.

Для виконання запиту необхідно на панелі інструментів конструктора запитів натиснути кнопку [Запуск (!)] Або [Подання запиту].

Сортування даних. Для зручності перегляду можна сортувати записи в таблиці в певній послідовності. Кнопки сортування на панелі інструментів (або команди меню Записи \\ Сортування, Сортування по зростанню (Сортування за спаданням) дозволяють сортувати стовпчики за зростанням або спаданням. Перш ніж клацнути по кнопці сортування, слід вибрати поля, які використовуються для сортування. Сучасні СУБД (такі, як Access) ніколи не сортують таблиці фізично, як це робилося раніше. Засоби сортування даних (а також фільтрації, пошуку і заміни) реалізовані в Access як автоматично створювані запити. Записи таблиці завжди розташовуються у файлі бази даних і тому порядку, в якому вони були додані в таблицю.

Логічні оператори застосовуються для об'єднання двох логічних значень і повернення значення «істина», «брехня» або null. Логічні оператори також іноді називаються булеві.

Головною метою при створенні бази даних є упорядкована організація інформації в таблиці, уникаючи надмірності відомостей і досягаючи логіки при їх об'єднанні. Природно, користувачеві не цікаві всі дані, що знаходяться в документах і клієнта більш цікавить конкретна інформація, ніж її місце розташування. Саме для подібної фільтрації відомостей і існують запити. Запит на вибірку в Access є одним з найбільш використовуваних, він дозволяє витягувати необхідну інформацію, Проводити обчислення, створювати перехресні посилання, але без зміни відомостей в основних таблицях.

Як створити запит на вибірку в Access за допомогою Конструктора

Для прикладу спробуйте створити список співробітників із зазначенням їх посади, окладу і надбавки виходячи зі стажу роботи. Природно, що цікавлять дані будуть знаходитися в різних таблицях, тому вибірка в Access буде здійснюватися з пов'язаних об'єктів. Для створення слід виконати такі дії:

На малюнку чітко видно, що дані не впорядковані, і переглядати їх в такому вигляді не дуже зручно. При формуванні запиту цей параметр слід мати на увазі. Для цього в бланку слід використовувати рядок під назвою «Сортування», за допомогою якої можна вибудувати список по надбавок починаючи від максимальної, закінчуючи мінімальним значенням.

Крім цього можна створювати в Access запит на вибірку з умовою: для цього в поле «Умови відбору» слід задати необхідний параметр (Певну суму окладу, переглянути надбавки тільки у інженерів проекту та ін).

літерали - конкретні значення, сприймаються Access так, як вони записані. Як литералов можуть бути використані числа, текстові рядки, дати. Текстові рядки полягають в подвійні лапки, дати - в знаки (#). Наприклад, 567, "Інформатика", # 1-січня-99 #.

константи - які не змінюються значення, які визначені в Access, наприклад, True, False, Так, Ні, Null.

ідентифікатор - посилання на значення поля, елемента управління або властивості. Ідентифікаторами можуть бути імена полів таблиць, форм, звітів і т. Д., Які повинні полягати в квадратні дужки. Як правило, Access проводить автоматичну підстановку дужок.

У багатьох випадках посилання на конкретне значення має вказувати точне його місце розташування в ієрархії об'єктів бази даних, починаючи з об'єкта верхнього рівня. Якщо необхідно вказати посилання на поле в конкретній таблиці, формі, звіті, то перед ім'ям поля ставиться ім'я таблиці, форми, звіту, також укладену в квадратні дужки і відокремлене від імені поля оклику. Наприклад, посилання на поле в таблиці прийме вид: [Ім'я таблиці]! [Ім'я поля], А посилання на властивість DefaultValue елемента управління Дата народження в формі СТУДЕНТ: Forms! [СТУДЕНТ]! [Дата народження] .DefaultValue

Операторами порівняння і логічними операторами , Використання яких допускається в вираженні умови відбору, є:

Ці оператори визначають операцію над одним або декількома операндами.

Якщо вираз в умови відбору не містить оператора, то за замовчуванням використовується оператор \u003d.

текстові значення, Якщо вони містять прогалини або знаки пунктуації, вводяться в подвійних лапках. В іншому випадку лапки можна не вводити, вони будуть додані автоматично.

допускається використання операторів шаблону - зірочка (*) і знак питання (?).

Оператор Between дозволяє задати інтервал для числового значення і дати. наприклад:

Between 10 And 100

задає інтервал від 10 до 100; можна задати інтервал дат:

Between # 01.01.1997 * And # 31.12.1997 *

Оператор in дозволяє виконати перевірку на рівність будь-якому значенню зі списку, який задається в круглих дужках. наприклад:

In ( "Математики"; "Інформатики"; "Історії")

оператор Like дозволяє використовувати зразки, що використовують символи шаблону, при пошуку в текстових полях. наприклад: Like "Іванов *"

Сформувати умова відбору можна за допомогою будівника виразів. Перейти у вікно будівник виразів можна, натиснувши кнопку побудувати на панелі інструментів конструктора запитів або вибравши команду побудувати в контекстно-залежному меню. Курсор миші повинен бути встановлений попередньо в поле рядка введення умови відбору.

Логічні операції

Логічні операції "І", "АБО"

Умови відбору, задані в одному рядку, зв'язуються за замовчуванням за допомогою логічної операції і, задані в різних рядках - за допомогою логічної операції або. Ці операції можуть бути також задані явно в вираженні умови відбору за допомогою операторів and і or відповідно.

обчислювані поля

У запиті над полями можуть проводитися обчислення. Результат обчислення утворює обчислюване поле в таблиці запиту. При кожному виконанні запиту здійснюється обчислення з використанням поточних значень полів.

Крім імен полів, в виразах обчислюваних полів можуть використовуватися літерали і вбудовані функції. В результаті обробки виразу може виходити тільки одне значення.

Вираз вводиться в бланк запиту в порожнє поле рядка поле. Після натискання клавіші або переміщення курсору в інше поле рядка, перед вираженням в цьому полі рядка додається ім'я поля Виражeніе N. N - ціле число, яке збільшувалося на одиницю для кожного нового створюваного обчислюваного поля в запиті. Ім'я обчислюваного поля, що стоїть перед виразом, відділяється від нього двокрапкою. наприклад:

Вираз !: [Ціна] * [Кількість] де Ціна і Кількість - імена полів.

Ім'я обчислюваного поля - вираження1 стає заголовком стовпця в таблиці з результатами виконання запиту. Це ім'я можна змінити.

Для обчислюваних полів, як і для будь-яких інших, допускається сортування, завдання умов відбору і розрахунок підсумкових значень.

вбудовані функції

В Access і VBA визначено приблизно 150 вбудованих функцій, Які можна використовувати в обчислюваних полях і умовах відбору. Перерахуємо деякі згруповані за призначенням функції.

Функції дати і часу. Використовуються при обробці дат і часу в полях і літералах. Повертають дату і час повністю або частково (рік, місяць, день), наприклад, функція Date формує поточну дату, Функція Month виділяє місяць із значення поля, що містить дату.
Функції обробки тексту. Використовуються при роботі з символьними рядками.
Функції перетворення типу даних. Надають можливість користувачеві задавати тип даних для числових значень, що дозволяє уникнути підбору найбільш відповідного типу даних системою.
Математичні і тригонометричні функції. Виконують операції над числовими значеннями, Які неможливо виконати за допомогою стандартних арифметичних операторів.
фінансові функції. подібно функцій Excel, Служать для розрахунку відсотка повернення з інвестицій, амортизаційних відрахувань, річний ренти і т. П.
Статистичні функції. Використовуються при роботі над полями підмножини записів для обчислення середнього значення, суми, мінімального, максимального значення.

Для запису виразу може бути використаний будівник виразів, який викликається кнопкою побудувати панелі інструментів.

Присвоєння користувальницьких імен обчислюваним полях

Користувач має можливість дати нову назву обчислюваному полю, використовуючи один з таких способів:

Зміна імені поля в запиті. У режимі конструктора запиту в бланку запиту замість Виявом введіть нове ім'я.
Зміна підпису поля у властивостях поля. Встановіть курсор на обчислюване поле в бланку запиту і відкрийте вікно властивості поля, Клацнувши правою кнопкою миші і вибравши в контекстному меню пункт властивості. У вікні властивості поля на вкладці загальні введіть потрібний текст підпису в рядок підпис поля.