Тема 9. Формування запитів за допомогою Майстра

1 Створення простого запиту

Майстер запитів дозволяє послідовно формувати бланк запиту, використовуючи готові шаблони, крім того, з'являється можливість створювати вирази без використання Будівника, що дуже зручно при створенні бази даних.

Відкриття Майстри запитів здійснюється натисканням на піктограму на вкладці "Створення" (Создание), після чого відкривається вікно "Новий запит" (Новый запрос) (рис. 1), в якому можна вибрати один з чотирьох варіантів створення запитів: Простий запит; Перехресний запит; Запит на пошук повторюваних записів; Запит на пошук записів, які не мають підлеглих (Простой запрос; Перекрёстный запрос; Запрос на поиск повторяющихся записей; Запрос на поиск записей, не имеющих подчинённых).

Рис. 1. Вікно для створення нового запиту за допомогою Майстра

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

Використовуючи Майстер запитів, виконаэмо наступні дії:

1. Відкриваємо "Новий запит" (Новый запрос) в Майстрі запитів та вибираємо рядок "Простий запит" (Простой запрос) (рис. 1), після чого натискаємо на кнопку ОК.
2. У вікні "Створення простих запитів" (Создание простых запросов) вибираємо спочатку таблицю "Особисті дані", з лівого вікна переносимо в праве вікно стрілкою  поля "Прізвище", "Доплати", після чого відкриваємо таблицю "Доплати" і переносимо вправо поле "Коефіцієнт доплати", далі відкриваємо таблицю "Надбавки за стаж", з якої вибираємо поля: "Стаж роботи співробітника" і "Надбавка" (рис.2).

Рис. 2. Збір полів з таблиць у вікні створення простих запитів

3. Наступним кроком (рис. 3), зробимо позначку проти слова "Підсумковий" (Итоговый) і натискаємо на кнопку .
4. У вікні "Підсумки" (Итоги) (рис.4) поставимо позначки в стовпці Sum навпроти "Коефіцієнт" (Коэффициент) і так само у полі "Підрахунок числа записів у Особисті відомості" (Подсчёт числа записей в Личные сведения), натисніть на кнопку ОК.

Рис. 4. Підготовка до обчислення підсумків у запиті

Рис. 5. Заповнення вікна "Підсумки"

5. Виконавши наступні кроки (вони не потребують коментарів), слід не забудути присвоїти ім'я запиту, наприклад, "Надбавки і сум_коеф доплат", після чого перевіряємо які результати будуть отримані в результаті виконання запиту (рис. 6).

Рис. 6. Результат виконання простого запиту, створеного в Майстр запитів

Слід зверніть увагу, що аналогічне завдання було розглянуто за допомогою Конструктора, але в даному випадку з'явилося поле "Count − кількість", в якому зазначено кількість видів доплат кожному співробітнику.

Поле "Sum" містить сумарне число коефіцієнтів за видами доплат.

2 Застосування перехресного запиту для отримання статистичних розподілів даних

У Access 2010 (2013) є можливість надати запитам, в яких використовуються групові операції, вид електронних таблиць.

Найменуваннями рядків і стовпців в них служать поля таблиці або проміжного запиту, якщо потрібно виводити дані з декількох таблиць. У створюваній таблиці, за допомогою перехресного запиту в комірках відображаються обчислювані статистичні дані (Sum − сума, Avg − середнє, Count − кількість, Max − максимальне значення, Min − мінімальне значення).

Результати перехресного запиту легко експортувати в Excel, використовувати для створення демонстраційних діаграм. Розглянемо на конкретному прикладі технологію створення перехресного запиту.

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

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

1. Створюємо новий запит за допомогою Майстра, в якості напрямку, виберіть рядок − Перехресний запит (Перекрёстный запрос).
2. У діалоговому вікні "Створення перехресних таблиць" (Создание перекрёстных таблиц) вибераємо запит − "Надбавки і сум_коеф доплат" (рис. 7), натисніть на кнопку.

Рис. 7. Вибір таблиці для організації перехресного запиту

3. У наступних діалогових вікнах слід наступні дії:

• вибираємо ім'я поля з таблиці запиту те, яке буде відображатися у рядках перехресного запиту (рис. 8), наприклад, поле − Надбавки;

Рис. 8. Вибір найменування рядків у новому запиті

• вказуємо найменування стовпців для таблиці (рис. 9), поле − Стаж роботи;

Рис. 9. Вибір поля для заголовків стовпців

• визначаємо, які обчислення необхідно провести для кожної комірки на перетині рядків і стовпців (у вікні "Функції" (Функции) вибираємо − Sum), у вікні "Доступні поля" (Доступные поля) вибираємо: Count − Особисті дані, і ставимо прапорець "Так" (Да) у вікні запитання Обчислити підсумкове значення для кожного рядка (рис. 10);

Рис. 10. Визначення виду представлення значень даних в комірках таблиці

• надаємо ім'я запиту, наприклад, Надбавки і сум_коеф доплат (рис. 11).

Рис. 11. Завдання імені запит

В області переходів з'явиться нове позначення перехресного запиту .

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

Рис. 12. Результати виконаного запиту, створеного за допомогою Майстра

3 Використання результатів перехресного запиту

Нагадаємо, що в групові дані входять ті, які в будь таблиці повторюються.

Отже, можна в якості осі категорій у запиті вибрати, наприклад, поле "Посада", а потім отримати розподіл надбавок за стаж за посадами співробітників.

Як було сказано раніше, перехресні запити дозволяють створювати досить добрий ілюстрований матеріал у вигляді гістограм не тільки всередині бази даних, але і при експорті в інші програми Windows.

3.1 Створення об'єкта бази даних у вигляді гістограми

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

Примітка. У Access 2013 відсутні засоби створення зведених діаграм і зведених таблиць.

1. Відкриваємо таблицю або запит, на основі якого буде створено гістограма.
2. На вкладці "Головна" (Главная) розкриваємо піктограму "Режим" (Режим) (у лівому кутку) або клацаємо правою кнопкою миші по ярлику відкритої таблиці, і вибераємо рядок , після чого буде відкрито вікно для конструювання діаграми і вікно зі списком полів для діаграми (рис. 13).

Рис. 13. Вікно з заготівлею для конструювання діаграми

3. На полі побудови діаграми перетягуємо послідовно мишкою значки з позначенням даних , і так далі заповнюємо назви осей.
4. Закриваємо об'єкт, який буде збережений в запиті під тим же ім'ям. Тепер при відкритті запиту, щоб побачити гістограму досить клацнути правою кнопкою миші по ярлику і в меню натиснути на рядок .

3.2 Експорт результатів в Excel

Більш виразною діаграма виходить в додатку MS Excel, тому, буде потрібно здійснити операцію експорту створеного запиту в Excel, побудувати там діаграму, і зберегти у вигляді окремого файлу.

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

Для експорту результатів перехресного запиту, необхідно виконати наступні дії:

1. Клацаємо правою кнопкою миші по найменуванню запиту (в розглянутому прикладі, це ), виберіть в меню, рядок : а потім .
2. У діалоговому вікні "Експорт - електронна таблиця Excel" (Экспорт – электронная таблица Excel) натискаємо на кнопку Обзор..., знаходимо місце для збереження файлу, надайте йому ім'я, наприклад, D:\Access\Надбавки і сум_коеф доплат_перекрестный.xlsx, вказуємо параметри експорту (рис. 14) і натискаємо на кнопку ОК.

Рис. 14. Вибір параметра експорту даних

3. Форматуємо таблицю, будуємо діаграму (рис. 15), зберігаємо файл.

Рис. 15. Діаграма, побудована в Excel після імпорту даних із запиту

4 Питання для самоконтролю

1. Чим Майстер побудови запитів відрізняється від Конструктора запитів ?
2. При створенні простого запиту в Майстрі, чи можна використовувати кілька таблиць ?
3. При створенні простого запиту, як будуть відображатися результати, якщо вибрати режим підготовки докладного звіту?
4. Які обчислювальні операції можна задавати в Майстрі при складанні підсумкового звіту ?
5. У якому вигляді відображаються електронна таблиця при створенні перехресного запиту ?
6. Чи можно при створенні перехресного запиту вибирати дані з декількох таблиць ?
7. Що слід зробити, щоб у перехресному запиті відображалися саме ті поля, на базі яких створюється результуюча таблиця ?
8. Який вплив на результати запиту надає вибір найменування полів для рядків і стовпців ?