Лекція 24 Створення запитів і робота з ними

Запити використовують для вибирання тих записів, що задовольняють поставлену умову, а також для відно­влення, додавання і вилучення записів із таблиць, ство­рення нової таблиці на базі відібраних значень. За допо­могою запитів можна об'єднувати інформацію, вміщену в кількох взаємопов'язаних таблицях. Результатом виконання запиту є набір даних, зібраних у таблиці (Recordset). В об'єкті Recordset записи змінюють, дода­ють, вилучають.

Створення запитів

Існує кілька типів запитів, найчастіше з яких засто­совують запит на вибирання. Для його створення у вікні БД активізують команду Конструктор запросов групи Другие вкладки Главная. Його можна створювати також за допомогою майстрів створення (команди Мастер запросов — Простой запрос, Перекрестньїй запрос та ін.). Унаслідок цих дій на екрані дисплея з'яв­ляється вікно Добавление таблицьі (рис. 6.15), в якому вибирають потрібну таблицю (таблиці).

Якщо, наприклад, потрібно створити запит на базі значень таблиці «Інформація» і таблиці «Дані_для_роз- рахунку», їх виділяють (при натиснутій клавіші Ctrl) і активізують кнопки Добавить та Закрьіть. Унаслідок цього з'являється вікно зі стандартним іменем (Запросі, Запрос2 і т. д.), у верхній частині якого вміщено вибрані таблиці з переліком їхніх полів. Якщо зв'язок між клю­човими полями таблиць установлено, він автоматично відображається лінією об'єднання.

Нижня частина вікна містить бланк запиту, що скла­дається із стовпців. У першому рядку (Поле) мають бути визначені імена тих полів, що включаються в запит. Для вибору полів із таблиць слід виконати такі дії:

— установити курсор у стовпці рядка Поле (позиція 2 на рис. 6.23), розкрити список, вибрати з переліку потрібне поле (якщо потрібні всі поля таблиці, із перелі­ку полів вибирають значок «*»);

— виділити поле у переліку полів таблиць (позиція 1 на рис. 6.23). Якщо їх кілька, необхідно утримувати натиснутою клавішу Ctrl, клацнути клавішею миші на виділенні і, не відпускаючи її, «перетягнути» поля з вікна таблиці у стовпець рядка Поле. Після визначення всіх полів запит має вигляд, показаний на рис. 6.24.

Рис. 6.23. Створення запиту у вікні конструктора Створений запит можна переглянути у вигляді таблиці (рис. 6.23), активізувавши команду Режим таблицы у групі Режими вкладки Главная або ярлик Режим таблицы у рядку статусу.

Для автоматичного вирівнювання ширини стовпця відповідно до введених значень необхідно двічі клацну­ти клавішею миші на його правій межі.

Після закінчення роботи із запитом його закривають, активізувавши кнопку . Унаслідок цього на екрані дисплея з'являється вікно, в якому потрібно зазначити ім'я запиту і папку, де він зберігатиметься (наприклад, «Всі_значення»). Для збереження запиту можна також використати команди Сохранить как, Сохранить об'ьект как значка Кнопка «Office». Тоді на екрані дис­плея з'явиться вікно Сохранение (рис. 6.25), в якому вносять ім'я запиту.

Рис.6.25. Збереження запиту

Відбирання записів

за умовою і їх сортування в запитах

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

Наприклад, потрібно відібрати тільки ті записи, в яких сума кредиту не перевищує 30 000 грн. Для цього у вікні конструктора запиту в полі Условие отбора під відповідним іменем поля («Сума_кредиту») з клавіату­ри вводять умову <= 30000 (позиція 3 на рис. 6.26).

Якщо потрібно впорядкувати прізвища за алфаві­том від А до Я, у рядку Сортировка під іменем поля «Прізвище» встановлюють курсор, розкривають спи­сок і вибирають сортування по возрастанию (2).

Для визначення одного або кількох полів, непотрібних для виведення на екран дисплея, використовують переми­кач у рядку Вьівод на зкран (І) відповідного стовпця (наприклад, на рис. 6.26 — це стовпець «Код_кредиту»; перемикач неактивний).

Запит після сортування і відбирання записів відпо­відно до поставленої умови можна переглянути за коман­дою Режим таблицы у групі Режими вкладки Главная або після активізації ярлика Режим таблицы у рядку статусу. Значення запиту відображені на рис. 6.27.

Рис. 6.26. Запит із використанням умов і сортування у вікні конструктора

Рис. 6.27. Запит із використанням умов і сортування у режимі таблиці

У полях типу Дата/Время також можна використо­вувати умови, зокрема логічні (>, <, >=, <=, =), або опе­ратор Between.

Якщо, наприклад, потрібно відібрати тільки ті зна­чення запиту, які стосуються дат 2003 p., то умова матиме такий вигляд:

Between #01.01.03#and#31.12.03#.

Для текстових полів використовують оператор Like. Якщо, наприклад, потрібно відібрати клієнтів, у яких прізвища починаються на «С», то умова матиме такий вигляд:

Like «С*» або просто С*.

Створення розрахункового поля

У запиті можна створити нове поле, значення якого є розрахунковими.

Наприклад, потрібно розрахувати для кожного клі­єнта суму щомісячного погашення кредиту (без нараху­вання суми за видачу кредиту). Для проведення розра­хунку запит відкривають у конструкторі, курсор уста­новлюють у вільний стовпець рядка Поле, вводять формулу (позиція 1 на рис. 6.28), наприклад =Сума_кредиту/Термін_кредиту, натискають на клаві­шу Enter, унаслідок чого у полі з'являється такий вираз:

Выражение 1: [Сума_кредиту]/[Термін_кредиту].

Далі замінюють назву поля Выражение 1 на «Погашення кредиту» (рис. 6.28).

Рис. 6.28. Створення розрахункового поля в запиті

У випадку проведення складних розрахунків для спрощеного створення формул використовують інстру­мент Построитель выражений.

Наприклад, необхідно розрахувати суму щомісячно­го погашення кредиту. Для створення формули курсор установлюють у вільний стовпець рядка Поле й активі­зують команду (Построитель) у групі Настройка

запроса на вкладці Конструктор або команду Построить контекстного меню. Унаслідок виконання дій на екрані дисплея з'являється вікно Построитель выражений (рис. 6.29), верхня частина якого міститиме розрахун­кову формулу після її створення. Під цією частиною вікна є кнопки для введення знаків арифметичних дій (+, -, * тощо). їх можна також ввести з клавіатури. Лівий стовпець вікна включає об'єкти поточної БД, в ньому активізують об'єкт, для якого створюють форму­лу (тут — запит «Всі значення»). Другий стовпець показує поля вибраного ліворуч об'єкта.

Рис. 6.29. Створення формули у вікні Построитель выражений

Для створення потрібної формули у переліку полів другого стовпця вибирають потрібне поле («Сума_кредиту»), активізують кнопку Вставить (позиція 1 на рис. 6.29) та знак «/», знову вибирають поле («Термін_кредиту») і натикають на кнопку Вставить. У верхній частині вікна з'являється потрібна формула, після чого активізують кнопку ОК.

У розрахункових полях під час роботи з десяткови­ми значеннями можна встановити певний формат поля. Для цього у вікні Конструктор виділяють потрібне поле, активізують команду Страница свойств (Стра­ница свойств) або команду Свойства контекстного меню і відкривають вкладку Общие (рис. 6.30). У рядку Формат поля із списку вибирають Фиксированный, у рядку Число десятичных знаков зазначають потрібну кількість або вибирають параметр Авто (за замовчуван­ням він дорівнює 2).

Рис. 6.30. Форматування числових значень

Запит можна зберегти під іменем «Розрахунок» для подальшої роботи з ним.

У запитах також використовують операцію форма­тування виводу значень. Для цього у вільному стовпцю рядка Поле активізують інструмент Построитель выра­жений і, вибравши функцію Format, вносять відповідні значення аргументів. Для виведення дати у вигляді «місяць-рік» ця функція матиме такий вигляд:

Рогта1;([Дата_отримання]; «mm-уу»),

а дата отримання кредиту виведеться у формі, напри­клад, «10-03».

Створення запитів інших типів

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

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

команду (Тип запроса: создание таблицы). Унаслідок цього на екрані дисплея з'являється вікно Создание таблицм (рис. 6.31), в якому вводять ім'я нової таблиці й місце її розташування (наприклад, у поточній базі даних), після чого активізують кнопку ОК.

Рис. 6.31. Створення нової таблиці на базі значень запиту

Далі виконують дії запиту, для чого активізують!

Команду (Выполнить) у групі Результати вкладки Конструктор. Унаслідок цього на екран дисплея з'яв­ляється вікно з повідомленням про кількість значень, які додаються в нову таблицю (рис. 6.32).

Дію закінчують, активізувавши кнопку Да.

Запити на вилучення. За їх допомогою вилучають із таблиці певні значення.

Наприклад, необхідно вилучити записи таблиці «Розрахунок» з інформацією про тих клієнтів, які отримали кредит терміном на 5 років (60 місяців). Спо­чатку потрібно створити запит на вибірку, яка містить усі поля потрібної таблиці (для додавання цих полів можна використати «*», «потягнувши» її в рядок Поле). Далі додають поле (поля), для значень якого поставлено умову (поле «Термін_кредиту»).

У групі Тип запроса вкладки Конструктор вибирають команду Щ (Тип запроса: удаление), після чого в рядку Условие отбора: потрібного поля створюють умову (позиція 1 на рис. 6.33), наприклад «60».

Рис. 6.33. Створення запиту на вилучення певних записів таблиці

Перед вилученням записи можна переглянути (рис. 6.34), активізувавши команду Режим таблицьі у групі Результати вкладки Конструктор або ярлик режимів перегляду Режим таблицьі у рядку статусу.

Рис 6.34. Перегляд записів які вилучають із таблиці

Для виконання запиту вибирають команду вишить (Вьіполнить) у групі Результати вкладки Конструктор. Унаслідок цього на екрані дисплея з'являється вікно з повідомленням про кількість вилучених записів (для поданого прикладу — 4), після чого активізують кнопку Да. Отже, у таблиці «Розрахунок» уже не буде записів тих клієнтів, які отримали кредит терміном на 60 місяців.

Запити на додавання. їх використовують для дода­вання значень із однієї таблиці в іншу. Таблиці, з якими працюють запити, можуть мати різні структури, але типи їх полів повинні бути ідентичні. Для створен­ня такого запиту спочатку створюють запит на вибиран­ня, що містить поля таблиці, значення яких додаються. Далі у режимі конструктора в групі Тип запроса вкладки Конструктор вибирають команду (Тип запроса: добавление). На екрані дисплея з'являється вікно Добавление (рис. 6.35). У ньому в полі имя таблицьі: визначають ім'я таблиці, в яку потрібно додати значен­ня, і натискають на кнопку ОК.

Рис. 6.35. Визначення таблиці, в яку додають значення

Після цього активізують команду (Вьіполнить) у групі Результати вкладки Конструктор. З'являється вікно, в якому виводиться повідомлення про кількість записів, які додаються. Після натискання на кнопку Да запит виконується, у визначену таблицю додаються записи.

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

Наприклад, потрібно зменшити значення ставки за кредитним договором у 1,2 раза. Для того щоб не зміни­лися значення базової таблиці «Дані_для_розрахунку», попередньо роблять її копію у вкладці Таблицн під іме­нем «Дані». Зберігають запит під іменем «Оновлення» і у групі Тип запроса вкладки Конструктор вибирають команду (Тип запроса обновление). У запит додається рядок Обновление, в якому під полем «Став- ка_за_кредитним_договором» за допомогою інструмента Построитель вьіражений створюють вираз для розрахун­ку ([Ставка_за_кредитним_договором]/1,2) (позиція 1 на рис. 6.36). Далі виконують запит, активізувавши команду вмполниіь (Вьіполнить) у групі Результати вкладки Конструктор.

Рис. 6.36. Створення запиту на оновлення

З'являється вікно, в якому виводиться повідомлен­ня про кількість записів, які змінюються. Після нати­скання на кнопку Да запит виконується, в таблиці «Дані» значення поля «Ставка_за_кредитним_догово- ром» зменшуються в 1,2 раза.

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

а) поле (поля), яке буде використовуватися як заго­ловок рядків;

б) поле, яке буде використовуватися як заголовок стовпців;

в) поле для розрахунку підсумків, значення якого буде використовуватися для розрахунку вибраної функції.

Для власноручного створення перехресного запиту, який виводить для кожного коду кредиту максимальне значення суми кредиту, яка розраховується для термі­нів кредиту 60 та 120 місяців, спочатку задають запит на вибирання, який має поля: «Кодкредиту», «Сума_кредиту», «Термін_кредиту». Далі активізують команду В (Тип запроса: перекрестньїй) у групі Тип запроса вкладки Конструктор команди Запрос, Пере­крестньїй. У нижній частині вікна конструктора запиту в рядку Групповая операция для поля «Сума_кредиту» вибирають із переліку функцію Мах, в рядку Перекрестная таблица для поля «Код_кредиту» — Заголовки строк, для поля «Термін_кредиту» — Заголовки столбцов, для поля «Сума_кредиту» — Значение (рис. 6.37).

Рис 6.37. Створення перехресного запиту

Результат розрахунку можна побачити у режимі таблиці (рис. 6.38).

Рис 6.38.Перегляд перехресного запиту

Запити з параметрами. Це різновид запиту на вибирання. їх створюють з метою відбирання потріб­них записів. Критерій для відбирання вводять через параметри.

Наприклад, необхідно отримати інформацію лише за певним видом кредиту. Для створення такого запиту у вікні конструктора у рядку Условие отбора під потріб­ним полем («Вид_кредиту») вводять текст параметра в квадратних дужках ([Визначте вид кредиту]). Після цього запускають запит на виконання. З'являється діа­логове вікно, в якому вводять потрібне значення крите­рію на відбирання значень, наприклад «102» (рис. 6.39).

Рис. 6.39. Виконання запиту з параметрами

Запити з використанням групових операцій. За

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

Наприклад, необхідно знайти максимальну і міні­мальну суму кредиту за видами кредитів. Об'єднання записів виконується за даними поля «Вид_кредиту», що має повторювані значення. Для створення такого запиту слід сформувати запит на вибирання, включити в нього потрібні поля, наприклад «Код_кредиту» і «Сума_кредиту» (це поле додають двічі, оскільки по­трібно розрахувати два підсумки).

Далі у вікні конструктора запитів активізують команду (Итоги), наслідком чого є поява рядка Групповая операция в нижній частині вікна запиту. Після встановлення у цей рядок курсора під конкрет­ним полем («Сума_кредиту») в ньому з'являється кноп- ка-список, активізувавши яку, можна вибрати потріб­ну функцію із переліку (наприклад, Міп). Аналогічні дії виконують і для введення другої функції (Мах) (рис. 6.40). У режимі таблиці можна переглянути отримані резуль­тати (рис.6.41).

Что делать, если нет взаимности? А теперь спустимся с небес на землю. Приземлились? Продолжаем разговор...

Что будет с Землей, если ось ее сместится на 6666 км? Что будет с Землей? - задался я вопросом...

ЧТО И КАК ПИСАЛИ О МОДЕ В ЖУРНАЛАХ НАЧАЛА XX ВЕКА Первый номер журнала «Аполлон» за 1909 г. начинался, по сути, с программного заявления редакции журнала...

Что вызывает тренды на фондовых и товарных рынках Объяснение теории грузового поезда Первые 17 лет моих рыночных исследований сводились к попыткам вычис­лить, когда этот...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: