§21
Работа с многотабличной базой данных
Таблицы и связи между ними
Вспомним про базу данных кафе, о которой мы говорили в конце § 14 (рис. 3.38).
Построим в новой базе данных (назовём её Кафе) все необходимые таблицы (пока без связей). Поскольку в этой базе несколько таблиц, далее мы будем использовать общепринятые обозначения типа Блюда.[Название] — это означает «поле Название таблицы Блюда».
Не забудьте, что связи устанавливаются только между однотипными полями, т. е. поля Заказано.[Номер заказа] и Заказано.[Код блюда] должны быть целого типа (INTEGER), чтобы их можно было связать соответственно с номером заказа и кодом блюда. Для поля Блюда.[Цена] выберите десятичный тип (DECIMAL) и денежный формат для вывода на экран.
Чтобы установить связи между таблицами, выберем пункт меню (В программе Microsoft Access нужно щёлкнуть на кнопке Схема данных на вкладке Работа с базами данных) Сервис – Связи. С помощью специального окна добавим в рабочую область (она пока пустая) все три таблицы (рис. 3.39).
Теперь можно «схватить» мышью название какого-то поля и перетащить его на название поля другой таблицы, с которым нужно установить связь. С помощью этого метода установим все связи, показанные на схеме в начале параграфа. После этого окно можно закрыть, сохранив изменения.
Чтобы изменить или удалить связи, снова зайдите в меню Сервис – Связи. Для удаления связи её нужно выделить щелчком мышью и нажать клавишу Delete.
Теперь остаётся заполнить таблицы (можно придумать свои данные или взять их из § 14).
Запрос данных из нескольких таблиц
Мы построим два запроса к базе данных Кафе — простой и итоговый. Начнём с простого запроса, в котором будет собрана информация по всем сделанным заказам (рис. 3.40).
Посмотрим, откуда нужно взять данные. Номер заказа (в столбце Заказ) и дата хранятся в таблице Заказы, а название блюда и цена — в таблице Блюда. Перейдём к созданию запроса в режиме дизайна и добавим в рабочую область две названные таблицы (рис. 3.41).
Теперь добавим в столбцы бланка все нужные поля и выполним запрос. Результаты окажутся неожиданными — запрос выдаёт слишком много записей, больше, чем есть на самом деле. Дело в том, что таблицы в запросе оказались несвязанными: мы не добавили таблицу Заказано. Из неё не берутся данные, но она служит для связи таблиц в единую систему.
Чтобы исправить ситуацию, выберем пункт меню Вставка – Добавить таблицу или запрос и добавим таблицу Заказано. После этого запрос отберёт данные правильно. Остаётся только дать столбцам запроса понятные названия, для этого используется строка Псевдоним в бланке запроса,
Теперь окно конструктора запросов можно закрыть, при сохранении дайте запросу название ЗапросЗаказы. Обратите внимание, что нельзя называть запрос именем, которое совпадает с именем таблицы (например, Заказы) или другого запроса.
Двойной щелчок на имени запроса запускает его на выполнение. Щёлкнув правой кнопкой мыши на заголовке столбца, можно изменить его оформление. Установите таким образом денежный формат для поля Цена и выравнивание вправо для всех числовых данных.
Итоговый запрос
Теперь построим запрос, который считает общую стоимость каждого заказа (рис. 3.42).
Как видно из рис. 3.42, нам нужны номер и дата заказа (из таблицы Заказы) и цены блюд (из таблицы Блюда), которые нужно как-то сложить. Посмотрев на схему соединения таблиц, легко понять, что для правильного объединения данных нужно добавить в запрос таблицу Заказано, хотя ни одно её поле в результате запроса не выводится.
Создадим новый запрос в режиме дизайна и добавим в рабочую область таблицы Заказы, Заказано и Блюда. Перетащим в бланк запроса нужные поля и введём правильные заголовки столбцов (как на образце) в строке Псевдоним. Если выполнить этот запрос, мы увидим цены отдельных блюд, а не общую стоимость.
Чтобы подсчитать сумму цен по каждому заказу, в поле Цена найдём строку Функция (В Microsoft Access эта строка называется Групповая операция. Чтобы она появилась в бланке запроса, нужно щёлкнуть по кнопке Итоги на вкладке Конструктор) и там выберем вариант Сумма. Кроме того, надо указать, какие группы записей объединять при суммировании. Для этого в той же строке Функция в полях запроса Номер и Дата выберем вариант Group (Группировка). Это значит, что сумма считается для каждой уникальной комбинации «номер запроса – дата». Поскольку у нас номер заказа уникальный, фактически будет рассчитана сумма каждого заказа. Выполним запрос и проверим, что он работает правильно. Теперь можно закрыть окно Конструктора, сохранив запрос под именем ЗапросКОплате. Запустим запрос двойным щелчком и настроим формат вывода данных.
Кроме функции Сумма в итоговых запросах можно использовать и другие функции, например количество, среднее значение, минимум, максимум.
Формы
Построим форму, показанную на рис. 3.43, в которой для каждого номера заказа выдаётся его дата, состав заказанных блюд с ценами и общая сумма.
Форма создаётся на основе таблицы или запроса. В данном случае мы хотим получить информацию о заказе, поэтому основной источник данных — это таблица — Заказы.
Информацию о названиях блюд и ценах можно было бы взять из таблицы Блюда, но она напрямую не связана с таблицей Заказы, поэтому так сделать не получится. Однако у нас есть запрос ЗапросЗаказы, где эти данные объединены, поэтому состав заказа и цены на отдельные блюда мы возьмём из этого запроса.
Как получить общую сумму? Вспомним про ЗапросКОплате, где сумма для каждого заказа уже найдена. Таким образом, форма объединяет информацию из таблицы Заказы и двух запросов. Связь между ними устанавливается по номеру заказа — это поле есть везде.
Как мы уже упоминали, основной источник данных — это таблица Заказы. Щёлкнем на её имени правой кнопкой мыши и выберем пункт Мастер форм из контекстного меню. Включим в форму все поля.
На втором шаге мастер предлагает добавить субформу (подчинённую форму) — дополнительный источник данных, связанный с главной формой. Отметим флажок Добавить субформу (рис. 3.44).
Мастер определил, что для таблицы Заказы подчинённой является таблица Заказано (они связаны отношением 1:N) и предлагает выбрать эту таблицу. Однако нам нужно использовать запрос, поэтому отметим второй вариант: Субформа, основанная на выбранных полях. Далее выберем поля Заказ, Блюда и Цена из запроса (рис. 3.45).
Несмотря на то что поле Заказ не будет выводиться на экран, его нужно включить в субформу, потому что через это поле на следующем шаге работы мастера устанавливается её связь с главной таблицей (рис. 3.46).
Затем выберем расположение элементов главной и подчинённой форм (рис. 3.47).
После завершения работы мастера нужно отрегулировать размеры полей и настроить формат вывода. Например, для правильного отображения цены нужно войти в конструктор форм, щёлкнуть правой кнопкой мыши на столбце Цена и выбрать пункт Заменить на — Поле валюты. Количество десятичных знаков можно изменить с помощью свойства Точность в окне свойств столбца (пункт Столбец в контекстном меню). Столбец Заказ в подчинённой форме нужно просто удалить (пункт Удалить столбец в контекстном меню).
Осталось вывести общую стоимость заказа. Напомним, что её нужно взять из другого источника данных — запроса ЗапросКОплате. Это значит, что необходимо добавить к форме ещё одну субформу. Мастер тут не поможет, придётся операцию вручную.
Откроем форму в режиме дизайна (Конструктора) и запустим Навигатор форм, щёлкнув на кнопке на нижней панели инструментов (рис. 3.48).
Здесь MainForm — это главная форма, a SubForm — подчинённая (её содержимое можно раскрыть, щёлкнув на знаке «+»). Остальные ветви дерева — это метки и поля данных.
Щёлкнем правой кнопкой мыши на имени главной формы и выберем в контекстном меню пункт Создать – Форма. В Навигаторе форм появится новая форма с именем Форма, но на экране ничего не меняется, ведь мы пока не добавили в эту форму ни одного элемента.
Выделите название новой формы и создайте метку (надпись) с текстом «К оплате». Для этого надо щёлкнуть на кнопке на панели Элементы управления (обычно она расположена слева) и выделить область по размеру метки (под таблицей). Свойства метки настраиваются с помощью кнопки на нижней панели инструментов или пункта Элемент управления из контекстного меню. Обратите внимание, что в окне Навигатора форм эта метка принадлежит новой субформе. Теперь аналогично добавьте на форму поле валюты из дополнительных элементов управления.
Проверьте, чтобы оно также принадлежало второй субформе.
Осталось связать новую субформу с источником данных. Для этого выделим субформу в окне Навигатора форм и выберем Свойства в контекстном меню. На вкладке Данные установим тип содержимого — запрос, в следующей строчке выберем ЗапросКОплате (рис. 3.49).
Теперь в окне свойств добавленного поля валюты на вкладке Данные надо выбрать поле данных КОплате. После этого форма полностью работоспособна.
Отчёты
Используя подстроенный ранее запрос, составим подробный отчёт по всем заказам (рис. 3.50).
Обратите внимание, что заказы сгруппированы по датам, кроме того, данные о блюдах, относящихся к одному заказу, тоже расположены вместе. Это отчёт с группировкой, причем здесь использованы два уровня группировки — сначала по дате, а потом — по номеру заказа.
Перейдём на вкладку Запросы, выделим запрос с именем ЗапросЗаказы и выберем пункт Мастер отчетов из контекстного меню. Добавим в отчёт все поля запроса, а на третьем шаге установим два уровня группировки — сначала по полю Дата, затем — по полю Заказ (рис. 3.51).
Остальные шаги выполняются так же, как и для простых отчётов, с которыми мы работали раньше.
К сожалению, в настоящей версии OpenOffice.org Base можно строить только самые простые отчёты. Например, невозможно подсчитать общую стоимость каждого заказа или включить в отчёт данные из двух источников (таблиц или запросов), как это мы делали для форм. Нужно отметить, что Microsoft Access обладает значительно большими возможностями для создания профессиональных отчётов.
Вопросы и задания
1. Как установить связи между таблицами?
2. Как вы думаете, по каким признакам программа при установке связи автоматически определяет её тип (1:1, 1:N, N:1)?
3. Как строится запрос с выбором данных из нескольких таблиц?
4. Почему иногда в запрос приходится добавлять таблицы, данные
которых не появляются в результатах запроса?
5. Что такое итоговый запрос? Зачем он нужен?
6. Что такое группировка?
7. Что означает группировка по нескольким полям?
8. Объясните, что даёт группировка при построении отчёта.
9. Какие функции можно использовать в итоговых запросах?
10. Как построить форму с подчинённой формой (с субформой)?
11. Как добавить подчинённую форму с помощью Навигатора форм?
12. Как связать новую субформу с главной формой?
Подготовьте сообщение
а) «Работа с базами данных в браузере Firefox»
б) «Работа с базами данных в браузере Chrome»
Задача
Постройте базу данных, запросы, форму и отчёт так, как показано в тексте параграфа.