Лабораторная работа №4

Тема: Построение сложных запросов.

Цель: изучение способов и средств MS Access для выборки данных из нескольких таблиц БД, а также их представления с помощью перекрестной таблицы.

Теоретические сведения

Использование запросов к нескольким таблицам

Запросы можно использовать для выборки данных из нескольких таблиц. Например, для определения количества рейсов водителя с указанием общего километража необходимо получать данные из 2х таблиц: список водителей из таблицы «Водители» и информацию о рейсах из таблицы «Путевые листы». Для создания такого запроса необходимо в окне объектов БД перейти на вкладку «Запросы» и выбрать пункт «Создание запроса в режиме конструктора». После этого, в появившемся окне (рис. 2.6) выбрать таблицу «Водители», добавить её в источники данных запроса (нажатием кнопки «Добавить»), затем выбрать таблицу «Путевые листы» и также добавить её в источники данных запроса (нажатием кнопки «Добавить»). В появившемся окне (рис. 4.1) необходимо выбрать необходимые поля из каждой таблицы и задать требуемые выражения для вычисляемого поля, например:

Километраж: Sum([ПОКАЗАНИЯ_СПИДОМЕТРА_ПОСЛЕ]-[ПОКАЗАНИЯ_СПИДОМЕТРА_ДО]))

И групповой операции подсчета количества рейсов

COUNT Кол-во рейсов: КОД_ПУТЕВОГО_ЛИСТА.

Рисунок 4.1 – Конструктор запроса определения количества рейсов водителя с указанием общего километража

Аналогично, например, для определения водителя, проехавшего максимальное расстояние за текущий месяц необходимо также получать данные из 2х таблиц: из таблицы «Водители» - список водителей, а из таблицы «Путевые листы» - информацию о километраже его рейсов (рис.4.2):

Рисунок 4.2 – Конструктор запроса определения количества рейсов водителя с указанием общего километража

Аналогично, для определения списка водителей, проехавших за рейс расстояние большее нормативного свыше 15% исходные данные необходимо получать из 3х таблиц: из таблицы «Водители» - информацию о водителях, из таблицы «Пункты назначения» - информацию о нормативном расстоянии до пункта назначения и из таблицы «Путевые листы» - информацию о реально пройденном расстоянии за рейс (рис. 4.3).

Рисунок 4.3 – Конструктор запроса определения списка водителей, превысивших нормативное расстояние более чем на 15%

Формула, по которой определяется превышение:

Превышение: ([ПОКАЗАНИЯ_СПИДОМЕТРА_ПОСЛЕ]-[ПОКАЗАНИЯ_СПИДОМЕТРА_ДО])/[РАССТОЯНИЕ]

Использование перекрестных запросов

Для формирования итоговых таблиц, в которых и заголовки строк, и заголовки столбцов представляют собой значения записей (а не названия полей базы данных) используются перекрестные запросы. Например, если необходимо определить общее расстояние, которое проехал каждый автомобиль в каждый пункт назначения, необходимо, чтобы заголовками строк были марки автомобилей и их гос. номера, заголовками столбцов – названия пунктов назначения, а в ячейках таблицы содержалось расстояние, которое автомобиль проехал в данный пункт назначения.

Для создания перекрестного запроса в окне БД необходимо перейти на вкладку «Запросы» и выбрать пункт «Создание запроса в режиме конструктора». Далее необходимо, в появившемся окне (рис. 2.6) выбрать таблицы «Автомобили», «Пункты назначения» и «Путевые листы» и добавить их в источники данных запроса (нажатием кнопки «Добавить» либо двойным нажатием левой кнопки «мышки»). Затем необходимо на панели инструментов нажать кнопку «Тип запроса» и выбрать тип «Перекрестный» (рис. 4.4).

Рисунок 4.4 – Выбор типа запроса

Для поля или полей, значения которых должны быть представлены в виде заголовков строк («»), необходимо в окне конструктора запроса в ячейке строки «Перекрестная таблица» выбрать значение «Заголовки строк». Для таких полей нужно оставить в строке «Групповая операция» значение «Группировка» (рис. 4.5). В рассматриваемом примере заголовками строк являются значения полей «Марка» и «Гос_номер» таблицы «Автомобили».

Рисунок 4.5 – Выбор полей-заголовков строк

Для поля, значения которого должны быть представлены в виде заголовков столбцов, необходимо в строке «Перекрестная таблица» конструктора запросов выбрать значение «Заголовки столбцов». Значение «Заголовки столбцов» можно задать только для одного поля. Для этого поля также необходимо оставить в строке «Групповая операция» значение «Группировка» установленное по умолчанию (рис. 4.6).

В рассматриваемом примере заголовками столбцов являются записи поля «Название» таблицы «Пункты назначения».

Рисунок 4.6 – Выбор поля-заголовка столбцов

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

В рассматриваемом примере значениями будут результаты вычисляемого поля «Километраж», которое определяется по данным таблицы «Путевые листы» согласно формуле:

Километраж: Sum([ПОКАЗАНИЯ_СПИДОМЕТРА_ПОСЛЕ]-[ПОКАЗАНИЯ_СПИДОМЕТРА_ДО])

Рисунок 4.7 – Выбор поля содержащего значения ячеек перекрестной таблицы

В итоге будет сформирован перекрестный запрос, который формирует таблицу с данными о пройденном расстоянии каждого автомобиля в каждый пункт назначения (рис. 4.8)

Рисунок 4.8 – Конструктор перекрестного запроса определения километража, пройденного каждым автомобилем в каждый пункт назначения

Результаты выполнения запроса приведены на рис. 4.9.

Рисунок 4.9 – Результат выполнения перекрестного запроса определения километража, пройденного каждым автомобилем в каждый пункт назначения

Если в перекрестном запросе значениями ячеек таблицы является количество записей, например, количество поездок водителей в различные пункты назначения, в перекрестном запросе следует использовать групповые операции. Для этого в строке «Групповая операция» необходимо выбрать статистическую функцию, которая будет использована для заполнения перекрестной таблицы (например Sum, Avg или Count).

Кроме этого для упорядочивания значений заголовков строк и заголовков столбцов можно задавать соответствующий порядок сортировки (по возрастанию, либо по убыванию).

Например, конструктор запроса, формирующего таблицу распределения поездок водителей по пунктам назначения, приведен на рис. 4.10, а результат его выполнения – на рис. 4.11.

Рисунок 4.10 – Конструктор перекрестного запроса таблицы распределения поездок водителей по пунктам назначения

Рисунок 4.11 – Результат выполнения перекрестного запроса таблицы распределения поездок водителей по пунктам назначения

Вычисляемые поля можно также использовать в заголовках столбцов перекрестных запросов. Например, запрос, формирующий распределение для каждого из водителей, расстояний, которые были пройдены ими по каждому дню (рис. 4.12), в качестве заголовков столбцов использует вычисляемое выражение:

Day([ВРЕМЯ_ПРИЕЗДА])

Рисунок 4.12 – Конструктор перекрестного запроса таблицы распределения километража водителей по числам месяца