Сортувати таблицю по декількох полях має сенс тільки тоді, коли в поле, по якому сортуються записи в першу чергу, є багато повторюваних значень. Сортування по другому полю буде проводитися вже тільки серед цих записів.
Якщо сортування по двох полях усе-таки не дає повного упорядкування записів, то можна вказати поля, по яких сортування буде здійснюватися в третю чергу і т.д.
Рис. 6. Діалогове вікно "Поиск и замена"
Пошук даних
Зі збільшенням кількості записів пошук визначеної інформації ускладнюється. MS Access спрощує цей процес. Для проведення пошуку в таблиці потрібно відкрити цю таблицю, перейти на будь-який запис у поле, по якому передбачається вести пошук і виконати команду Правка/Найти. З'явиться діалогове вікно Поиск и замена (Рис. 6).
У поле Образец потрібно ввести шукане значення. У поле Совпадение можна установити значення Поля целиком. У цьому випадку Access знаходить тільки ті записи, значення яких цілком збігається із шуканим. Можна провести пошук, вибравши значення з початку поля чи С какой-либо частью поля зі списку Совпадение. Ці способи пошуку самі повільні, але і самі надійні, тому що не вимагають повної відповідності шуканого значення заданому. У поле Просмотр потрібно вказати, з якого запису вести пошук: Вверх/вниз від поточної чи по всіх записах таблиці.
За замовчуванням регістр при пошуку не враховується, але якщо при пошуку потрібно враховувати і спосіб написання потрібно включити опцію С учетом регистра. Якщо потрібно провести пошук не тільки в поточному полі, а у всій таблиці, то варто виключити опцію Только в текущем поле.
Розширити коло пошуку можна, застосувавши символи підстановки (аналогічно як узагалі у всіх програмах для Windows і не тільки):
* - заміняє будь-яка кількість символів;
? - заміняє тільки один символ.
Наприклад, при пошуку "за зразком" *ова будуть знайдені усі прізвища, що закінчуються на ова: Петрова, Іванова, Смирнова.
Процес пошуку запускається натисканням кнопки Найти. При виявленні необхідного об'єкта Access виділяє його, а в рядку стану з'являється повідомлення Образец поиска обнаружен. Натиснувши кнопку Найти далее можна знайти наступні записи, що задовольняють умовам пошуку.
При необхідності знайдені значення можна замінити на які-небудь інші. Цей процес аналогічний пошуку, але викликається командою Правка/Заменить. Ця команда відкриє діалогове вікно Замена дуже схоже на вікно пошуку. У поле Образец указується замінний об'єкт, а в поле Заменить - нове його значення. Програма починає виконувати заміну після натискання кнопки Найти далее. Вона знаходить і маркірує підлягаючий заміні об'єкт, але заміну не робить. Підтвердити заміну дозволяє кнопка Заменить, а скасувати її і продовжити пошук - кнопка Найти далее.
Сферу пошуку і заміни можна обмежити за допомогою опцій С учетом регистра і Только поле целиком. Якщо користувач абсолютно упевнений у необхідності заміни всіх шуканих об'єктів, можна скористатися кнопкою Заменить все.
6. Порядок виконання завдань практичних занять №3 – №4.
6.1. У режимі Конструктора таблиці Клієнти зробити в її структурі наступні зміни:
· додати поля Місто, Адреса, Телефон;
· для поля Місто у властивості Значение по умолчанию задати значення: Дніпропетровськ.
· для поля Знижка ввести умову на значення: >=0 AND <=20 і задати відповідне повідомлення про помилку.
6.2. Дані в поля Місто, Адреса, Телефон вводити в режимі таблиці.
Відредагувати значення в поле ТипКлієнта, використовуючи список значень, створений Мастером подстановок.
Для декількох записів використовувати значення за замовчуванням у поле Місто.
В одній із записів спробувати внести в поле Знижка значення, більше 20.
6.3. Викликати вікно для зміни схеми даних. Сховати одну з таблиць, включених у схему даних (наприклад, таблицю ТипКлієнта). Потім відобразити всі прямі зв'язки.
6.4. Змінити макет таблиці Клієнти:
· зафіксувати стовпці Прізвище і КодКлиента.
· поле Місто поставити після поля По батькові;
· сховати стовпці Адреса, Телефон;
· залишити для стовпців тільки вертикальну сітку;
· установити довільно колір тла для записів;
· змінити шрифт для записів таблиці на курсив.
6.5. Відсортувати таблицю Клієнти по наступних ознаках:
· зростанню в поле Прізвище;
· убуванню в поле Дата народження;
· убуванню в поле Знижка і зростанню у полі Прізвище.
6.6. Знайти в таблиці Клієнти всі записи, що задовольняють наступним умовам:
· клієнти, чиї прізвища починаються з визначеної букви;
· клієнти, дні народження яких у грудні 1980 року;
· знайти записи для клієнтів з визначеною знижкою і замінити для них її значення. Наприклад, знижку 10 замінити на 12.
7. КОНТРОЛЬНІ ПИТАННЯ
1) Що таке проект бази даних?
2) Як додати/видалити поле в структурі таблиці?
3) Як змінити шрифт записів і колір фону в таблиці?
4) Як змінити ширину стовпця і висоту рядка в таблиці?
5) Як відсортувати записи по одному полю?
6) Як відсортувати записи по декількох полях?
7) Як зробити пошук і заміну даних у Access?
8) Як використовувати шаблони при пошуку даних?
Практичне ЗАНЯТТЯ № 2.9 (2 год.)
СТВОРЕННЯ ЗАПИТІВ НА ВИБІРКУ.
1. ЦІЛЬ РОБОТИ
Освоїти принципи створення запитів на вибірку.
2. ЗАДАЧІ РОБОТИ
Створити запити-вибірки й одержати зведення про дані з використанням різних критеріїв.
3. ЗМІСТ РОБОТИ
· Вивчити основні теоретичні положення (п. 5). Перейти до виконання роботи на комп'ютері згідно п.6.
· Створити прості запити до раніше створених таблиць.
· Створити запити з полями, що обчислюються.
4. ВИМОГИ ДО ЗВІТУ
Звіт про пророблену роботу повинний містити файл створеної БД на дискеті.
5. ЗАГАЛЬНІ теоретичні ПОЛОЖЕННЯ
Поняття запиту
Основна функція БД — видавати відповіді на запити, що надходять.
Якщо СУБД не уміє відповідати на запити, то всі її інші функції безглузді. Зрозуміло, що можна сказати і про кваліфікацію користувача, якщо він не вміє задавати питання до бази даних.
Таблиці - це основні об'єкти Access, але основне призначення баз даних – це давати відповіді на запити користувачів. Запит у якомусь ступені аналогічний фільтру, тому що одна з основних функцій запиту - знайти і відібрати потрібні дані в базі даних. Однак вони дозволяють виконувати і багато інших дій.
Запити використовуються для перегляду, аналізу і зміни даних однієї чи декількох таблиць. Наприклад, можна використовувати запит для відображення даних з однієї чи декількох таблиць і відсортувати їх у визначеному порядку, виконати обчислення над групою записів.
Запити можуть служити джерелом даних для інших об'єктів Access: форм, звітів. Сам запит не зберігає дані, але дозволяє вибирати дані з таблиць і виконувати над ними різні операції.
ЗАПИТ - ЦЕ ЗВЕРТАННЯ ДО БАЗИ ДАНИХ ДЛЯ ПОШУКУ ЧИ ЗМІНИ ДАНИХ, У ВІДПОВІДНОСТІ с ЗАДАНИМи КРИТЕРІЯМи
У Microsoft Access існує кілька видів запитів:
· Запити на вибірку - Запит на вибірку є найбільше часто використовуваним типом запиту. Запити цього типу повертають дані з однієї чи декількох таблиць і відображають їх у виді таблиці, запису в який можна обновляти (з деякими обмеженнями). Запити на вибірку можна також використовувати для угруповання записів і обчислення сум, середніх значень, підрахунку записів і перебування інших типів підсумкових значень.
· Запити на зміну, що дозволяють модифікувати дані в таблицях (у тому числі видаляти, обновляти і додавати запису);
· Запити на створення таблиці, що дозволяють створити нову таблицю на основі даних однієї чи декількох існуючих таблиць,
· Запит з параметрами — це запит, що при виконанні відображає у власному діалоговому вікні запрошення ввести дані, наприклад, умова для повернення записів чи значення, що потрібно вставити в поле. Можна розробити запит, що виводить запрошення на введення декількох одиниць даних, наприклад, двох дат. Потім Access може повернути всі записи, що приходяться на інтервал часу між цими датами.
· Запити на автопідстановку, що дозволяють автоматично заповнювати поля для нового запису;
· Перехресні запити -це спеціальні запити підсумкового типу. Такі запити дозволяють побачити значення, що обчислюються, у виді перехресної таблиці, що нагадує електронну таблицю. Перехресний запит служить для одержання аналітичного зрізу інформації.
· Запити до сервера, що дозволяють здійснювати вибірку даних із сервера (випадок реалізації мережного додатка – мережної бази даних);
Ми не будемо розглядати всі типи запитів, а познайомимося тільки з основними.
Запит на вибірку містить умови відбору даних і повертає вибірку, що відповідає зазначеним умовам, без зміни даних, що повертається.
Основна подібність між запитами на вибірку і фільтрами полягає в тім, що в тім і іншому випадку виробляється витяг підмножини записів з базової таблиці чи запиту.
Створення запиту
Для початку створення запиту треба відкрити базу даних, і, перейшовши на вкладку Запросы натиснути кнопку Создать. З'явиться вікно Новый Запрос для вибору способу побудови запиту (мал. 1).
Мал. 1 Діалогове вікно Новый Запрос
Конструктор - створює запит на основі порожнього бланка запиту.
Простой Запрос - створює простий запит з визначених полів.
Перекрестный запрос - створює запит, дані в який мають компактний формат, подібний до формату зведених таблиць ("сводных таблиц" – рос.) у Excel.
Повторяющиеся записи – створює запит, що вибирає записи, що повторюються, з таблиці чи простого запиту.
Записи без подчиненных - створює запит, що вибирає з таблиці записи, не зв'язані з записами з іншої таблиці.
При виборі Конструктора через діалогове вікно Добавление таблицы (мал. 2) додаються імена таблиць у вікно конструктора запиту
Мал. 2. Вікно Добавление таблицы
Вікно Добавление таблицы складається з трьох вкладок, що містять переліки об'єктів, пропонованих програмою для проектування запиту: Таблицы, Запросы і Таблицы и запросы. При виборі вкладки Таблицы необхідно виділити потрібну нам таблицю з запропонованого списку і за допомогою кнопки Добавить можна додати кілька таблиць. Наприклад, на мал. 2 обрана таблиця Клієнты.
 |
Імена таблиць повинні бути представлені у вікні Конструктора запросов (Мал. 3).
Рис. 3. Вікно конструктора запитів. Приклад введення умови.
5.2.1. Вікно "конструктора запроса"
Вікно "конструктора запроса" (мал. 3) розділено на дві частини. У верхній частині знаходяться вікна таблиць зі списками полів. Ім'я кожної таблиці відображається в рядку заголовка такого вікна. На мал.3 представлені таблиці Клієнты та Операції.
Нижня частина є Бланком запроса, або, як його називають, QBE – областю (Q uery b y E xample – Запит за зразком). Тут указуються параметри запиту і дані, які потрібно відібрати, а також визначається спосіб їхнього відображення на екрані.
(Для переміщення з верхньої панелі вікна в нижню і назад використовується клавіша F6).
5.2.2. Включення полів у запит.
У запит не слід включати всі поля обраних таблиць. Додати потрібні поля в бланк запиту можна шляхом перетаскування їхніх імен зі списку, що знаходиться у верхній частині вікна конструктора в рядок бланка Поле. Ще один спосіб – подвійний щиглик по імені поля.
Наприклад, на мЯяал. 3 у бланк запиту включені поля Прізвище, Дата операції, Код Операції, Сума операції з таблиць Клієнт та Операції.
Примітка: Якщо був установлений прапорець Имена таблиц з меню Вид, то в другому рядку бланка QBE вийде на екран ім'я таблиці, з якої обране поле (див. мал. 3). У рядку Вывод на экран прапорцем позначаються ті поля, що повинні бути виведені на екран.
У загальному випадку поля, що вводяться в наборі записів запиту, успадковують властивості, задані для відповідних полів таблиці.
Можна визначити інші значення властивостей, виконавши команду Свойства з меню Вид: Описание (текст, що містить опис об'єкта), Формат поля (представлення даних на екрані), Число десятичных знаков (для числових даних, Маска ввода, Подпись (заголовок стовпця).
Видалити поле з бланка запиту можна клавішею Delete чи через меню Правка командою Удалить. Щоб видалити таблицю, варто маркірувати її у верхній частині вікна конструктора запиту, виконавши щиглик по імені, і натиснути Delete в меню Правка чи командою Удалить.
5.2.3. Установка критеріїв відбору записів.
При створенні запиту можна задати критерії, унаслідок чого по запиту буде здійснений відбір тільки потрібних записів.
Щоб знайти записи з конкретним значенням поля, потрібно ввести це значення в дане поле в рядку бланка QBE Условие отбора
(див. мал. 3).
Критерії, встановлювані в QBE – області,
повинні бути укладені в лапки.
Якщо ACCESS ідентифікує введені символи як критерії відбору, то укладає їх у лапки автоматично, а якщо ні, те повідомляє про синтаксичну помилку.
Наприклад, як показано на мал. 3, побудований запит, по якому з даних по сумі операції будуть обрані прізвища клієнтів, дати і тип операцій, сума яких повинна бути більше заданого значення 30000.
5.3. Види критеріїв.
Для створення запиту з декількома критеріями користаються різними операторами.
5.3.1. Логічна операція OR (ИЛИ – рос.).
Можна задати кілька умов відбору, з'єднаних логічним оператором or, для якогось поля одним із двох способів:
1) можна увести всі умови в один осередок рядка Условие отбора, з'єднавши їх логічним оператором OR. У цьому випадку будуть обрані дані, яки задовольняют хоча б одній з умов.
Наприклад, умова: 4 or 5
відповідає тому, що будуть обрані значення: 4 або 5.
2) увести другу умову в окремий осередок рядка. І якщо використовується кілька рядків, щоб запис був обраний, досить виконання умов хоча б в одному з рядків, як, наприклад, показане на мал. 4.
Мал. 4. Приклад запису умови з використанням оператора OR.
При такому записі умови також будуть обрані клієнти для яких установлена знижка 10 чи 12 відсотків.
5.3.2. Логічна операція AND (и – рос.)
Логічна операція AND використовується в тому випадку, коли повинні бути виконані одночасно обоє умови і тільки в цьому випадку запис буде обрана.
Наприклад, записавши умову
>5 and <15
будуть обрані тільки ті клієнти, для яких знижки знаходяться в інтервалі (5;15).
Щоб об'єднати кілька умов відбору оператором and, необхідно привести їх в одному рядку.
Наприклад, на Мал. 5 показано, як можна задати умову для вибору прізвищ клієнтів, що проживають у місті Дніпропетровську, що мають знижку тільки 10%.
Мал. 5. Приклад запису умови з використанням оператора AND.
Виключити групу даних зі складу записів, що аналізуються запитом, дозволяє наступний критерій
< > 10 (не дорівнює)
У цьому випадку можна не використовувати лапки.
Оператори AND та OR застосовуються як окремо, так і в комбінації. Необхідно пам'ятати, що умови зв'язані оператором AND виконуються раніш умов, об'єднаних оператором OR.
Оператор Between
Оператор Between (між) дозволяє задати діапазон значень, наприклад:
Between 10 and 20
5.3.4. Оператор In дозволяє задавати список значень для порівняння. Наприклад:
In (“перший”,”другий”,”третій”)
Оператор Like
Оператор Like корисний для пошуку зразків у текстових полях, причому можна використовувати шаблони:
* — позначає будь-яку кількість (включаючи нульову) символів;
? — будь-який одиночний символ;
# — указує що в даній позиції повинна бути цифра.
Наприклад: для вибору прізвища, що починається з букви П и с закінченням на “ ов ” можна записати
like П*ов
5.3.6. Оператори для дати і часу.
Можна ввести дату і час, при цьому значення, що задають дату, повинні бути укладені між символами #. Наприклад:
#10 травня 1998#
>#31.12.96#
У Access використовується ряд інших функцій, що допоможуть задати умови відбору для дати і часу, наприклад:
· Day(дата) – повертає значення дня місяця в діапазоні від 1 до 31
· Month(дата) – повертає значення місяця року в діапазоні від 1 до 12
· Year(дата) – повертає значення року в діапазоні від 100 до 9999
5.4. Сортування даних у запиті.
Дані можна упорядкувати по зростанню чи по убуванню. Для упорядкування відібраних записів доцільно скористатися можливостями сортування самого запиту в рядку Сортировка вікна конструктора запиту (див. мал. 5).
Поля що обчислюються.
Можна
· задати обчислення над будь-якими полями таблиці
· зробити значення, що обчислюється, новим полем у запиті.
Для цього в рядку Поле бланка QBE уводиться формула для обчислення, причому імена полів беруть у квадратні дужки.
Наприклад: =[Сума]/5,05
У вираженнях можна використовувати наступні оператори:
· арифметичні: * множення; + додавання; - віднімання; / ділення; ^ зведення в ступінь;
· з'єднання частин тексту за допомогою знака &, наприклад:
=[Прізвище]&“ “&[Ім'я]
(Пробіл у лапках уставлений для того, щоб текст не був злітним)
Прімітка. При створенні виражень для полів, що обчислюються, можна використовувати ПостроителЬ выражений. Для цього потрібно клацнути по порожньому полю в бланку QBE, а потім по кнопці Построить - 
на панелі інструментів, при цьому відкриється вікно Построитель выражений (мал. 6).
Мал. 6. Вікно Построителя выражений.
У верхній частині вікна розташована порожня область уведення, у якій створюється вираження. Можна самим увести вираження, але простіше використовувати різні кнопки, розташовані під областю введення.
Для початку потрібно клацнути двічі в лівому списку по папці Таблицы, і вибрати саму таблицю, а потім вибрати необхідне поле і клацнути по кнопці Вставить.
Усі імена об'єктів, з яких будується вираження для обчислення, укладені в квадратні дужки, причому перед ім'ям поля може стояти знак (!) поділяючий його й ім'я таблиці.
Помилку при складанні вираження можна скасувати, клацнувши по кнопці Отмена. У результаті зроблених дій вийде вираження, наприклад як показане на малюнку 6 задане вираження для поля, що обчислює суму операцій у доларах.
Якщо клацнути по кнопці ОК, то отриманий результат буде перенесений у бланк QBE.
5.6. Групові операції у запитах (ПІДСУМКОВІ запити).
У запитах, створених у режимі Конструктора (як і у запиті, створеним у з допомогою Майстра) можна виконувати групову обробку даних. Підсумкові запити значно відрізняються від звичайних. У них поля поділяються на два типи:
· поля, по яких здійснюється угруповання даних;
· поля, для яких проводяться обчислення.
Для складання підсумкового запиту, знаходячись у режимі конструктора, необхідно натиснути кнопку 
- Груповые операции на панелі інструментів чи скористатися командою Груповые операции з меню Вид. У результаті чого в бланку запиту з'явиться рядок Груповая операция. Якщо для відповідного поля зі списку вибрати функцію Группировка (Мал. 7), те при виконанні запиту групуються за значеннями в цьому полі, але обробка (підсумок) не виконується.
Угруповання в підсумковому запиті виконується тільки по одному полю. У всіх інших полях уводяться підсумкові функції.
Мал. 7. Рядок Групповая операция в бланку QBE.
Access надає ряд функцій, що забезпечують виконання групових операцій. Можна задати потрібну функцію, набравши на клавіатурі її ім'я в рядку Групповая операция вибравши її зі списку, що розкривається. Основні групові функції, якими можна скористатися:
· SUМ - обчислює суму всіх значень заданого поля (для числових чи грошових полів), відібраних запитом;
· AVG - обчислює середнє значення в тих записах визначеного поля, що відібрані запитом (для числових чи грошових полів);
· MIN - вибирає мінімальне значення в записах визначеного поля, відібраних запитом;
· MAX - вибирає максимальне значення в записах визначеного поля, відібраних запитом;
· COUNT - обчислює кількість записів, відібраних запитом у визначеному полі, у яких значення даного поля відмінні від нуля;
· FIRST - визначає перше значення в зазначеному полі записів;
· LAST - визначає останнє значення в зазначеному полі записів.
Виконання запиту.
Готовий запит виконується після щиглика по кнопці 
панелі інструментів у режимі Конструктора запросов чи при активізації команди Запуск із меню Запрос.
5.8. Запити до декількох таблиць.
Запити можна створювати для відбору даних як з однієї, так і з декількох таблиць. Запити до декількох таблиць виконуються аналогічно запитам до одно-табличних БД із тією лише різницею, що у вікно конструктора запиту додаються всі таблиці, дані яких потрібні в запиті. При цьому необхідно враховувати наявність зв'язків між таблицями.
ПОРЯДОК ВИКОНАННЯ РОБОТИ
1. Створити простий запит - вибрати декілька полів з таблиці Клієнти.
2. За допомогою Конструктора створити запити, що задовольняють умовам:
· єдине значення типу клієнта;
· два різних значення типу клієнта;
· прізвища клієнтів, що починаються з визначеної букви (використовувати шаблони);
· прізвища клієнтів, що закінчуються на “ ов ”;
· прізвища клієнтів одного міста й одного типу;
· прізвища й імена клієнтів, що проживають в одному з міст чи з однією знижкою;
· прізвища клієнтів, у яких операції більше 40000 гривень;
· прізвища клієнтів, що відносяться до типу "випадковий" і операції яких у межах від 20000 до 50000 гр.
Примітка: У запиті повинні бути включені поля Прізвище, Ім'я, По батькові і ті поля, де вводяться критерії.
3. Після того, як був заданий критерій для запиту, запит потрібно виконати і зберегти під ім'ям, що підходить за змістом.
Для запитів з полем типу Дата / Время додати поле Прізвище, Дата народження і вибрати записи, що задовольняють умовам:
· дата більше 01.01.85;
· дата в інтервалі значень і заданий тип операції;
· прізвища та імена клієнтів, що народилися в 80-х роках;
· обчислити вік клієнтів;
· прізвища та імена клієнтів, що народилися в першій половині місяця;
4. Створити підсумковий запит:
· залишити в запиті поля Сума операції, ТипКлієнта, КодКлієнта, обчислити максимальне значення суми операції для кожного типу клієнта і підрахувати кількість клієнтів по типу операції (використовуючи Count).
· Запит з полями, що обчислюються:
· включити в запит поле, що обчислюється, що є результатом зчеплення текстових полів Прізвище, Ім'я, По батькові. Назвати поле, що обчислюється, як ПІПБ.
5. Створити запит, у якому використовуються поля з двох раніше створених і зв'язаних таблиць, задавши йому ім'я " Запит для 2-х таблиць "
· згрупувати по типу та обчислити середню суму операцій для кожного клієнта.
7. КОНТРОЛЬНІ ПИТАННЯ
1) Що таке запит?
2) Назвіть елементи вікна конструктора запитів.
3) Що таке бланк QBE?
4) Де записуються умови вибору для запиту?
5) Як видалити таблицю з запиту?
6) Як видалити поле з запиту? Перелічите основні операторі, яки можна використати в запиті.
7) У чому розходження між операторами OR і AND?
8) Призначення підсумкових запитів.
9) Як обчислити суму значень заданого поля?
10) Як здійснити сортування записів у запиті?
11) Які види обчислень можна зробити в підсумкових полях?
Практичне ЗАНЯТТЯ № 2.10 (2 год.)
СТВОРЕННЯ І ВИКОРИСТАННЯ запитІВ - ДІЙ
1. ЦІЛЬ РОБОТИ
Придбати навички по роботі з даними бази за допомогою запитів-дій.
2. ЗАДАЧІ РОБОТИ
Створити і виконати різні типи запитів-дій.
3. ПОРЯДОК ВИКОНАННЯ РОБОТИ:
· Вивчити теоретичні положення п.5.
· Відкрити створену в попередніх роботах базу даних.
· Створити резервну копію таблиці.
· Обновити дані за допомогою запиту на відновлення.
