ЛАБОРАТОРНЫЕ РАБОТЫ MS ACCESS. Лабораторная работа 1 создание таблиц и схемы базы данных
 Скачать 1.89 Mb.
|
Лабораторная работа 4 |
| Функция | Описание |
| Sum | Вычисляет сумму всех значений заданного поля в каждой группе. Используется только для числовых и денежных поле. |
| Avg | Вычисляет среднее арифметическое всех значений заданного поля в каждой группе. Используется только для числовых и денежных поле. Значения Null не включаются в вычисления. |
| Min (Max) | Возвращает наименьшее (наибольшее) значение, найденное в заданном числовом поле внутри каждой группы. Для текстовых полей - наименьшее из символьных значений независимо от регистра. Значения Null игнорируется. |
| Count | Возвращает число записей, в которых значения данного поля отличны от Null. Для учета значений Null используется Count(*) |
Перекрестные запросы – запросы, результаты которых представляются в виде сводных кросс-таблиц. Кросс-таблица - это таблица с группировкой значений в двух направлениях: по горизонтальным строкам и вертикальным столбцам, на пересечении которых рассчитаны итоги по этим группам. Кроме того, таблица может включать общие итоги по строкам и столбцам Строки и столбцы кросс-таблиц содержат значения группируемых полей, например (рис.4.1) , группировка по названию разделов и тем с указанием общего количества разновидностей книг в области значений.
Группировка по темам, заголовки столбцов
Группировка по разделам, заголовки строк
 | Маркетинг | Менеджмент | Проза | Поэзия |
| Научная литература | 4  0 | 20 | | |
| Художественная литература | | | 5 Область данных 0 | 40 |
Рис.4.1 Пример кросс-таблицы, содержащей количество книг по разделам.
Запросы на создание таблиц – запросы, позволяющие сохранять свои результаты в качестве таблиц базы данных.
Запросы на обновление значений полей таблиц – запросы, которые вносят изменения в значения полей таблиц.
Запросы на добавление записей в таблицы – запросы, позволяющие записи одной таблицы добавлять в другую, причем объединяемые таблицы могут принадлежать как одной и той же, так и разным базам данных.
Запросы на удаление записей – запросы, которые удаляют все записи или только отобранные по условию, заданному пользователем.
Задание
Создать в режиме Конструктора по два запроса каждого вида выборки и по одному запросу всех остальных типов.
Последовательность выполнения работы
Внимание! После открытия файла базы данных необходимо включить параметры содержимого, для этого следует нажать кнопку «Параметры» в верхней части окна В открывшемся диалоговом окне выбрать опцию «Включить это содержимое».
Создание запросов на выборку с параметром
Запрос 1
Создайте запрос, выводящий на экран список книг по теме «Маркетинг». Для этого выполните команду Создание/ Другие/ Конструктор запросов (рис. 4.2).
Рис. 4.2 Группа команд Создание/Другие
В диалоговом окне Добавление таблицы выберите таблицы Тема и Книга. Закройте диалоговое окно.
В окне Конструктора из таблицы Тема двойным щелчком выберите поле Название темы, а из таблицы Книга поля: Автор, Название, Издательство, Год издания.
Переведите курсор в столбец Название темыбланка запроса и в строке Условие отбора укажите в квадратных скобках следующую фразу: [Введите название темы].
Чтобы запустить запрос, на панели инструментов выберите кнопку с пиктограммой восклицательного знака.
Появится диалоговое окно, запрашивающее ввод названия литературной темы. Укажите Маркетинг. Результатом будет список книг по данной теме.
Закройте и сохраните запрос под названием Поиск книг по заданной теме.
В окне «Все объекты Access», расположенном в левой части окна, двойным щелчком выберите созданный запрос, чтобы запустить его снова. Теперь укажите тему Менеджмент. Убедитесь, что получили правильный ответ на свой запрос. Закройте запрос.
Запрос 2
Создайте запрос, выводящий на экран список книг, выданных библиотекой за отчетный период. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицы Книга, Журнал и Читатель. Закройте диалоговое окно.
В окне Конструктора из таблицы Книга двойным щелчком выберите поля: Автор, Название, из таблицы Читатель – поля: Фамилия, Имя, а из таблицы Журнал выберите поле Номер билета и дважды выберите поле Дата выдачи, так как оно понадобится нам в двух случаях.
Переведите курсор в первый столбец Дата выдачибланка запроса, в строке Условие отбора укажите: >=[Введите дату начала периода].
Переведите курсор во второй столбец Дата выдачи, в строке Условие отбора укажите: <=[Введите дату окончания периода], ав строкеВывод на экран отключите флажок.
Чтобы запустить запрос, на панели инструментов выберите кнопку с пиктограммой восклицательного знака.
Появится диалоговое окно, запрашивающее ввод даты начала отчетного периода. Укажите, например, 01.09.11 и нажмите ОК. В окне ввода даты окончания отчетного периода укажите, например, 30.11.11 Результатом будет список книг, выдававшихся библиотекой в течение осени 2011 года.
Закройте и сохраните запрос под названием Книги, выданные за отчетный период.
Убедитесь, что получили правильный ответ на свой запрос. Закройте запрос.
Создание запросов на выборку с функцией
Запрос 3
Создайте запрос, обеспечивающий поиск сведений о книгах, выданных в апреле 2011 года. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицы Книга, Журнал, Читатель. Закройте диалоговое окно.
В окне Конструктора из таблицы Книга выберите поля: Автор и Название, из таблицы Читатель – поля: Номер билета, Фамилия, Имя, из таблицы Журнал – поле Даты выдачи.
Переведите курсор в столбец Дата выдачи бланка запроса, в строке Условие отбора нажмите правую кнопку мыши. Из контекстного меню выберите команду Построить.
В окне построителя выражений в левом столбце найдите группу Функции, двойным щелчком раскройте группу и выберите Встроенные функции.
В среднем столбце построителя выражений двойным щелчком выберите функции Дата/время.
В правом столбце построителя выражений двойным щелчком выберите функцию Month. Данная функция извлекает из даты номер месяца.
В верхней части построителя выражений удалите все лишнее, оставив только Month ( ). Установите курсор между скобками функции.
В левом столбце найдите группу Таблицы, двойным щелчком раскройте группу и выберите таблицу Журнал.
В среднем столбце двойным щелчком выберите поле Дата выдачи.
В верхней части построителя выражений должна получиться конструкция Month ([Журнал]![Дата выдачи]). Удалите все лишнее. Добавьте к ней справа элемент =[введите номер месяца].
В итоге получается следующее выражение:
Month ([Журнал]![Дата выдачи]) =[введите номер месяца].
Нажмите кнопкуОК.
Запустите запрос. Появится диалоговое окно, запрашивающее ввод номера месяца. Укажите 4. Результатом будут сведения о том, какие книги и кому выдавались в апреле.
Закройте и сохраните запрос под названием Книги, выданные в течение месяца.
Запрос 4
Создайте запрос, обеспечивающий поиск книг по фрагменту названия. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицу Книга. Закройте диалоговое окно.
В окне Конструктора из таблицы Книга выберите поля: Номер книги, Автор, Название, Издательство, Год издания.
Переведите курсор в столбец Название бланка запроса, в строке Условие отбора укажите Like[Введите название книги]&* .
Функция Like позволяет в диалоговом окне, запрашивающем ввод названия книги, указывать не всё название целиком, а хотя бы первые буквы.
Запустите запрос. В диалоговом окне укажите Стих. Результатом будет список книг, названия которых начинаются со слога Стих. Закройте и сохраните запрос под названием Поиск книг по фрагменту названия.
Создание запросов на выборку с арифметической операцией
Запрос 5
Создайте запрос, выводящий на экран сведения о читателях, задержавших книги на руках дольше положенного срока. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицы Книга, Журнал, Читатель. Закройте диалоговое окно.
В окне Конструктора из таблицы Читатель выберите поля: Номер билета, Фамилия, Имя, из таблицы Книга – поля: Автор и Название, из таблицы Журнал – поля: Дата возврата план и Дата возврата факт.
Сохраните запрос под названием Список должников.
Переведите курсор в чистый столбец, расположенный справа от столбца Дата возврата факт бланка запроса. В строке Поле нажмите правую кнопку мыши и из контекстного меню выберите команду Построить.
В верхней части окна построителя выражений введите Разница в днях:.
В среднем столбце построителя выражений находится список полей данного запроса. Двойным щелчком выберите поле Дата возврата факт, выберите кнопку со знаком минус, двойным щелчком выберите поле Дата возврата план.
В верхней части построителя выражений должна получиться конструкция:
Разница в днях: [Дата возврата факт] – [Дата возврата план]).
Удалите все лишнее. Нажмите кнопкуОК.
В строке Условие отбора данного столбца укажите: >0.
Запустите запрос.
Результатом будут сведения о том, кто из читателей, какие книги и на сколько дней задержал.
Примените сортировку по полю Номер билета.
Закройте и сохраните запрос.
Запрос 6
Создайте запрос, выводящий на экран сведения о размере пени за несвоевременную сдачу книг в библиотеку из расчета: 2 рубля за каждый день. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы перейдите на закладку Запросы и выберите запрос Список должников. Закройте диалоговое окно.
В окне Конструктора из запроса Список должников выберите поля: Номер билета, Фамилия, Имя, Автор, Название, Разница в днях.
Переведите курсор в чистый столбец, расположенный справа от столбца Разница в днях бланка запроса. В строке Поле нажмите правую кнопку мыши и из контекстного меню выберите команду Построить.
В верхней части окна построителя выражений введите Пени:
В левом столбце построителя выражений раскройте группу Запросы и выберите запрос Список должников.
В среднем столбце построителя выражений двойным щелчком выберите поле Разница в днях, выберите на клавиатуре знак умножить, затем цифру 2.
В верхней части построителя выражений должна получиться конструкция:
Пени: [Список должников]![ Разница в днях] *2.
Нажмите кнопкуОК.
Запустите запрос.
Результатом будут сведения о том, кто из читателей, сколько должен заплатить за каждую задержанную книгу.
Закройте и сохраните запрос под названием Расчет размера пени.
Создание запросов на выборку с подведением итогов
Запрос 7
Создайте запрос, выводящий на экран сведения о размере штрафов, уплаченных недобросовестными читателями. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы перейдите на закладку Запросы и выберите запрос Расчет размера пени. Закройте диалоговое окно.
В окне Конструктора из запроса Расчет размера пени выберите поля: Номер билета, Фамилия, Имя, Пени.
На ленте инструментов Конструктор выберите кнопку Итоги (). В бланк запросы будет добавлена строка Групповая операция.
Переведите курсор в столбец Пени бланка запроса и в строке Групповая операция вместо операции Группировка из раскрывающегося списка выберите Sum.
Запустите запрос.
Результатом будут сведения о том, кто из читателей сколько должен заплатить за все книги, которые он задержал.
Переименуйте столбец Sum-Пени. Для этого вернитесь в режим Конструктора с помощью кнопки Режим, расположенной на ленте инструментов Главная.
Переведите курсор в столбец Пенибланка запроса и в строке Поле укажите Штраф: Пени.
Снова запустите запрос.
Закройте и сохраните запрос под названием Расчет размера штрафов.
Запрос 8
Создайте запрос, выводящий на экран сведения об общем количестве книг в каждом из разделов фонда библиотеки. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы на закладке Таблицы выберите таблицы: Раздел, Тема и Книга. Закройте диалоговое окно.
В окне Конструктора из таблицы Раздел выберите поле Название раздела, из таблицы Книга – поле Кол-во экземпляров.
На панели инструментов выберите кнопку Итоги ().
Переведите курсор в столбец Кол-во экземпляров бланка запроса и в строке Групповая операция из раскрывающегося списка выберите операцию Sum.
Запустите запрос.
Результатом будут сведения о том, сколько всего книг содержат литературные разделы.
Переименуйте столбец Sum-Кол-во экземпляров. Для этого вернитесь в режим Конструктора с помощью кнопки Вид на панели инструментов.
Переведите курсор в столбец Кол-во экземпляров бланка запроса и в строке Поле укажите Всего книг: Кол-во экземпляров.
Снова запустите запрос.
Закройте и сохраните запрос под названием Сколько книг содержит раздел.
Создание перекрестных запросов
Чтобы оценить популярность литературных тем, создайте запрос, выводящий на экран количество книг, выданных по каждой теме. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицы Раздел, Тема, Книга и Журнал. Закройте диалоговое окно.
В окне Конструктора из таблицы Раздел выберите поле Название раздела, из таблицы Тема – поле Название темы, а из таблицы Журнал – поле Номер книги.
Выберите команду Конструктор/ Тип запроса/Перекрестный (рис 4.2). В бланк запроса будет добавлены строки Перекрестная таблица и Групповая операция.
Рис. 4.3 Группа команд Конструктор/ Тип запроса
Переведите курсор в столбец Название раздела бланка запроса и в строке Перекрестная таблица из раскрывающегося списка выберите Заголовки строк.
Переведите курсор в столбец Название темыбланка запроса и в строке Перекрестная таблица из раскрывающегося списка выберите Заголовки столбцов.
Переведите курсор в столбец Номер книгибланка запроса, в строке Перекрестная таблица из раскрывающегося списка выберите Значение, а в строке Групповая операция из раскрывающегося списка выберите Count.
Запустите запрос. Результатом будет кросс-таблица (рис.4.3), содержащая сведения о том, сколько книг из какого раздела по какой теме было выдано.
Рис. 4.4 Результат выполнения перекрестного запроса.
Закройте и сохраните запрос под названием Количество книг, выданных по теме.
Создание запроса на создание таблицы
Создайте запрос, выводящий на экран список научной литературы, которой располагает библиотека, и сохраняющий данные сведения в таблице Научная литература. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицы Раздел, Тема, Книга. Закройте диалоговое окно.
В окне Конструктора из таблицы Раздел выберите поле Название раздела, из таблицы Тема – поле Название темы, а из таблицы Книга – поля: Автор, Название, Издательство, Год издания.
Переведите курсор в столбец Название раздела бланка запроса, в строке Условие отбора укажите Научная литература, а в строке Вывод на экран уберите флажок.
Выберите команду Выберите команду Конструктор/ Тип запроса/Создание таблицы. В диалоговом окне Создание таблицы укажите название создаваемой таблицы Научная литература, переключатель оставьте в положении В текущей базе данных и нажмите кнопку ОК.
Запустите запрос.
Появится сообщение о числе записей, которые будут помещены в новую таблицу в результате выполнения запроса. Нажмите кнопку Да.
Закройте и сохраните запрос под названием Список научной литературы.
В окне Все объекты Access, расположенном в левой части окна, убедитесь, что таблица Научная литература действительно создана и содержит правильные данные.
Аналогичным образом, с помощью запроса, создайте таблицу Художественная литература.
Создание запроса на обновление значений поля таблицы
Проведите переоценку стоимости книг. Создайте запрос, увеличивающий размер цены каждой книги на 10%. Для этого выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицу Книга. Закройте диалоговое окно.
В окне Конструктора из таблицы Книга выберите поле Цена.
Выберите команду Конструктор/ Тип запроса/Обновление. В бланк запроса будет добавлена строка Обновление.
Переведите курсор в столбец Цена бланка запроса. В строке Обновление нажмите правую кнопку мыши и из контекстного меню выберите команду Построить.
Создайте следующее выражение: [Книга]![Цена]*1,1.
Запустите запрос. Появится сообщение о числе записей, которые будут обновлены в результате выполнения запроса. Нажмите кнопку Да.
Закройте и сохраните запрос под названием Обновление цен.
В окне Все объекты Access, расположенном в левой части окна, откройте таблицу Книга и убедитесь, что цены на книги в таблице Книга действительно увеличились на 10%.
Создание запроса на добавление записей
Предположим, что часть книжного фонда одной библиотеки переводится в другую библиотеку. Возникает необходимость объединения данных о книгах двух фондов. Создайте запрос, который записи о книгах одной таблицы добавит в другую. Для этого в окне Все объекты Access, расположенном в левой части окна, выделите таблицу Книга, выберитекомандуконтекстного менюКопировать, а затемкомандуконтекстного менюВставить. В диалоговом окнеВставка таблицывведите имятаблицы Книга1, переключатель оставьте в положении Структура и данные, нажмите кнопку ОК.
Откройте таблицу Книга1 в режиме ввода данных.Щелчком на сером фоне слева от строк выделите и удалите все данные, введите новые (табл.4.2.):
Таблица 4.4
Данные таблицы Книга1
| Номер книги | Код темы | Автор | Название | Издательство | Год издания | Кол-во экземпляров | Цена |
| 9 | 1 | Виктор Гальперин | Макроэконо-мика | Феникс | 2003 | 20 | 1000 |
| 10 | 1 | Василий Тарасевич | Микроэконо-мика | Феникс | 2003 | 20 | 1000 |
| 11 | 1 | Майкл Хизрич | Экономикс | Феникс | 2003 | 20 | 1000 |
Закройте таблицу Книга1.
Выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицу Книга1. Закройте диалоговое окно.
В окне Конструктора из таблицы Книга1 двойным щелчком выберите символ звездочки (*). Это означает, что данные для добавления будут браться из всех полей таблицы Книга1.
Выберите команду Конструктор/ Тип запроса /Добавление. Появится окно Добавление, где необходимо указать имя таблицы, в которую предполагается добавлять данные, и какой БД она принадлежит. Из раскрывающегося списка в поле Имя таблицы выберите Книга, переключатель оставьте в положении В текущей базе данных и нажмите кнопку ОК.
В бланке запроса будет добавлена строка Добавление, в которой указываются те поля таблицы Книга, куда добавляются данные.
Запустите запрос. Появится сообщение о числе записей, которые будут добавлены из таблицы Книга1 в таблицу Книга в результате выполнения запроса. Нажмите кнопку Да.
Закройте и сохраните запрос под названием Добавление книг.
В окне Все объекты Access, расположенном в левой части окна, просмотрите перечень имеющихся таблиц и убедитесь, что таблица Книга действительно пополнилась данными.
Создание запроса на удаление записей
Создайте запрос на удаление записей из таблицы Книга1.
Выполните команду Создание/ Другие/ Конструктор запросов.
В диалоговом окне Добавление таблицы выберите таблицу Книга1. Закройте диалоговое окно.
В окне Конструктора из таблицы Книга1 двойным щелчком выберите символ звездочки (*). Это означает, что данные для удаления будут браться из всех полей таблицы Книга.
Выберите команду Конструктор/ Тип запроса /Удаление. В бланк запроса будет добавлена строка Удаление со значением Из.
Запустите запрос. Появится сообщение о числе записей, которые будут удалены из таблицы Книга1 в результате выполнения запроса. Нажмите кнопку Да.
Закройте и сохраните запрос под названием Удаление книг.
В окне Все объекты Access, расположенном в левой части окна, просмотрите перечень имеющихся таблиц, откройте таблицу Книга1 и убедитесь, что таблица Книга1 пуста.
Самостоятельная работа.
- создать запрос на расчет величины залога (составляющей 150 % от цены) для книг, стоимость которых превышает 900 рублей.
- вывести книги, изданные между 2001 и 2003 годами, выполнить сортировку по автору;
- вывести книги указанного издательства, выполнить сортировку по теме и по автору;
-вывести книги указанного раздела, находящиеся на руках у читателей, с указанием фамилии и имени читателя.
Контрольные вопросы
Какому требованию обязательно должны удовлетворять таблицы, выбираемые источниками данных для запроса?
Назовите функции и групповые операции, используемые в Access.
Перечислите типы запросов, объясните особенности их применения.
Что представляет собой кросс-таблица, каковы ее структурные элементы?
Как переименовать столбец таблицы, являющейся результатом запроса?