Ряди даних. 4 страница

Кожний стовпець бланка QBE представляє одне поле, що використовується в запиті.

Поле запиту може:

- відбиратися з таблиці-джерела;

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

- бути підсумковим (тобто використовуючим одну з вбудованих функцій Microsoft Access).

Рисунок 3.6 – Бланк QBE для створення запитів

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

В другому рядку бланка запиту «Ім'я таблиці» MS Access виведе ім'я таблиці, з якої вибрано поле.

В третьому рядку бланка «Сортування» користувач може задати, чи потрібно виконувати сортування по вибраному або обчислюваному полю.

Прапорці в четвертому рядку бланка «Виведення на екран» відповідають за виведення полів в наборі записів. За умовчанням MS Access виводить на екран всі поля, включені в бланк. Проте деякі поля включаються в запит тільки для відбору по якій-небудь умові потрібних записів, а виводити їх не обов'язково. Щоб відключити таке поле з набору записів, зніміть в його стовпці прапорець «Виведення на екран» ().

Нарешті, для введення умови відбору записів використовується п'ятий рядок «Умова відбору» і додаткові рядки «або».

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

В самому початку списку полів, що знаходиться у верхній половині вікна запиту (а також в списках, що розкриваються, в першому рядку бланка QBE), знаходиться спеціальний символ «*», що означає «Всі поля». Якщо ви хочете включити в запит всі поля таблиці, не треба визначати кожне по окремості в бланку QBE (за винятком тих випадків, коли для деяких полів необхідно задати умови відбору або порядок сортування). Достатньо перетягнути «*» із списку полів в бланк QBE. Врахуйте, що ви можете повторно включити окремі поля таблиці в бланк запиту, щоб визначити умови відбору для них. В цьому випадку, щоб поля двічі не виводилися в наборі записів запиту, зніміть прапорці «Виведення на екран» в стовпцях бланка, що містять поля, що повторюються.

Встановлення властивостей полів.

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

- опис (інформація, яка виводиться в рядку стану вікна запиту в режимі таблиці, коли поле стає поточним);

- формат поля (представлення даних на екрані);

- число десяткових знаків (для числових даних);

- маска введення;

- підпис (заголовок стовпця).

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

Корисно визначати властивості обчислюваних полів.

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

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

Можна виконувати обчислення з будь-якими полями таблиці і зробити обчислюваний вираз новим полем в наборі записів. Для створення обчислюваного поля можна використовувати будь-які з вбудованих функцій Access. Крім того, поля запиту можуть містити дані, одержувані за допомогою арифметичних операцій над полями таблиці. Наприклад, запис про товар містить поля Ціна і Кількість (покупка), але загальна вартість покупки (кількість, помножена на ціну) в ній не фіксується. Щоб вивести її значення в наборі записів запиту, потрібно ввести в порожню чарунку рядка Поле бланка запиту вираз:

Цена*Количество

Імена полів при обчисленні виразів необхідно брати в квадратні дужки, якщо вони складаються більш ніж з одного слова. При створенні будь-якого виразу MS Access автоматично поміщає перед ним стандартне ім'я поля і двокрапку (наприклад, Выражение1:). Такі імена краще замінювати на більш змістовні, оскільки вони відображаються в заголовку стовпця для даного поля при запуску запиту на виконання. Наприклад, повний вираз для обчислення вартості покупки матиме наступний вигляд:

Сумма: [Цена]*[Количество]

Більш раціональне створювати обчислюване поле за допомогою будівника виразів.

Використання будівника виразів.

Для побудови складних виразів Access надає сервіс, званий «Будівник виразів».

Щоб познайомитися з будівником виразів, необхідно відкрити запит в режимі конструктора, клацнути по порожньому полю в бланку запиту і натиснути кнопку «Побудувати» на панелі інструментів. Microsoft Access відкриє вікно «Будівник виразів» (див. рис. 3.7).

Рисунок 3.7 – Будівник виразів

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

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

Вже розглянутий приклад побудови поля Сумма у будівнику виразів буде виглядати наступним чином. Користувач повинен вмістити курсор в порожню чарунку рядка Поле бланка запиту, та натиснути кнопку „ Побудувати ”. Потім у вікні будівника виразів ввести назву обчислюваного поля, поставити двокрапку, обрати в першому зліва списку розділ таблиці, відкрити необхідні таблиці та по черзі вибрати необхідні поля подвійним натисненням миші. При цьому між полями відразу необхідно проставляти знаки арифметичних операцій (у даному прикладі множення *). Якщо з’явиться зайве слово „ Вираження ”, його потрібно знищити. Після закінчення побудови необхідно натиснути кнопку ОК (див. рис. 3.7).

У виразах можна використовувати наступні оператори:

1) + - складає два арифметичні вирази;

2) - - віднімає з першого арифметичного виразу друге;

3) * - перемножує два арифметичні вирази;

4) / - ділить перший арифметичний вираз на другий;

5) \ - округляє два арифметичні вирази до цілих значень і ділить перше на друге. Результат округляється до цілого;

6) ^ - підносить перший арифметичний вираз до ступеня, що задається другим арифметичним виразом;

7) Mod - округляє обидва арифметичні вирази до цілих значень, ділить перше на друге і повертає залишок;

8) & - створює текстовий рядок як результат приєднання другого рядка до кінця першого (оператор конкатенації).

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

[Прізвище]& " " &[Ім'я]

або ж:

[Прізвище]& " " &[Ім'я]& " " &[Посада]

Якщо один з операндів є числом, Microsoft Access перед проведенням конкатенації перетворить його в рядок символів.

Якщо після клацання в чарунці рядка Поле натиснути SHIFT-F2, то відкриється вікно Область введення, в якому зручніше задавати довгі вирази.

Підсумкові поля.

Якщо користувача цікавить не вибірка окремих записів таблиці, а підсумкові значення по групах даних, слід створити підсумковий запит. Для цього необхідно натиснути кнопку „Групові операції” на панелі інструментів конструктора запитів, щоб в бланку QBE з'явився рядок «Групова операція» (див. рис. 3.8). Тепер записи по кожному полю групуються, але підсумок ще не підводиться. Для отримання підсумків в рядку «Групова операція» необхідно вибрати конкретну підсумкову функцію.

Рисунок 3.8 – Підсумковий запит в бланку QBE

MS Access надає 9 підсумкових функцій.

1) Sum – сума всіх значень заданого поля в кожній групі; для числового і грошового типів даних.

2) Avg – середнє арифметичне всіх значень заданого поля в кожній групі; значення Null (пустий) не враховуються; для числового і грошового типів даних.

3) Min – найменше значення, знайдене в цьому полі усередині кожної групи; значення Null (пустий) не враховуються; для числового, грошового і текстового типів даних.

4) Max - найбільше значення, знайдене в цьому полі усередині кожної групи; значення Null (пустий) не враховуються; для числового, грошового і текстового типів даних.

5) Count – кількість записів, в яких значення даного поля відмінні від Null (не пусті).

6) StDev – стандартне відхилення всіх значень даного поля в кожній групі; для числового і грошового типів даних.

7) Var – дисперсія значень даного поля в кожній групі; для числового і грошового типів даних.

8) First – перше значення цього поля в групі.

9) Last – останнє значення цього поля в групі.

3.2.3 Створення умов відбору в запитах

Оператори порівняння Between, In і Like.

Окрім звичайних операторів порівняння, MS Access надає три спеціальні оператори, корисні для відбору даних, що виводяться в наборі записів запиту.

Between...And - визначає приналежність значення виразу вказаному діапазону. Оператор Between...And часто використовують для перевірки, чи потрапляє значення поля у вказаний діапазон чисел. Якщо значення поля потрапляє в діапазон, що задається (наприклад, between 18 and 30), оператор Between...And повертає значення True; в осоружному випадку повертається значення False. Логічний оператор Not дозволяє перевірити протилежну умову.

In - перевіряє, чи співпадає значення виразу з одним з елементів вказаного списку. Якщо вираз міститься в списку значень, оператор In повертає значення True; в осоружному випадку повертається значення False. За допомогою логічного оператора Not можна перевірити зворотну умову (тобто, що вираз не належить списку значень).

Like - використовується для порівняння рядкового виразу із зразком в полі. Для аргументу зразок можна задавати повне значення (наприклад, Like "Іванов") або використовувати підстановлювальні знаки для пошуку діапазону значень (наприклад, Like "Ив*").

Знаки підстановки * (зірочка),? (знак питання), # (знак числа) і [ (відкриваюча квадратна дужка) розглядаються як зразок для пошуку цього символу тільки при виведенні їх в квадратні дужки. Варіанти використання цих знаків в умовах відбору за допомогою оператора Like див. в таблиці 3.1.

Таблиця 3.1 - Знаки підстановки в умовах відбору з оператором Like

Знак	Приклад	Результат	Використання
*	ст*   *ка	знаходить «стол», «стул» і «стакан» знаходить «лампочка», «папка» і «краска»	Відповідає будь-якій кількості символів і може бути використаний в будь-якому місці текстового рядка
?	л?па	знаходить «лапа», «липа» і «лупа»	Відповідає будь-якому одиночному символу
#	1#3	знаходить 103, 113, 123	Відповідає будь-якій одиночній цифрі
[ ]	л[аи]па	знаходить «лапа» і «липа», але не «лупа»	Відповідає будь-якому символу, розташованому в квадратних дужках
!	л[!аи]па	знаходить «лупа», але не «лапа» і не «липа»	Відповідає будь-якому символу, не включеному в список
-	б[а-в]д	знаходить «бад», «ббд» і «бвд»	Відповідає будь-якому символу з діапазону символів

Оператори AND і OR.

Якщо користувач хочете відібрати записи з конкретним значенням поля, він повинен ввести його в чарунку «Умова відбору» цього поля. Текстове значення, що використовується як умова відбору, повинне бути укладено в лапки. Зверніть увагу, що Access додає до введеного текстового значення подвійні лапки автоматично, а також додає оператор Like (в бланку запиту, після натиснення клавіші ENTER).

Якщо користувача цікавить декілька значень, можна ввести їх в рядок «Умова відбору», розділяючи логічним оператором Or. Наприклад, умова
Like "А*" Or Like "Г*" дозволяє відібрати всі записи для назв на відповідні букви. Можна також вводити кожне зі значень, що цікавлять, в рядки «Умова відбору» і «або» в стовпці поля, за яким ведеться пошук. Наприклад, користувач можете ввести А* в рядок «Умова відбору», Г* - в наступний рядок (перший з рядків «або») і т. д.

Коли задані для деякого поля декілька умов відбору, сполучених логічним оператором Or, то для того, щоб запис був відібраний, істинним повинне бути хоча б одне з них.

Коли користувач вводить умови відбору для декількох полів, то всі вирази в рядку «Умова відбору» або в рядку «або» повинні приймати значення Істина для будь-якого запису, що включається в набір записів запиту. Це означає, що Access виконує логічну операцію AND над умовами відбору, що знаходяться в одному рядку. Тому, якщо в рядку «Умова відбору» ввести А* для поля Найменування і <3 для поля Ціна, то в набір записів запиту потраплять тільки найменування на букву А, за ціною не більш, ніж 3 у.о. Якщо ж в рядку «Умова відбору» для поля Найменування ввести значення А* і вираз >=2 And <=10 для поля Ціна, то будуть відібрані найменування на букву А, за ціною
від 2 до 10 у.о. (див. рис. 3.9).

Умови відбору для дат і часу.

Microsoft Access зберігає значення дат і часу як числа з плаваючою точкою і з подвійною точністю. Значення зліва від десяткової точки відповідає даті, дробова частина числа представляє час доби.

Щоб повідомити Access про те, що він вводить дату і час, користувач в запитах повинен укладати значення в символи числа (#). Для вказівки конкретної дати можна використовувати вид запису дати та часу, який вам здається найзручнішим. Наприклад, #15 Квітень 2011#, #15/04/11# і
#15-апр-2011# визначають одну і ту ж дату. Так само #5:ЗО РМ# і #17:30# визначають час «сімнадцять годин тридцять хвилин».

Рисунок 3.9 – Умови відбору в запиті

Access надає також декілька функцій, які можна використовувати при завданні умов відбору для дат і часу:

Day(дата) - повертає значення дня місяця в діапазоні від 1 до 31;

Month(дата) - повертає значення місяця року в діапазоні від 1 до 12;

Year(дата) - повертає значення року в діапазоні від 100 до 9999;

Weekday(дата) - за умовчанням повертає ціле число від 1 (Неділя) до 7 (Субота), відповідне дню тижня;

Hour(дата) - повертає ціле число від 0 до 23, яке представляє значення години;

і інші.

Перехресні запити.

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

Для створення перехресного запиту в режимі конструктора в пункті меню Запит виберіть підпункт Перехресний, щоб перетворити запит на вибірку в перехресний. При такому перетворенні змінюється заголовок вікна запиту і в бланку QBE перед рядком «Сортування» з'являються рядки «Групова операція» і «Перехресна таблиця» (див. рис. 3.10, а).

В рядку «Перехресна таблиця» треба визначити одне поле значень, по яких виконуватиметься вибрана в рядку «Групова операція» підсумкова функція або обчислюватиметься заданий вираз.

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

Результат роботи перехресного запиту див. на рис. 3.10, б.

а)

б)

Рисунок 3.10 – Бланк QBE (а) та результат роботи (б) перехресного запиту

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

Оновлення даних таблиці.

За умовчаннямMS Access завжди створює запит на вибірку. В режимі конструктора в пункті меню Запит присутні команди для чотирьох типів запитів на зміну: Створення таблиці, Оновлення, Додавання, Видалення.

Виберіть пункт Оновлення, щоб перетворити запит на вибірку в запит на оновлення. При цьому змінюється заголовок вікна запиту і в бланку QBE замість рядка «Сортування» з'являється рядок «Оновлення» (див. рис. 3.11).

Рисунок 3.11 – Бланк QBE запиту на оновлення даних таблиці

Запит на оновлення також вибиратиме дані за заданим вами умов. Але якщо в якому-небудь полі в рядку «Оновлення» користувач запише нове значення або вираз для його обчислення, то це поле у відібраних записах буде замінено (оновлено).

При запуску на виконання запиту на оновлення, як і інших типів запитів на зміну, користувач отримає:

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

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

Якщо користувач упевнений в правильності складання свого запиту, то в обох випадках слід відповісти „Так” і виконати запит.

Створення нової таблиці.

В режимі конструктора в пункті меню Запит виберіть підпункт Створення таблиці, щоб перетворити запит на вибірку в запит на створення таблиці. MS Access виведе на екран вікно діалогу Створення таблиці (див. рис. 3.12, а). Необхідно задати ім'я (ввести з клавіатури) для майбутньої таблиці.

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

а)

б)

Рисунок 3.12 – Вікно діалогу (а) та бланк QBE (б) запиту на створення нової таблиці

Додавання даних в таблицю.

В режимі конструктора в пункті меню Запит виберіть підпункт Додавання, щоб перетворити запит на вибірку в запит на додавання даних в таблицю. На екрані з’явиться вікно діалогу Додавання (див. рис. 3.13, а). Необхідно вибрати ім'я таблиці, в яку будуть вставлені (додані) нові дані.

Змінюється заголовок вікна запиту і в бланку QBE з'являється рядок «Додавання». Важливо, щоб кількість полів в запиті відповідала кількості полів в таблиці, або, принаймні, не перевищувала їх. Необхідна відповідність типів даних в полях запиту і відповідних полях таблиці. Бажано відповідність імен полів в запиті і полів таблиці. В цьому випадку взаємовідповідні поля будуть підставлені в бланку запиту QBE в рядку «Додавання» автоматично (див. рис. 3.13, б). Інакше необхідно буде вибрати відповідні імена полів в рядках «Поле» і «Додавання» вручну.

а)

б)

Рисунок 3.13 - Вікно діалогу (а) та бланк QBE (б) запиту на додавання

Видалення даних з таблиці.

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

При такому перетворенні змінюється заголовок вікна запиту і в бланку QBE замість рядка «Сортування» з'являється рядок «Видалення» (див. рис. 3.14).

Рисунок 3.14 - Бланк QBE запиту на видалення

3.3 Призначення і методика створення звітів

Звіти – це об'єкти, за допомогою яких дані з бази виводяться на друк в «регламентованому, чітко визначеному, зручному для користувача вигляді» (див. рис. 3.15). Окрім привабливого вигляду, звіти дозволяють комбінувати дані з декількох таблиць, а також упорядковувати записи таблиці на підставі даних інших таблиць.

Звіти зручно створювати за допомогою майстра, а допрацьовувати при необхідності в конструкторі.

Вікна конструктора є стандартними як для звітів, так і для форм. Розглянемо деякі їх особливості (див. рис. 3.16).

...

Рисунок 3.15 – Звіт з надходження товарів

Рисунок 3.16 – Вікно конструктору звіту з надходження товарів

Кнопка «Список полів» (можна також використовувати команду Список полів меню Вид) відкриває список полів, що містяться в базовому джерелі записів звіту або форми. Переміщення поля із списку за допомогою миші дозволяєавтоматично створювати елементи управління, приєднані до джерела записів.

Кнопка «Область виділення звіту» служить для відображення вікна властивостей звіту або форми; подвійне клацання по ній виведе на екран це вікно. (Поява чорного квадрата на цій кнопці показує, що звіт або форма виділений).

Кнопка «Панель елементів» (можна також використовувати команду Панель елементів меню Вид) відображає або приховує панель елементів. Ця панель використовується для створення елементів управління в звіті або формі.

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

Область вікна звіту розділяється на декілька функціонально різних областей: верхнього колонтитулу, даних, нижнього колонтитулу, заголовку та примітки звіту, а також заголовку та примітки груп, якщо вони є в структурі звіту (див. рис. 3.16).

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

В області даних, яка повторюється для кожного запису, що виводиться в звіті (тобто при кількості записів n з'явиться в звіті n раз), звичайно розміщуютьполя з даними.

В області нижнього колонтитулу, яка також з'являється один раз, але в нижній частині кожної сторінки звіту, звичайно розміщують номер сторінки.

В заголовку звіту звичайно розміщують його назву і дату.

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

В примітках до звіту та групи звичайно розміщують підсумкові поля.

Контрольні запитання і завдання

1. Структура бази даних – описати принцип реалізації в Access.

2. Як називаються рядки і стовпці таблиць в Access?

3. Конструктор таблиць – виконати характеристику типів даних полів (текстовий, числовий) і визначити призначення властивості «розмір поля».

4. Конструктор таблиць – виконати характеристику типів даних полів (дата/час, грошовий) і визначити призначення властивості «обов'язковість».

5. Конструктор таблиць –виконати характеристику типів даних полів (лічильник, логічний) і визначити призначення властивості «індексованість».

6. Яким полям призначають тип даних «Поле MEMO»?

7. Як називаються поля, що беруть участь в зв'язку між двома таблицями?

8. Для чого використовується вікно «Схема даних»?

9. Без яких об'єктів не може існувати база даних?

10. Чому при закритті таблиці програма Access не пропонує виконати збереження внесених даних?

11. Запит з обчислюваним полем (принцип побудови, приклад).

12. Запит на вибірку з умовою (принцип побудови, приклад).

13. Запит з угрупуванням, або підсумковий запит (принцип побудови, приклад).

14. Застосування операторів для створення умов в запитах (опис, приклади).

15. Який оператор проводить пошук зразків в текстових полях?

16. Яку підсумкову функцію треба вибрати для обчислення кількості записів в наборі?

17. Для чого призначений кожний з об'єктів бази даних Access – таблиці, форми, запити, звіти, модулі, макроси?

18. В чому полягає особливість поля типу Лічильник?

19. Яку підсумкову функцію треба вибрати для обчислення середнього арифметичного значення з набору записів?

20. В чому переваги поля, яке є первинним ключем?

РОЗДІЛ 4

ОСНОВИ АЛГОРИТМІЗАЦІЇ ТА ПРОГРАМУВАННЯ

Дата добавления: 2015-05-24