§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»

Задача

Постройте базу данных, запросы, форму и отчёт так, как показано в тексте параграфа.