Запросы являются одним из основных инструментов работы с БД. Они позволяют выбирать из таблиц нужные данные, объединять таблицы по связанным полям, редактировать содержимое таблиц, выполнять расчеты и многое другое.
Фактически, любое действие с БД является по сути запросом. Просто в Access многие запросы автоматизированы, и пользователь их не видит. Однако в прошлых лабораторных работах, когда вы просматривали таблицы, вводили в них данные, выполняли сортировку и фильтрацию, Access каждый раз формировал нужные запросы к БД.
Теперь составим запросы самостоятельно. Создание запроса осуществляется через вкладку «Создание» и возможно двумя способами: через «Мастер запросов» или «Конструктор». Мастер позволяет создавать несколько типовых запросов. Затем их всегда можно отредактировать в Конструкторе.
Существует большое число различных видов запросов. В данном практикуме мы выполним следующие:
– запрос на выборку полей одной таблицы;
– запрос на выборку полей двух таблиц;
– запрос на выборку с различными критериями отбора;
– запрос с вычисляемыми полями;
– запрос с итогами;
– запросы с параметром;
– перекрестный запрос.
Замечание: вам необходимо создать только один запрос каждого типа.
1. Запрос на выборку полей из одной таблицы – самый простой вид запросов. Он позволяет отобразить только те столбцы таблицы, которые нам нужны. Чаще всего они используются для выборок из больших таблиц, которые содержат 10 столбцов и более.
В нашем примере выполним выборку из таблицы «Магазин». Отобразим столбцы «Название», «Нас.пункт» и «Адрес».
Данный запрос можно выполнить как с помощью мастера, так и с помощью Конструктора.
Мастер запросов:
В последнем окне задайте имя запроса и нажмите «Готово». Название запроса должно отражать сущность содержащихся в нем данных: «Адреса магазинов».
Результат:
Этот же запрос можно создать или изменить с помощью Конструктора. Запустите конструктор и добавьте таблицу, из которой будете делать запрос. Нужные столбцы отбираются двойным кликом или перетаскиванием.
2. Запрос на выборку полей двух таблиц выполняется полностью аналогично, за исключением того, что в запрос добавляются поля из двух или более связанных таблиц.
В нашем примере создадим запрос «Наличие товаров в магазинах», отобразим в запросе название магазина и населенный пункт, наименование и ед. измерения товара, количество на складе:
В результате получим:
3. Запрос на выборку с различными критериями отбора напоминает фильтрацию данных: нам нужно отобразить не все записи, а только удовлетворяющие какому-то критерию.
Выберем магазины в Самаре, в которых в феврале продавался сахар (любой). Также отсортируем запрос по магазинам и количеству проданного сахара. Для этого запроса нам потребуются таблицы «Магазин», «Продажа», «Товар».
Требование: запрос должен состоять минимум из двух условий и возвращать не менее 2 записей! Сортировка обязательно минимум по 1 полю.
Критерии отбора указываются в Конструкторе в строке «Условие отбора». Например, для поля «Нас_пункт» напишем "Самара" (в кавычках, это текстовая константа).
Для построения сложных условий удобно пользоваться мастером 
во всплывающем меню. В нем можно найти все доступные в запросах данные (из таблиц, других запросов, форм), функции и операторы:
Диапазон дат или значений можно задать с помощью оператора Between. Даты нужно указывать между символами ##.
Between #01.02.2014# And #28.02.2014#
А, например, даты за последние 30 дней можно получить с помощью функции Date() – текущая дата. Напомним, что дата представляется в памяти как число ней, прошедших с 01.01.1990 (=0).
>=Date()-30
Для выбора текстовых значений используется оператор Like. В образце отбора можно использовать маски: * обозначает любую последовательность символов,? один любой символ. Регистр символов не учитывается.
Like "*сахар*"
Если бы мы написали в условии отбора просто "Сахар", то ничего бы не нашлось, т.к. у нас нет товара в точности с таким именем.
Примеры других запросов вы можете найти в справке 
по Access (F1) в разделе «Примеры условий запроса».
Сортировка столбцов указывается в соответствующей строке. Порядок сортировки определяется порядком столбцов в запросе.
Но нужно ли нам отображать город? Мы и так знаем, что в запросе отображаются только магазины из Самары. Поэтому столбец «Нас_пункт» можно скрыть, сняв флажок «Вывод на экран».
В нашей маленькой БД нашлось только 2 таких записи:
4. Запрос с вычисляемыми полями. Вычисления – одна из наиболее распространенных функций запросов. Благодаря им, нам не нужно хранить в базе, например, сумму продажи, мы всегда можем вычислить ее через количество и цену.
Построим запрос «Стоимость продаж» из таблиц «Продажа» и «Товар». Отобразим дату продажи, номер чека, название товара, количество, единицы измерения, цену и сумму.
Первые столбцы добавляются в запрос как обычно.
Формула для вычисления суммы (цена*кол-во) записывается прямо в строке «Поле». Основные правила:
– сначала указывается название будущего столбца, ставится двоеточие, потом пишется формула:
Название: формула
– названия полей таблиц, с которыми выполняются действия, и самих таблиц, пишутся в [], между таблицей и полем ставится восклицательный знак. Название таблицы можно не писать, поле с таким именем встречается только в одной таблице
[Таблица]![Поле]
[Поле]
Как и для условий отбора, можно воспользоваться мастером «Построить...».
В итоге получим:
Но в поле сумма должны отображаться денежные единицы! Решетки ##### означают, что число слишком длинное и не умещается в столбец.
Чтобы это исправить, вернемся в Конструктор. Правый клик по полю «Стоимость», пункт меню «Свойства». Справа откроется окно свойств. Зададим формат поля – денежный. Кроме того, не помешает отсортировать запрос по дате продажи и номеру чека.
Теперь наш запрос немного похож на настоящие чеки, идущие без перерыва на чековой ленте:
5. Запрос с итогами (запрос с группировкой). В предыдущем примере явно не хватает общей суммы по чеку. К сожалению, вывести ее в том же запросе не получится, нужно создать отдельный. Потом эти запросы можно будет вывести в общем отчете или на одной форме.
Добавим запрос «Сумма по чеку». Чтобы не повторять предыдущие действия, его мы будем строить не на основе таблиц, а на основе прошлого запроса «Стоимость продажи».
Добавим в новый запрос поля «Дата», «№ чека» и «Стоимость». Далее необходимо включить подведение итогов в запросе.
В Конструкторе появится строчка «Группировка». Группировка объединяет совпадающие значения в одну запись. Нам нужно сгруппировать чеки по номеру и дате, а стоимость – просуммировать (Sum). Будет лучше переименовать поле «Стоимость» в поле «Сумма_по_чеку», это делается также как и для формул – через двоеточие. Сумме чека также нужно присвоить тип «Денежная».
Доступны и другие действия группировки:
Avg – среднее значение;
Min, Max – минимальное и максимальное значения;
Count – подсчет количества сгруппированных записей;
StDev, Var – стандартное отклонение и дисперсия;
First, Last – первое и последнее по порядку значение.
Результат выполнения запроса:
6. Запрос с параметром. Не во всех случаях мы заранее знаем параметр отбора строк в запросе. Например, мы хотим выводить чеки за разные даты. Конечно, можно каждый раз открывать Конструктор и исправлять запрос. Но обычные пользователи БД этого не умеют. Они должны просто ввести дату и получить чеки за это число. А составить запрос – задача администратора БД или программиста.
Параметр запроса – это как раз то условие, которое мы будем запрашивать у пользователя.
Параметры указываются в условии запроса в квадратных скобках [] (не путать с именами полей в формулах!). В скобках следует написать пояснение для пользователя – что ему нужно ввести.
[Введите дату]
В результате при выполнении запроса выведется окно:
Введем 01.02.14 и получим:
Параметр может быть частью условия отбора или формулы. Например, найдем все чеки на сумму более или равную указанной пользователем:
>=[Введите минимальную сумму чека:]
Отбор через оператор Like может выглядеть так:
Like "*"+[Введите подстроку для поиска товара:]+"*"
Отбор по диапазону дат:
Between [Введите дату начала периода:] And [Введите дату конца периода:]
Здесь будет выдано два запроса подряд.
Пример использования параметра в формуле - вычисление суммы с произвольной скидкой:
Сумма со скидкой: [Сумма]*(100-[Введите скидку в %:])/100
Замечание: вы можете создать запрос с параметром как в условии отбора, так и в формуле.
7. Перекрестный запрос подобен сводной таблице в Excel. Он позволяет превратить значения из записей в заголовки столбцов.
Простые перекрестные запросы – из одной таблицы или запроса – создаются через Мастер.
Например, покажем наличие магазинов по адресам в различных населенных пунктах:
Результат:
Если бы у нас было несколько магазинов по одному адресу, то в соответствующих ячейках отобразились бы 2, 3 и т.д. Кроме того, Access добавил столбец «Итоговое значение» с указанием общего числа таких магазинов.
Более сложные запросы (из нескольких таблиц, с вычислениями, условиями отбора) создаются в конструкторе.
Создадим перекрестный запрос, в котором покажем продажи товаров за указанный диапазон дат.
Сначала создадим обычный запрос с группировкой:
Теперь изменим его тип на «перекрестный». Вместо строки «Вывод на экран» появится «Перекрестная таблица». Там нужно указать, какие поля станут заголовками строк, какие – заголовками столбцов, какие – значениями:
Результат:
К сожалению, в перекрестных запросах имеются серьезные проблемы с округлением. Формат поля также не влияет на вывод значений.
В отчет необходимо вставить представление каждого запроса в Конструкторе и результат его выполнения (скриншотом или через экспорт, как для таблиц). Снабдите каждый запрос кратким пояснением – какие именно данные выбраны и из каких таблиц. Если условия запроса или формулы не умещаются на скриншоте, выпишите их отдельно.
Л.р. 4. Создание форм
Формы необходимы для более удобного редактирования таблиц и просмотра запросов. Соответственно, различают формы, ориентированные на ввод и редактирование данных, и формы, ориентированные на просмотр.
В данном практикуме мы создадим только формы для просмотра:
– автоформы в один столбец, табличную и ленточную;
– составные формы (подчиненную и связанную).
Формы можно создавать на основе таблиц или запросов. Автоформы в один столбец, табличная и ленточная создаются на основе одной таблицы, а составные формы - на основе нескольких связанных таблиц.
Как и запросы, формы можно создавать через Мастер (автоформы) или Конструктор. Некоторые формы вынесены непосредственно на Ленту.
1. Добавим автоформу в один столбец. Это самый простой вид форм, на которой все поля располагаются одно под другим. Отобразим на ней содержимое таблицы «Товар», за исключением цены.
В раскрывающемся списке «Другие формы» выберите «Мастер форм». Выберите нужную таблицу или однотабличный запрос. С помощью кнопок со стрелками переместите выбранные поля в правый столбец.
Вид формы - «в один столбец».
Выберите любой стиль, который Вам нравится:
Задайте имя формы:
Результат:
На такой форме отображается одна запись «за раз». Переход между записями осуществляется с помощью кнопок внизу. Если форма создана на основе таблицы, ее можно использовать как для просмотра, так и для ввода и редактирования данных.
2. Ленточная автоформа создается полностью аналогично. Отличается лишь выбор вида формы на втором шаге и результат. На ленточной форме все записи отображаются вместе, как в таблице:
Данная форма создана на основе запроса «Сумма со скидкой».
Решетки в столбце «Дата» отображаются из-за того, что автоматической ширины столбца недостаточно для отображения даты. В то же время столбцы «Сумма» и «Сумма со скидкой» слишком широкие.
Исправим это в Кострукторе:
Здесь можно изменить размер и положение любых полей, настроить их свойства (справа).
Замечание: используйте Конструктор по мере необходимости, если автоматически созданная форма неудовлетворительна.
3. Табличная автоформа создается также и выглядит полностью как обычная таблица или запрос:
Такие формы используются редко, обычно, как вспомогательные или часть составных форм.
В Конструкторе табличная форма отображается как форма в один столбец.
4. Подчиненные формы отображают одновременно несколько связанных таблиц. Их также можно создать с помощью Мастера.
В примере покажем на одной форме товары, имеющиеся в каждом магазине.
На первом шаге необходимо выбрать поля из нескольких связанных таблиц:
На втором шаге выбираем подчиненную форму и вид ее представления (какая таблица будет основной, какая – подчиненной). Основная форма отображается по одной записи, и одновременно можно увидеть все связанные с данной записи в подчиненной форме.
Внешний вид подчиненной формы можно выбрать любой:
Дальнейшие шаги пояснений не требуют.
В результате будет создано две формы. Но подчиненная отображается как еще одно поле на главной форме. Размещение элементов на форме потребовалось отредактировать в Конструкторе.
Замечание: для свободного перемещения элементов на форме может потребоваться отключить кнопку «Табличный» или «В столбик» на вкладке «Упорядочить».
5. Связанные формы также создаются из нескольких таблиц или подчиненных запросов. При этом на главной форме размещается кнопка, нажатие на которую вызывает подчиненную форму.
Связанные формы удобнее использовать, изменив настройки Access по умолчанию: формы лучше отображать не на вкладках, а как обычно. Чтобы настройки вступили в силу, нужно перезапустить БД.
Создаются связанные формы аналогично подчиненным, только на втором шаге нужно указать тип формы – связанная.
Связанные формы в примере созданы на основе таблиц «Продажа», «Товар», «Магазин». Вид форм отредактирован в Конструкторе.
Таком образом, мы можем увидеть, где и когда был продан выбранный товар. При перелистывании записей на главной форме, содержимое подчиненной или связанной формы сразу меняется.
В отчет по работе необходимо вставить скриншоты готовых форм. Связанные формы можно отдельными скриншотами.
Л.р. 5. Создание отчетов
Отчеты необходимы для вывода сведений из БД на печать. Обычно отчеты содержат итоговые сведения из запросов.
Отчеты, как и формы, и запросы, можно создавать с помощью Мастера отчетов (автоотчеты) или через Конструктор. Любые отчеты можно отредактировать в Конструкторе.
Создадим следующие типы отчетов:
– автоотчет в один столбец;
– отчет по нескольким полям одной таблицы;
– отчет с группировкой и итогами.
1. Автоотчет в один столбец создается с помощью Мастера и позволяет вывести список всех значений какого-либо поля. Например, отобразим список всех указанных в БД товаров (наименований) в алфавитном порядке.
Создадим отчет с помощью Мастера.
В первую очередь, необходимо выбрать поля из таблиц и/или запросов, которые будут отображаться в БД.
Затем указываем порядок и направление сортировки (если требуется).
Макет отчета можно выбрать любой:
· табличный подходит для большинства отчетов;
· в столбец выводит данные наподобие формы, подходит для отображения полей с большим содержимым (длинный текст, рисунки);
· выровненный стоит использовать в отчетах с большим количеством столбцов, где они имеют разный размер или просто не умещаются в одну строчку таблицы.
Желательно использовать флажок «Настроить ширину полей для размещения на одной странице», иначе часть столбцов может «уехать» на другие страницы.
Далее выбирается стиль оформления (в примере выбран стиль «Справедливость») и задается имя отчета (Товары по наименованию). Имя лучше изменить – таблицы БД называются в единственном числе, а отчеты не обязательно.
Результат:
Ширина столбца, очевидно, недостаточна. Исправим структуру отчета через Конструктор (кнопка «Закрыть окно предварительного просмотра»).
Поскольку столбец всего один, его можно растянуть на всю ширину страницы.
Полученный отчет можно распечатать из Access или экспортировать в Word или другую программу.
Для добавления в отчет по лабораторной работе, выполните экспорт в Word. В конечном документе исправим шрифт (заголовок напечатан белым цветом), уберем колонтитулы. Чтобы отделить отчеты друг от друга добавьте вокруг них границы (как для таблиц: выделить отчет, кнопка 
на вкладке «Главная»).
Результат:
Товары по наименованию
Наименование
Крупа гречневая разв.
Молоко, 3,5%
Пакет-майка
Посуда одноразовая, ложки, 5 шт.
Посуда одноразовая, набор
Посуда одноразовая, стаканы, 5 шт.
Посуда одноразовая, тарелки, 5 шт.
Сахар-песок разв.
Сахар-песок фас., 900г.
Сахар-рафинад фас, 350г
Соль йодированная разв.
Соль мелкая, 500г
Соль морская, 450г
Ткань "Сатин" в асс.
Ткань подкладочная
Черснослив разв.
2. В отчете из нескольких полей одной таблицы покажем сведения о магазинах.
В целом, процесс создания отчета аналогичен.
Однако на втором шаге предлагается выбрать группировку записей. Название группы будет выводиться в виде подзаголовка.
В данном случае достаточно логично объединить магазины по населенному пункту, в котором они находятся. В других вариантах можно обойтись без группировки.
Сортировку выполним по адресу.
При наличии группировки типы макетов будут другими:
· ступенчатый;
· блок;
· структура.
«Ступенчатый» и «блок» похожи, они представляют собой таблицу, в которой не будут повторяться одинаковые значения записей.
В «структуре» сгруппированные поля отображаются как подзаголовки.
В примере выберем ступенчатый макет.
Стиль – «Справедливость», название отчета «Магазины по городам».
Ширину столбцов также потребовалось отредактировать в Конструкторе, а также поменять порядок столбцов: хоть мы и сортируем магазины по адресу, логично первым столбцом показать название.
Результат экспорта в Word:
Магазины по городам