CC BY
31
ИНФОРМАЦИОННОЕ
Дмитриева Марина Валерьевна Дмитриев Юрий Игоревич
СОВРЕМЕННЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ:
СУБД ACCESS 8.0
ФОРМИРОВАНИЕ ЗАПРОСОВ И СОЗДАНИЕ ОТЧЕТОВ
В предыдущей статье были рассмотрены вопросы, связанные с созданием и редактированием таблиц и форм. В предлагаемой работе приведены практические задания по формированию запросов и созданию отчетов.
После того, как база данных сформирована, надо научиться извлекать из нее информацию. Извлечь информацию из базы данных можно с помощью различных запросов. MS Access располагает удобными средствами визуального проектирования объектов и, в частности, запросов.
Тема 5. Формирование запросов
Одна из основных задач системы управления базой данных - обеспечение запросов пользователей. Запрос можно рассматривать как вопрос о некоторых дан-
<Wn
"Мосле Лого, как. ёауа faHHux сформировала, Надо ftatyimfacfr и^&ллека^л uf Лее иЛформагци^,
ных, хранящихся в базе данных. Например, сколько часов отработал сотрудник Краснов в марте? Запрос можно интерпретировать как распоряжение, на основании которого должны быть отобраны данные из базы. Например, можно сформировать запрос на определение всех сотрудников, работающих в заданный период времени. Источником построения запроса являются таблицы базы данных. В результате его выполнения также формируется таблица. Можно построить и сохранить часто используемые запросы. С помощью Access можно создавать запросы разных типов. Самым распространенным типом является запрос на выборку. Запрос в Access можно создавать либо вручную, либо с помощью Мастера запросов.
Практическая работа 5 Формирование запросов на выборку
1. Сформируйте запрос для получения информации о днях и часах работы определенного сотрудника, например, Петрова. • Выберите вкладку Запросы и нажмите на кнопку Создать. В окне Новый запрос выберите Конструктор и нажмите кнопку Ok.
• В окне Добавление таблицы выберите таблицу Учет времени работы и нажмите на кнопку Добавить. После появления в окне списка полей таблицы нажмите на кнопку Закрыть.
• В первую ячейку строки Поле перетащите из списка полей таблицы Учет времени работы поле Фамилия, во вторую - Текущая дата, в третью - Число часов.
В первую ячейку строки Условие отбора поместите выражение: «Петров». После выполнения описанных действий окно примет вид как на рисунке 1. 2. Выполните запрос, либо щелкнув на кнопке панели инструментов Запуск, либо выбрав пункт меню Запрос, Запуск. Для данных, хранящихся в текущий момент в базе данных Учет времени работы, в результате выполнения запроса будет сформирована таблица 1.
Фамилия Текущая дата Число часов
Петров 01.04.99 5
Петров 11.05.99 5
Петров 02.06.99 3
Таблица 1.
фор-морОлШе ¿шросО- Ял выборку....
3. Сохраните запрос, выполнив команду меню Файл, Сохранить, имя запроса - Сотрудник Петров.
4. Постройте запрос, который формирует таблицу, содержащую информацию о днях и часах работы сотрудника с фамилией Сидоров. Сохраните запрос под именем Работа Сидорова.
5. Сформируйте запрос для получения информации обо всех сотрудниках, работающих в определенный день. Сохраните под именем Определенный день.
6. Сформируйте запрос для получения информации обо всех сотрудниках, работающих 5 часов в день. Сохраните под именем Часы работы.
7. Сформируйте запрос о сотрудниках,
Рисунок 1.
Мосле появления в окне списка полей Лаблщи, Ллфми&е кнопку Закриви.
работающих после 7 июня 1999 года. При построении запроса в строку Условие отбора следует поместить выражение >07. 06. 99. Сохраните под именем Работа после. 8. Предположим, что теперь нас интересует полная информация обо всех сотрудниках за определенный период времени (например, с 20 апреля 1999 года по 15 мая 1999 года). Для того чтобы ее получить при построении запроса, в строку Условие отбора следует ввести выражение between 20. 04. 99 and 15. 05. 99. Постройте запрос и сохраните под именем Определенный период.
'иполни^е. ¿апрос, щелкнув Ла кнопке панели иЛсйрумеЛ&ов Запуск...
9. Исходную таблицу, построенные запросы и все таблицы, полученные в результате выполнения запросов, сохраните в своей папке и на дискете, покажите преподавателю.
Тема 6. Формирование запросов с параметром
Запрос с параметром позволяет определить одно или несколько условий отбора информации во время выполнения запроса. В результате выполнения преды-
^ормирование ¿апросоов с параметром,,,
дущего задания был построен запрос с именем Сотрудник Петров. Выполнение его приводило к формированию таблицы, в которой хранилась информация о работе сотрудника Петрова. Предположим, что директору фирмы требуется информация о работе каждого сотрудника. Если сотрудников немного, то можно для каждого из них сформировать соответствующий запрос. Если число сотрудников велико, то такой путь вряд ли приемлем. Access позволяет формировать запросы с параметром. В рассматриваемом случае параметром является фамилия сотрудника. При выполнении запроса с параметром появится диалоговое окно, в котором требуется указать фамилию того сотрудника, время работы которого нас интересует.
^формфцй&е ¿лорос, полугшбА
и-Нформалума о любом
Практическая работа 6 Формирование запросов с параметром
1. Сформируйте запрос, позволяющий получить информацию о любом сотруднике из таблицы Учет времени работы.
• Откройте файл, хранящий базу данных. Выберите вкладку Запросы, нажмите на кнопку Создать. В окне Новый запрос выберите Конструктор и нажмите кнопку Ок. После выделения и добавления необходимых таблиц закройте окно Добавление таблицы.
• Как и раньше, в первую ячейку строки Поле перенесите из списка полей поле Фамилия, во вторую - Текущая дата, в третью - Число часов.
• В первую ячейку строки Условие отбора поместите выражение: =[фамилия сотрудника]. Выполните запрос, нажав на кнопку Запуск или выбрав пункт меню Запрос, Запуск. При выполнении запроса высветится диалоговое окно, в котором надо задать значение переменной фамилия сотрудника, то есть написать фамилию сотрудника, информация о котором нас интересует, и нажать кнопку Ок. После выполнения
запроса с параметром, значение которого Сидоров, должна быть сформирована следующая таблица 2, а для сотрудника Костина - таблица 3. Сохраните запрос под именем Работа сотрудника. 2. Создайте запрос, в результате выполнения которого выдается информация о времени работы сотрудника в определенный день, который задается параметром. Сохраните его под именем День работы. 3 . Создайте запрос для формирования информации о сотрудниках, работающих после интересующей даты, которая задается параметром. При формировании запроса в строку Условие отбора следует поместить выражение >[дата]. При выполнении запроса потребуется задать значение параметра, то есть ввести интересующую нас дату. Сохраните запрос под именем После даты.
4. Создайте запрос для получения информации обо всех сотрудниках за определенный период времени. При формировании запроса в строку Условие отбора следует поместить выражение between [начало] and [конец]. При выполнении запроса потребуется задать начальную дату, а затем конечную для того периода времени, который нас интересует. Сохраните запрос под именем Работа за период. 5 . Сформируйте запрос, в результате выполнения которого выводится информация обо всех сотрудниках и всех часах, отработанных ими.
• Выберите вкладку Запросы и нажмите кнопку Создать. Далее выберите Конструктор для создания запроса и нажмите кнопку Ok. Выделите таблицу Учет времени работы и нажмите на кнопку Добавить. В окне запроса появляется список полей таблицы.
• Закройте окно Добавление таблицы с
Фамилия Текущая дата Число часов
Сидоров 01.04.99 3
Сидоров 05.04.99 5
Сидоров 11.05.99 3
Таблица 2.
Фамилия Текущая дата Число часов
Костин 01.04.99 4
Костин 03.04.99 8
Костин 12.05.99 4
Таблица 3.
Фамилия Sum Число часов
Киселев 8
Костин 16
Краснов 13
Крылов 10
Лавров 5
Петров 13
Селезнев 27
Сидоров 11
Таблица 4.
Фамилия Sum Число часов
Селезнев 27
Костин 16
Краснов 13
Петров 13
Сидоров 11
Крылов 10
Киселев 8
Лавров 5
Таблица 5.
помощью кнопки Закрыть. Далее перенесите поле Фамилия и Число часов
в первую и вторую ячейки строки Поле.
• Нажмите на кнопку Групповые операции. В бланке запроса появится строка с этим именем. Щелкните по полю с именем Число часов и из списка операций выберите операцию Sum. Сохраните запрос под именем Работа всех.
• В результате выполнения последнего запроса получилась следующая таблица 4. После сортировки таблицы в порядке убывания суммы выработанных часов получится таблица 5.
6. Выполните индивидуальное задание, сформулированное преподавателем.
Практическая работа 7 Формирование запросов к базе данных Моя библиотека
При выполнении заданий по построению баз данных с помощью Мастера была создана база данных Моя библиотека. Предлагалось заполнить таблицы базы данных, либо непосредственно записав информацию в таблицу, либо воспользовавшись созданной формой. Мастер баз данных, кроме таблиц, создал также формы и отчеты, и лишь запросы к базе данных пользователю необходимо сформировать самому. Сформируйте различные запросы для получения информации из вашей базы данных:
• о книгах определенного автора;
• о книгах, выпущенных некоторым издательством;
• о книгах, купленных вами до определенной даты;
• о книгах определенного раздела (например, детективах), изданных после 1997 года;
• об авторах, чьи работы были опубликованы в заданный период;
• об издательствах, в которых были выпущены книги из вашей библиотеки;
• о книгах, объем которых больше заданного значения;
• о том, в каких издательствах выпускались книги определенного автора (по данным вашей базы);
• о том, какие издательства в период с 1995 по 1999 год выпускали книги определенного раздела и название этих книг;
• о том, какая сумма была потрачена на приобретение литературы в определенный период времени.
Сформируйте запросы для ответа на интересующие вас вопросы.
Практическая работа 8 Формирование запросов к базе данных Ассортимент
В задании, посвященном обмену данными между различными приложениями Windows, таблица с именем Информация о товарах хранилась в рабочей книге Excel на листе с именем Полный Ассортимент. Целью выполнения задания было импортировать эту таблицу в Access для последующей ее обработки и формирования по ней различных запросов. Сформируйте различные запросы для получения информации из вашей базы данных:
• о ксероксах, названия которых содержат слово персональный;
• о факсах, названия которых содержит слово профессиональный;
• о товарах, стоимость которых больше заданной величины;
• о товарах, цена которых заключена в заданных пределах;
• о товарах, количество которых меньше заданного значения;
• о товарах, расходы на которые больше заданного значения.
Приведите примеры запросов, отличных от тех, которые были рассмотрены, и выполните их.
Тема 7. Создание отчетов
Access выводит данные в виде отчета. Отчеты бывают различных типов. При построении простого отчета информация выводится в табличной форме. Отчет можно создавать самостоятельно или использовать при составлении Мастер отчетов. Источником формирования отчета может быть как таблица, так и запрос.
Практическая работа 9 Создание отчетов
Создайте отчет, в котором хранится информация о работе сотрудников фирмы за весь период работы. 1. Выберите вкладку Отчеты и нажмите на кнопку Создать. Появится окно Но-
вый отчет, в котором следует выбрать строку Мастер отчетов и нажать на кнопку ОК.
• В окне Создание отчетов в поле ввода со списком Таблицы/Запросы следует выбрать таблицу, по которой строится отчет (в нашем случае таблица Учет времени работы).
• Далее следует перенести поля Фамилия и Число часов из окна Доступные поля в окно Выбранные поля и нажать на кнопку Далее.
• В следующем окне выберите сортировку в алфавитном порядке для фамилий и нажмите на кнопку Готово.
• Отчет построен. Его можно просмотреть и при желании распечатать. В построенном отчете фамилии располагаются в алфавитном порядке. Каждая фамилия встречается столько раз, сколько записей в таблице с соответствующей фамилией.
2. Создайте отчет, в котором для каждого сотрудника вычислено суммарное время работы за весь период. Этот отчет следует построить на основании запроса с именем Работа всех.
3. Постройте отчет на основании запроса с параметром Работа сотрудника и сформируйте отчет для сотрудников Селезнева и Сидорова.
4. Постройте различные отчеты по таблицам и запросам базы данных Моя библиотека.
5. Постройте отчет на основе таблицы Ассортимент.
Контрольные работы
После выполнения практических заданий учащийся должен уметь строить од-нотабличную базу данных, создавать формы, формировать различные запросы к базе данных, создавать отчеты. Приобретенные навыки учащийся должен продемонстрировать при построении базы данных из контрольной работы. Во всех заданиях требуется разработать однотаблич-ную базу данных и сформировать различные запросы на выборку и запросы с параметром, создать отчет
1. Создайте базу данных Курсовые работы. В таблице базы данных должна содержаться информация: номер записи, фамилия учащегося, фамилия преподавателя, дата сдачи работы, полученная оценка. Сформируйте запросы на получение информации:
• обо всех отличниках (учащихся, получивших оценку 5);
• об учащихся, сдавших работы в указанный период;
• об учащихся, сдавших курсовую работу определенному преподавателю, в заданный период с оценкой выше 3.
2. Создайте базу данных График обследования больных. В таблице базы данных должна содержаться информация: номер записи, фамилия пациента, дата обследования, поставленный диагноз. Сформируйте запрос для ответа на вопрос:
• кому из больных поставлен определенный диагноз;
• кто из пациентов обследовался в определенный период времени;
• кому из пациентов поставлен диагноз ОРЗ в определенный период.
3. Создайте базу данных Технический осмотр. В таблице базы данных должна содержаться информация: номер записи, дата сдачи, фамилия владельца автомобиля, марка машины. Сформируйте запрос на получение информации о том,
• кто из владельцев определенной марки машины прошел техосмотр в указанный период;
• в какие дни проходили технический осмотр машины определенной марки.
4. Создайте базу данных Журнал регист-раций пансионата. В таблице базы данных должна содержаться информация: номер записи, фамилия отдыхающего, дата заезда, номер комнаты, дата отъезда и отметка об оплате. Постройте запрос на получения сведений:
• о том, кто из отдыхающих не оплатил проживание;
• какие из комнат свободны на сегодняшний день;
• кто из отдыхающих проживал в некоторой комнате в определенный пе-
риод времени.
5. Создайте базу данных Журнал регистрации переговоров. В таблице базы данных должна содержаться информация: номер записи, фамилия звонившего, дата переговоров, продолжительность разговора, номер телефона. Сформируйте запрос для определения:
• фамилий звонивших по определенному номеру телефона;
• дней, продолжительность разговоров в которые превышала определенную величину;
• номеров телефонов, по которым звонил некоторый сотрудник.
6. Создайте базу данных Расписание экзаменов. В таблице базы данных должна содержаться информация: номер записи, дата сдачи экзамена, номер группы, название сдаваемого предмета. Сформируйте запрос для формирования:
• расписания экзаменов определенной группы;
• расписания сдачи экзамена по определенному предмету;
• номеров групп, сдающих экзамены в определенный день.
7. Создайте базу данных Учет канцелярских товаров. В таблице базы данных должна содержаться информация: номер записи, фамилия покупателя, дата покупки, купленный предмет, истраченная сумма. Сформируйте запрос для определения:
• покупателей, сделавших покупку на сумму, превышающую заданную;
• какие предметы были куплены за определенный период;
• какие суммы были истрачены покупателями в определенный день.
8. Создайте базу данных Выдача ключей. В таблице базы данных должна содержаться информация: номер записи, фамилия сотрудника, взявшего ключ, номер аудитории, время взятия ключа и время возвращения. Сформируйте запрос для определения:
• фамилий сотрудников, пользующихся ключами от некоторой аудитории в заданный день;
• списка номеров аудиторий, которые
были заняты в определенный период времени;
какая из аудиторий за весь период формирования использовалась чаще других;
какая из аудиторий использовалась в течение наибольшего периода времени.
После выполнения комплекта практических заданий учащийся должен научиться строить несложные однотабличные базы данных, создавать формы для ввода данных в таблицу, формировать отчеты для получения информации из базы данных, создавать простые отчеты.
Литература.
1. Информатика // под ред. проф. Макаровой Н. И.. М., 1997.
2. Джонс Э., Саттон Д. Библия пользователя Microsoft Office Professional для Windows 95. Киев, 1996.
3. Дейт К. Дж. Введение в системы баз данных. Киев, 1999.
4. Мартин Дж. Организация баз данных в вычислительных системах. М., 1990.
5. Мейер Д. Теория реляционных баз данных. М., 1987.
6. Дмитриева М.В. Дмитриев Ю.И. Основы информационных технологий: Access 7.0, СПбГУ, 1998.
Дмитриева Марина Валерьевна, доцент кафедры информатики СПб Государственного Университета.
Дмитриев Юрий Игоревич, преподаватель кафедрыь математического моделирования СПб Государственного Университета кино и телевидения.
НАШИ АВТОРЫ
