Урок "Базы данных МS Аccess и принципы построения запросов и отчетов" Цели урока: Формировать у учащихся знания об основных объектах базы данных MS Access; познакомить учащихся с различными видами и методами построения запросов и отчетов, формировать у учащихся умения строить запросы и отчеты по заданной базе данных; Развивать внимание, познавательную активность, сосредоточение, общую информационную культуру, самоконтроль и интерес к предмету; Воспитывать у учащихся навыков учебного труда и ответственное отношение к учебному труду. ТСО: Мультимедиа проектор, ЭВМ. Программное обеспечение: Операционная система Microsoft Windows XP Professional, Программа Microsoft Access, Программа Microsoft Power Point. Структура урока: Постановка темы и целей урока (1 минута), Фронтальный опрос (8 минут), Ознакомление с новым материалом (20 минут), Первичное осмысление и применение изученного (15 минут), Домашнее задание (1 минута). 1. Постановка темы и целей урока.Ребята, мы продолжаем изучать базы данных, на прошлых уроках вы познакомились с понятием базы данных, с понятием СУБД, с различными классификациями баз данных, с полями и записями, с типами данных и с операциями над информацией в базах данных. Сегодня на уроке мы познакомимся с основными объектами базы данных MS Access, с различными видами и методами построения запросов и отчетов в MS Access.^ 2. Фронтальный опрос.(Диалог учителя и учащихся)– Что такое база данных?– База данных – это совокупность хранящихся взаимосвязанных данных, организованных по определенным правилам. – Приведите примеры баз данных.– База данных крупного банка, справочники, словари, записная книжка, энциклопедии, телефонный справочник и др.– Что такое СУБД?– Системы управления базой данных – это комплекс программных средств, предназначенных для создания структуры новой базы, наполнение ее содержимым, редактирование содержимого и визуализации информации. – На какие два вида делятся базы данных по структуре организации данных? Представьте ответ в виде схемы.– На реляционные и нереляционные. – Какими базами данных являются нижеприведенные рисунки?– Иерархическая система папок операционной системы Windows (слева) и сетевая база данных Internet(справа).– С какими типами данных работает Microsoft Access?– Текстовый, числовой, дата/время, денежный, счётчик, логический, поле объекта OLE, гиперссылка, мастер подстановок. – К каким двум операциям сводится вся обработка информации в базе данных?– Сортировка – расположение данных в строго определенном порядке (сортировка по возрастанию (от А до Я) и сортировка по убыванию (от Я до А)). Фильтрация (выборка) – поиск записей по заданным условиям.^ 3. Ознакомление с новым материалом.(При объяснении нового материала учитель демонстрирует все примеры на экране с помощью мультимедиа проектора, учащиеся сидят за ЭВМ и выполняют все указания учителя).Окно базы данных – один из главных элементов интерфейса Ассеss. Оно включает в себя следующие составляющие (сверху вниз): титульную строку с кнопками управления окном, главное меню, панель инструментов, рабочее поле и строку состояния. На рабочем поле открывается окно, соответствующее выбранному режиму работы. В окне систематизированы все объекты БД: таблицы, запросы, формы, отчеты, макросы и модули. Дадим краткую характеристику всем объектам БД:Таблицы – это основной объект базы данных, в котором хранятся все данные, имеющиеся в базе, а также структура базы (поля, их типы, свойства).Запросы позволяют выбирать данные из одной или нескольких связанных таблиц. Результатом выполнения запроса является результирующая таблица, которая наряду с другими таблицами может быть использована при обработке данных. С помощью запросов можно также обновлять, удалять или добавлять данные в таблицы.Формы служат для ввода и просмотра данных в удобном для пользователя виде, который соответствует привычному для него документу. При помощи форм можно добавлять в таблицы новые данные, а также редактировать или удалять существующие. Форма может содержать рисунки, графики и другие внедренные объекты.Отчёты предназначены для формирования выходных документов и вывода их на печать. Основное их отличие от форм заключается в том, что в отчёте отображаются все данные и в них предусмотрена возможность группировать данные по различным критериям. Отчёты могут содержать специальные элементы оформления, характерные для печати документов: колонтитулы, номера страниц и т.д.Страницы – это специальные объекты баз данных, которые осуществляют интерфейс между клиентом, сервером и базой данных, размещённой на сервере.Макросы предназначены для автоматизации повторяющихся операций при работе с СУБД. Модули создаются пользователем путём применения интегрированной среды объектно-ориентированного программирования, основной идеей которой является объединение данных и оперирующих ими функций в один объект. После раскрытия на экране окна базы данных становятся доступными режимы работы с основными объектами MS Асcess: режим Конструктора (возможно изменение типа данных, структуры объекта) и режим Таблицы (возможно просматривать, вводить и редактировать данные).Запросы.1. Основные этапы создания запросов.С помощью запросов можно просматривать, анализировать и изменять данные из нескольких таблиц. Рассмотрим основные этапы создания запросов: 1) В окне ^ База данных выбрать объект Запрос;2) Выбрать Создание запроса в режиме конструктора или Создание запроса с помощью мастера; 3) В окне диалога Добавлении таблицы выделить нужную таблицу и нажать кнопку Добавить;4) Закрыть окно Добавление таблицы;5) В окне ^ Запрос на выборку перетащить нужное имя поля из списка полей таблицы в первую клетку строки Поле запроса (в нижней половине окна);6) Указать условия отбора; 7) При необходимости выполнить сортировку, щелкнув мышью в строке Сортировка под именем нужного поля;8) Нажать на панели инструментов кнопку Запуск;9) Сохранить запрос, присвоив ему некоторое имя;10) Открыть запрос для просмотра.2. Формирование запросов на выборку.Пользователям, имеющим малый опыт работы с Access, рекомендуется форматировать запросы с помощью мастера запросов. Для этого в окне базы данных необходимо выбрать объект Запросы и дважды щелкнуть на значке Создание запроса с помощью мастера. Эти действия вызовут открытие окна Запрос на выборку. Одновременно с этим окном открывается диалоговое окно Добавление таблицы, в котором в качестве источника данных для запроса выбирается таблица, например Телефонный справочник. Имя таблицы с перечнем содержащихся в ней полей появится в нижней части бланка запроса. Выбрав нужные поля, нужно щелкнуть на кнопке Добавить. Затем нажать кнопку Далее. Появится диалоговое окно, в котором можно выбрать одну из двух команд: Открыть запрос для просмотра данных или Изменить макет запроса. Нажать кнопку Готово. Второй способ создания запросов – в режиме конструктора. Для этого в окне базы данных необходимо выбрать объект Запросы и дважды щелкнуть на значке Создание запроса в режиме конструктора. Откроется окно Запрос на выборку и диалоговое окно Добавление таблицы, в котором в качестве источника данных для запроса выбирается таблица, например Телефонный справочник. В тех случаях, когда запрос формируется на основании не одной, а нескольких таблиц, необходимо последовательно выбирать таблицы из списка и щелкать на кнопке Добавить, а по окончании процесса выбора таблиц из списка щелкнуть на кнопке Закрыть. Следует помнить, что формировать запросы с использованием нескольких таблиц можно только при наличии связей между выбранными таблицами. Эти связи отображаются в верхней части бланка запроса на выборку. Нижняя часть бланка запроса разбита на столбцы, причем каждому столбцу соответствует определенное поле будущей результирующей таблицы. Для создания результирующей таблицы необходимо “перенести” имена полей таблицы (или таблиц) из верхней части бланка запроса в его нижнюю часть (в строку Поле), тем самым осуществив выбор полей, включаемых в результирующую таблицу. При необходимости упорядочивания данных в результирующей таблице по какому-либо полю необходимо щелкнуть на ячейке этого поля, находящейся в строке Сортировка. Критерии поиска задаются в строке Условие отбора. Рассмотрим таблицу Телефонный справочник и организуем несколько запросов:I. Запрос по фамилиям абонентов, отсортированных по возрастанию и проживающих в Москве, и по номеру абонентов. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Номер абонента, Адрес абонента. Под полем Фамилия абонента в строке Сортировка выбрать по возрастанию. Под полем Адрес абонента в строке Условие отбора написать слово “Москва”. Нажать кнопку Запуск. II. Запрос по фамилиям абонентов, заканчивающихся на “ов”; году рождения с 1980; адресу абонентов и категории абонентов. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Год рождения, Категория абонента, Адрес абонента. Под полем Фамилия абонента в строке Условие отбора написать “*ов”. Под полем Год рождения в строке Условие отбора написать “>=1980”. Нажать кнопку Запуск. (Предлагается ребятам выполнить самостоятельно)III. Запрос по фамилиям абонентов; адресу абонентов, отсортированному по убыванию, и категории абонентов “друзья” и “сервис”. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Категория абонента, Адрес абонента. Под полем Адрес абонента в строке Сортировка выбрать по убыванию. Под полем Категория абонента в строке Условие отбора написать “друзья and сервис”. Нажать кнопку Запуск. 3. Параметрические запросы.Запросы, как правило, служат для выполнения определенной операции. При изменении какого-либо критерия поиска приходится создавать новый запрос. Например, в одном запросе критерием поиска является значение друзья поля Категория абонента, а в другом запросе – значение сервис этого же поля. Однако эти критерии можно использовать в одном так называемом параметрическом запросе, в котором они задаются не при формировании запроса, а при его выполнении.Параметрический запрос формируется аналогично запросу на выборку. Отличие заключается лишь в том, что в строке ^ Условие отбора указывается не конкретное значение поля, по которому ведется отбор данных, а вводится в квадратных скобках сообщение пользователю о необходимости задания критерия поиска. Организуем несколько запросов:IV. Запрос по фамилиям, номеру абонентов и определенной категории абонентов, задаваемой пользователем. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Номер абонента, Категория абонента. Под полем Категория абонента в строке Условие отбора написать в квадратных скобках сообщение [ введите наименование категории] Нажать кнопку Запуск. При запуске запроса появится диалоговое окно, в котором будет запрос на ввод категории абонентов. Ввести любую из трёх категорий и нажать OK. Можно вывести всех друзей или родственников или сервис. (Предлагается ребятам выполнить самостоятельно)V. Запрос по фамилиям абонентов; году рождения, определенному пользователем; номеру, категории и адресу абонентов. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Номер абонента, Категория абонента, Год рождения, Адрес абонента. Под полем Год рождения в строке Условие отбора написать в квадратных скобках сообщение [ введите год рождения] . Нажать кнопку Запуск. При запуске запроса появится диалоговое окно, в котором будет запрос на ввод категории абонентов. Ввести любой год рождения, например 1985, и нажать OK. (Предлагается ребятам выполнить самостоятельно)VI. Запрос по фамилиям абонентов; категории и адресу абонентов, определенных пользователем. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Категория абонента, Адрес абонента. Под полем Категория абонента в строке Условие отбора написать в квадратных скобках сообщение [ введите наименование категории] . Под полем Адрес абонента в строке Условие отбора написать в квадратных скобках сообщение [ введите город] . Нажать кнопку Запуск. При запуске запроса появится сначала диалоговое окно, в котором будет запрос на ввод категории абонента, а затем диалоговое окно, в котором будет запрос на ввод адреса абонента. Нажать кнопку OK. Например, найдем друзей, проживающих в Москве.4. Запросы на обновление.Помимо запросов на выборку, с помощью которых осуществляется только отбор необходимых данных, MS Access предоставляет возможность модифицировать данные с помощью запросов на обновление. При выполнении такого запроса результирующая таблица не формируется, поскольку его задачей является изменение данных в таблице, выбранной в качестве источника данных во время формирования запроса. Поэтому бланк запроса может содержать только поля, значения которых требуется обновить, и поля, по значениям которых отбираются записи для изменения в них данных. С помощью запроса на обновление можно изменять данные не всех записей, а выборочно. Для этого в бланк запроса включаются поля, по значениям которых будет вестись отбор записей. Рассмотрим новую таблицу Телефонный справочник , где указана стоимость минуты разговора с конкретным абонентом с НДС. Используя запрос на обновление, посчитаем сколько будет стоить минута разговора с конкретным абонентом без учета НДС. После добавления таблицы Телефонный справочник, добавляем поля: Фамилия абонента, Категория абонента, Адрес абонента, Стоимость минуты разговора. На панели инструментов выбрать пиктограмму “Тип запроса” и выбрать “Обновление”. В нижней части бланка строка Сортировка заменяется строкой Обновление. В эту строку в ячейку поля, значения которого необходимо изменить, вводится выражение. В ячейку поля ^ Стоимость минуты ввести выражение [ стоимость] – 0,13*[ стоимость] , из которого видно, что стоимость минуты разговора без учета НДС равна стоимости минуты минус 13% от этой стоимости минуты. Наименования полей, значения которых будут использованы при вычислении введенного выражения, должны быть заключены в квадратные скобки. Нажать кнопку Запуск. Для просмотра результатов расчета после выполнения запроса необходимо в окне базы данных щелкнуть на значке объекта Таблицы, выделить имя таблицы (в данном случае таблицы Телефонный справочник и открыть ее щелчком на кнопке Открыть. 5. Перекрестные запросы.Перекрестный запрос используют для анализа данных. Он позволяет собирать данные из одной или нескольких таблиц в формате, похожем на формат электронной таблицы. Например, используя таблицу Товары, организовать перекрестный запрос, позволяющий найти товар, о котором известна цена и количество. Открыть БД Товары, В появившемся диалоговом окне выбрать закладку Запросы, На панели инструментов выбрать Создать и указать тип запроса Перекрестный запрос, нажать ОК, Выбрать таблицу Товары, поля которой необходимо вывести в перекрестном запросе, нажать Далее, Выбрать поля Код и Средняя цена (руб), нажать Далее, Выбрать поля для использования их значений в качестве заголовков столбцов – Количество товара (ед), нажать Далее, Выбрать поле, значения которого должны быть получены на пересечении столбцов и строк – Название товара, нажать Далее, Задать имя запроса, нажать Готово. Просмотреть получившийся запрос. 6. Итоговые запросы.Итоговые запросы позволяют выполнять вычисления (сумму, произведение, среднее значение и др.) по всем записям для какого-либо числового поля. Итоговые запросы формируются так же, как и ранее рассмотренные запросы. Только в итоговый запрос еще включают поля, для которых рассчитываются итоговые значения, а также поля, по которым производится группировка записей. Например, используя таблицу Товары, организовать итоговый запрос, позволяющий вычислять доход предприятия от продажи каждого вида продукции.1) После добавления таблицы Товары, добавляем поля: Код, Название товара, Количество товара(ед), Средняя цена (руб),2) Добавляем новое поле ^ Доход, в котором записываем формулу: Доход : [Количество товара(ед)] * [Средняя цена (руб)],3) Нажать кнопку Запуск.Отчеты.Основное назначение отчётов – это вывод данных в виде выходного печатного документа. Отчёты, так же как и формы, можно создавать с помощью конструктора или мастера отчетов. Этапы создания отчета с помощью мастера отчетов: Выбрать в окне БД объект Отчеты, Выбрать пункт Создание отчета с помощью мастера, Указать таблицу или запрос для создания отчета, Выбрать поля для отчета, нажать Далее, Можно указать, если запрашивается, порядок сортировки для каждого поля, нажать Далее, Указать макет отчета и ориентацию; нажать Далее, Указать стиль макета, нажать Далее, Задать имя отчета и установить флажок напротив пункта Просмотреть отчет или Изменить макет отчета; нажать Готово. Этапы создания отчета в режиме конструктора: Выбрать в окне БД объект Отчеты, Выбрать пункт Создание отчета в режиме конструктора, Появится диалоговое окно с макетом отчета, состоящее из трех частей: верхний колонтитул, нижний колонтитул, область данных; и панель элементов, В верхний и нижний колонтитулы можно вставить любой объект с панели элементов. Обычно их используют для печати служебной информации, например номеров страниц. Для вставки в область верхнего или нижнего колонтитула номера страницы выполняется команда Вставка ® Номера страниц. Этот элемент можно перемещать из области колонтитула в другие области, например в область заголовка отчёта, если указатель мыши принимает форму ладони, Необходимо связать отчет с таблицей или запросом, из которых будут использованы данные для отчета. Для этого нужно щелкнуть правой клавишей мыши на пересечении двух линеек в области отчета, выбрать пункт Свойства и в закладке Все установить в строке Источник записей ту таблицу или запрос, из которых будут использованы данные для отчета, В области данных расположить элементы – ^ Поле панели инструментов, Подписать эти поля. Для этого нужно щелкнуть правой клавишей мыши в каждом поле, выбрать пункт Свойства и в закладке Данные установить в строке Данные имя поля из таблицы или запроса, Сохранить отчет, при необходимости распечатать. ^ 4. Первичное осмысление и применение изученного.Ребятам предлагается самостоятельно выполнить следующие задания: Создать запрос на выборку по фамилиям абонентов, заканчивающихся на “а” и отсортированных по возрастанию, по году рождения, по номеру телефона и категории “родственники”. Создать параметрический запрос по фамилиям абонентов, году рождения, категории, адресу абонентов и номеру, определенному пользователем. Используя запрос на обновление, посчитать сколько будет стоить минута разговора с конкретным абонентом без учета НДС и некоторого налога 4%. Организовать перекрестный запрос, позволяющий найти товар, о котором известна цена и объём продаж. Зная доход от продажи каждого вида продукции и стоимость единицы продукции, создать итоговый запрос, позволяющий вычислить количество продукции каждого вида на предприятии. Создать отчет о продажах различных видов продукции предприятием. 5. Домашнее задание.Подготовиться к письменной и практической работе по теме: “Базы данных MS Access и принципы построения запросов и отчетов”.