Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

видача системної дати. Вирази будуються за правилами мови

Visual Basic for Application.

Є можливість виклику Будівельника виразів за допомогою

команди Побудувати, розташованого на панелі інструментів вікна

MS Access, або контекстного меню, його вікно показано на рисунку

3.6. Для використання Будівельника виразів необхідно встановити

курсор у те поле (наприклад, запиту), де має бути розміщений

вираз, і викликати його. Після цього можна вибирати та заносити у

спеціальне поле будь-які об’єкти, з яких складається формула. На

рисунку 3.6 показано перенесену в це спеціальне поле (зверху

вікна) функцію експоненти. Зрозуміло, що поле формули

необхідно ще редагувати, наприклад, занести в дужки функції exp()

аргумент замість слова “number”. Після закінчення роботи з

будівельником вираз автоматично переноситься в те поле, звідки

відбувався виклик.

Будівельником виразів можна користуватися з метою одержання

довідки, для цього необхідно відзначити його об’єкт (наприклад,

ім’я функції) і вибрати Довідку.

3.4.5 Мова запитів

Рисунок 3.6 – Вікно Будівельника виразів

Мову Structured Query Lanquage (SQL) розробив засновник баз

даних реляційного типу Е. Ф. Кодд (E. F. Codd) у 1970 році. Запит

являє собою команду, написану мовою SQL. Її можна складати

двома способами – використовувати візуальні засоби MS Access

або безпосередньо писати текст команд.

Наприкінці команди мовою SQL обов’язково ставиться крапка з

комою. Текст команди можна включати в текст модуля,

написаного мовою VBA. Засоби мови VBA, які дозволяють це

робити були розглянені вище в розділі 2.2.6 (об’єкт DoCmd). Під час

виготовлення запиту візуальним способом інтерпретатор Access

складає його скрипт у вигляді тексту. Для перегляду або

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

конструктора і виконати команду Вигляд–Режим SQL.

Сьогодні відомо багато версій мови SQL, MS Access 2000 може

працювати з SQL версії ANSI-89.

Розглянемо окремі команди мови SQL. Найбільш поширеною є

команда SELECT, вона має такий вигляд:

SELECT список імен полів запиту FROM ім’я джерела

INNER JOIN ім’я зв’язаного джерела ON ім’я поля = ім’я зв’язаного поля

WHERE ім’я поля, оператор, значення

GROUP BY список імен полів, у яких відбувається групування

HAVING умова відбору записів

ORDER BY ім’я поля, за даними якого записи запиту будуть посортовані;

Тут службові слова SELECT і FROM – обов’язкові. WHERE і

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

HAVING, WHERE не дозволяє використовувати ні стандартні функції

VBA, ні функції користувача. Якщо використовуються всі поля

джерела, то замість їх переліку у списку імен полів запиту вживається

символ зірочка. При сортуванні за зростанням використовується

слово ASC, за спаданням – DESC.

Наведемо декілька прикладів запиту.

Приклад 1. Скласти запит, який містить усі поля таблиці Kadry і

лише ті записи, яких поле Priz не містить букв “ен”:

SELECT * FROM Kadry WHERE NOT(Priz LIKE ‘*ен*’);

Приклад 2. Скласти запит, який містить усі поля таблиці Kadry і

лише ті записи, які містять прізвища Петренко або Дрозд:

SELECT * FROM Kadry WHERE Priz IN (‘Петренко’, ‘Дрозд’);

Приклад 3. Скласти запит, який містить усі поля і записи

таблиць Kadry і Oplaty, які мають однаковий код працівника:

SELECT * FROM Kadry, Oplaty

WHERE Kadry.Kod_p = Oplaty.Kod_p;

варіант цього запиту з командою SELECT має такий вигляд:

SELECT * FROM Kadry

INNER JOIN Oplaty ON Kadry.Kod_p = Oplaty.Kod_p;

Приклад 4. Скласти запит, який містить усі поля і записи

таблиці Kadry, поле Priz посортувати за зростанням, а поле Ім – за

спаданням:

SELECT * FROM Kadry ORDER BY Priz ASC, Ім DESC;

Приклад 5. Скласти запит, який містить два поля таблиці Kadry:

Priz і кількість повторень кожного прізвища:

SELECT Priz, COUNT(Priz) FROM Kadry GROUP BY Priz;

Команди SELECT можуть бути вкладеними, ступінь вкладеності

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

велику довжину. Проте, не варто захоплюватися надмірною

довжиною команд, бо це може спричинити виникнення труднощів

під час налагодження програми.

Команда INSERT служить для вставки записів у таблицю.

INSERT INTO ім’я таблиці (список імен полів) VALUES(список значень);

Приклад 7. Скласти запит, який додасть у таблицю Gazpr запис з

кодом газопроводу 4 і назвою “Україна”:

INSERT INTO Gazpr( Kodpr, Nazpr ) VALUES (4, 'Україна');

Якщо додаються всі поля, а це – найбільш поширений випадок, то

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

таблицю Oblik, яка має 4 поля: Kodpr, Kodpp, Kil i Data, за допомогою

такої команди:

INSERT INTO Oblik VALUES (4,3,5, to_date('09.12.26'));

Зауважимо, що дата тут записана в міжнародному форматі.

Запит INSERT дозволяє вводити дані за підказкою під час

виконання програми, тоді він буде запитом з параметром. Для

вищенаведеного прикладу він прийме такий вигляд:

INSERT INTO Oblik VALUES([Підказка1],..., [Підказка4]);

Команда UPDATE служить для редагування записів таблиці.

UPDATE ім’я таблиці SET ім’я поля1=значення1, ім’я поля2 = значення2, ...

WHERE вираз;

Приклад 8. Скласти запит для заміни в таблиці Gazpr поля Nazpp

на Україна тих записів, де Kodpr= 2:

UPDATE Gazpry SET Nazpp = ’Україна’ WHERE Kodpr = 2;

Якщо редагуються всі записи, то інструкція WHERE може бути

опущена. Для нашого прикладу такий запит матиме такий вигляд:

UPDATE Gazpr SET Nazpp = ’Україна’;

Команда DELETE служить для знищення всіх або вибраних за

умовою записів таблиці.

DЕLETE * FROM ім’я таблиці WHERE вираз;

Приклад 9. Знищити ті записи таблиці Gazpr, яких Nazpr =

Україна.

DELETE * FROM Gazpr

WHERE Gazpr.Nazpr = 'Україна';

Команда CREATE призначена для утворення макету (структури)

нової таблиці. Внесення нових таблиць програмним способом під

час експлуатації БД є небажаним, бо через це змінюється її

структура. Такий крок може бути оправданим хіба що на стадії

розробки БД, коли виникають труднощі з визначенням її

структури. Найчастіше таблицю додають до діючої БД з метою

зберігання проміжних, тимчасових даних.

CREATE TABLE ім’я таблиці (ім’я_поля тип, ім’я_поля тип, ...);

Приклад 10. Створити таблицю Kadry, яка має два поля: Kod_p

типу цілого числа і Priz текстового типу

CREATE TABLE Kadry(Kod_p INTEGER, Priz TEXT);

Цими командами далеко не вичепуються можливості мови SQL

MS Access. Повний їх перелік можна переглянути, викликавши

Довідку

3.4.6

Приклади побудови запитів MS Access

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

вікні База даних встановити перемикач на Запити і виконати команду

Створити. Після цих дій відкривається вікно Новий запит, яке має

п’ять перемикачів. Серед них варто використовувати Конструктор

для простого запиту і Перехресний запит – для перехресного. Після

натискання кнопки Ok відкривається відповідне цим перемикачам

вікно Запит у режимі конструктора. На його тлі повинно

знаходитися вікно Додати таблицю подібно до того, як це було з уже

розгляненим вікном Схема даних. Якщо воно відсутнє (наприклад,

через ненавмисне закриття), то його можна викликати командою

Додати таблицю з вікна MS Access так само, як це робиться при

встановленні зв’язків між таблицями або за допомогою

контекстного меню. При цьому курсор повинен знаходитися на

верхньому чистому полі запиту.

Новий запит в режимі конструктора створюється в три етапи:

1– занесення таблиць або інших запитів у верхнє поле запиту;

2– перенесення в запит полів з джерел запиту;

3– занесення виразів у нижню частину вікна запиту.

Побудуємо запит для звіту за умовою задачі 4. Нагадаємо, що

звіт повинен містити дані про сумарний об’єм транспортованого

газу за поточний рік по кожному підприємству для показаного на

формі газопроводу. У вікні

База даних

виберемо об’єкт

Запити

Появляється вікно

Новий запит

, в якому вибираємо

Конструктор

Появляється вікно

Конструктора запиту

, на тлі якого маємо вікно

Додати таблицю

з переліком таблиць бази даних. Вибираємо таблиці

Pidpr

(для вибору назв підприємств) та

Oblik

(для даних про об’єм

газу, дату і код газопроводу), і за допомогою команди

Додати

заносимо їх макети на верхнє поле конструктора запиту.

Виконуємо команду

Закрити

у вікні

Додати таблицю

. Встановимо

тимчасові зв’язки між таблицями за допомогою кодового поля

Kodpp

, для цього перенесемо його з макета однієї таблиці на інший.

Внесемо в запит такі поля:

–

Nazpp;

–

Suma: Kil

, установимо групову операцію:

Sum

;

–

Rik: Year([Data]), умова відбору записів: Year(Date());

–

Kodpr

, умова відбору записів:

[Forms]![Form]![Kodpr]

Групові операції викликаємо за допомогою відповідної кнопки

на панелі інструментів. Готовий запит у режимі конструктора

показаний на рис. 3.7.

Для його перевірки перейдемо в режим перегляду за допомогою

відповідної кнопки на панелі інструментів (крайня зліва). Копія

цього запиту, відкритого в режимі перегляду, показана на рис. 3.8.

Під час переходу в режим перегляду цей запит буде працювати як

запит з параметром. Оскільки форма

Form

нами ще не виготовлена,

Рисунок 3.7 – Запит

Ob_sum

, відкритий в режимі конструктора

а, отже, не відкрита, то запит не зможе прочитати код газопроводу

з форми, як це вимагається в постановці задачі. Тому під час

відкриття запиту в режимі перегляду появляється пропозиція

ввести цей код вручну в спеціальне поле. Допустимо, що пізніше

при відкритій формі на ній буде встановлено газопровід з кодом 2,

тому введемо саме це число.

Ob_sum

Nazpp Suma Rik Kodpr

Експорттрансгаз 1 2006 2

Львiвтрансгаз 2 2006 2

Рисунок 3.8 – Копія запиту Ob_sum в режимі перегляду

З метою перевірки запиту виконаємо всі потрібні операції

вручну. Таблиця Oblik містить три записи про транспортування газу

по газопроводу з кодом 2 (це підприємства з кодами 1, 4, і 5), але

тільки два останні підприємства відображені в таблиці, як такі, що

працювали в 2006 поточному році. В запиті правильно відображені

підприємства з кодами 4 і 5. Збігаються й суми транспортованого

газу. Зауважимо, що під час перевірки запиту системна дата

містила 2006 рік. Отже, запит працює вірно, закриємо його та дамо

йому назву Ob_sum.

Запит для діаграми за умовою задачі 5 теж побудуємо за

допомогою конструктора. Нагадаємо, що відповідно до постановки

задачі він повинен містити дані про сумарну кількість

перепомпованого газу всіма газопроводами за кожний рік протягом

останніх 8 років підприємством, назва якого знаходиться на

відкритій формі. Виберемо таблиці Pidpr i Oblik, в яких містяться ці

дані. Назвемо запит

Grafik

, занесемо в нього такі поля:

–

Rik: Year([Data]), умова відбору записів: Year(Date())-Year([Data])<9;

–

Kilk:Kil

, групова операція:

Sum

;

–

Kodpp, умова відбору записів: [Forms]![Form][Kodpp];

–

Nazpp

Grafik

Rik Kilk Kodpp Nazpp

2002 3 1 Харкiвтрансгаз

2006 6 1 Харкiвтрансгаз

Рисунок 3.9 – Копія запиту

Grafik

в режимі перегляду

Копія запиту для діаграми показана на рис. 3.9. Під час його

відкриття був введений код підприємства 1 (через відсутність

форми), назва якого

Харкiвтрансгаз

відображена в запиті. В таблиці

Oblik

є три записи про нього: один за

2002

рік з кількістю 3 і два за

2006 рік з сумарною кількістю 6, що вірно відображено в запиті.

Rozpodil

Nazpr Nazpp Kilkist Rik

Гадяч - Кобеляки - Київ Київтрансгаз 2 2006

Гадяч - Кобеляки - Київ Прикарпаттрансгаз 3 2006

Гадяч - Кобеляки - Київ Харкiвтрансгаз 5 2006

Орел – Київ Харкiвтрансгаз 1 2006

Україна Експорттрансгаз 1 2006

Україна Львiвтрансгаз 2 2006

Рисунок 3.10 – Копія запиту

Rozpodil

в режимі перегляду

Для звіту за умовою задачі 6 про розподіл кількості

транспортованого газу на кожному газопроводі по підприємствах

у поточному році побудуємо такі два запити: простий –

Rozpodil

перехресний

Rozpodil_p

Запит

Rozpodil

містить дані зі всіх трьох таблиць, з’єднаємо їх

за допомогою кодових полів і внесемо в запит такі поля:

–

Nazpr;

–

Nazpp

;

–

Kilkist: Kil, групова операція: Sum;

–

Rik: Year([Data])

, умова відбору записів:

Year(Date())

Запит

Rozpodil_p

виготовимо за допомогою майстра

перехресного запиту, відповідаючи на його кроки, в якості рядків

виберемо поле

Nazpr

– назви газопроводів, а якості стовпців поле

Nazpp

– назви підприємств. Джерелом цього запиту є запит

Rozpodil

який в режимі перегляду показаний на рис. 3.10. Запит

Rozpodil_p

показано на рис. 3.11. Звіривши дані, що є в цих запитах з даними

таблиць, переконуємося в тому, що запити працюють вірно.

Rozpodil_p

Nazpr

Итоговое

значение

Kilkist

Експорт

трансгаз

Київтранс

газ

Львiвтра

нсгаз

Прикарпа

ттрансгаз

Харкiвтр

ансгаз

Гадяч - Кобеляки -

Київ

10 2 3 5

Орел – Київ 1 1

Україна 3 1 2

Рисунок 3.11 – Копія запиту

Rozpodil_p

в режимі перегляду

Запит для поштової наклейки за умовою задачі 8 містить

інформацію з двох таблиць:

Gazpr

Oblik

. В режимі перегляду він

показаний на рис 3.12. Він має назву

Naklejka

і містить такі поля:

–

Nazpr;

–

Kil:Kil

, групова операція:

Sum

;

–

Kilnyt;

–

Diam

;

–

Povidom:Iif([Kilnyt]>3;"Кільк. ниток більше 3";"");

–

Vidbir:Iif(Year(Date())-Year([Data])<11;True;False)

, умова відбору:

True

. (На рисунку 3.12 це поле не показано).

Naklejka

Nazpr Kil Kilnyt Diam Povidom

Гадяч - Кобеляки - Київ 10 4 1700 Кільк. ниток більше 3

Орел – Київ 12 1 1200

Україна 6 1 1400

Рисунок 3.12 – Копія запиту Naklejka для поштових наклейок у

режимі перегляду

Побудуємо запит для форми за умовою задачі 7. Ця форма

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

запиту внесемо всі таблиці (Gazpr, Oblik i Pidpr) і з’єднаємо їх між

собою за допомогою відповідних кодових полів. Внесемо в запит

такі поля:

–

Nazpp

– назва підприємства;

–

Kodpr – код газопроводу;

–

Nazpr – назва газопроводу;

–

Kilnyt

– кількість ниток газопроводу;

–

Diam – діаметр газопроводу;

–

Karta

– карта-схема газопроводу;

–

Kil – кількість перепомпованого газу;

–

Data

– дата обліку;

–

Logopp – логотип підприємства.

Назвемо запит D_form, він не містить ні групових операцій, ні

умов відбору записів.

3.5 Виготовлення звітів

Рисунок 3.13. – Звіт

Ob_sum

в режимі перегляду

Звіт

Ob_sum

за умовою задачі 4 виготовимо за допомогою

майстра звіту. У вікні

База даних

виберемо об’єкт

Звіт

, виконаємо

команду

Створити

. Появляється вікно

Новий звіт

, вибираємо у

нижній його частині джерело звіту – запит

Ob_sum

, а в верхній –

майстра.

Дамо відповіді на кроки майстра:

1–

на пропозицію вибрати імена полів, виберемо:

Nazpp

Suma

;

2– додати рівень групування. Проігноруємо, групування

виконано під час виготовлення запиту;

3–

майстер пропонує встановити порядок сортування рядків,

залишимо все без зміни, команда

;

4–

макет звіту –

ступінчастий

;

5–

стиль –

спокійний

;

6–

надамо ім’я звіту

Ob_sum

команда Готово