Добавлен: 06.11.2018
Просмотров: 1707
Скачиваний: 23
Чтобы установить связь между таблицами, надо выделить поле в одной таблице и перетащить его на соответствующее поле в другой таблице.
После перетаскивания откроется диалоговое окно Изменение связей, в котором надо включить флажок Обеспечение целостности данных. Это позволит предотвратить случаи удаления записей из одной таблицы, при которых связанные с ними данные других таблиц останутся без связи.
Поскольку мы предусмотрели поля подстановок для таблицы Заказы, то Access автоматически создал связи между таблицами. В нашем случае схема данных выглядит как на рис. 4.3.
Рис. 4.3. Схема данных
Для изменения связи между таблицами её надо выделить, вызвать контекстное меню щелчком правой клавиши мыши, выбрать команду Изменить связь.
√ Измените связи между таблицами: включите опцию Каскадное обновление связанных полей.
После того, как заданы описания структур таблиц, установлены связи между таблицами, можно приступить к их заполнению. Для этого надо выполнить двойной щелчок на имени таблицы в левой части окна и ввести значения в поля таблицы.
√ Введите по пять записей в таблицы Сотрудники и Клиенты и десять записей в таблицу Заказы. При вводе данных в таблицу Заказы используйте списки, созданные для подстановки.
Навигация по полям таблицы осуществляется клавишами , , , , Tab, Shift/Tab, мышью. Переход к заполнению следующей записи автоматически сохраняет в памяти компьютера предыдущую запись.
В
процессе работы с таблицей можно в любой
момент вернуться в режим конструктора
для изменения её структуры путем нажатия
мышью пиктограммы
,
которая отображается в панели инструментов
в табличном режиме.
Изменению
могут быть подвергнуты названия полей,
их тип и последовательность. Для удаления
некоторого поля таблицы необходимо его
выделить щелчком правой клавиши мыши
в первом столбе и нажать мышью пиктограмму
(Удалить
строки)
в панели инструментов.
Пустая строка в описании структуры создается следующим образом:
-
курсор устанавливается на строку, перед которой необходимо вставить пустую;
-
на панели инструментов нажимается кнопка
(Вставить
строки).
Перенос строки описания структуры реализуется следующим образом:
-
выделяется мышью в первом столбце переносимая строка (строки);
-
выделенная строка в первом столбце захватывается мышью и перетаскивается на новую позицию.
√ Сохраните базу данных на своём носителе данных, поскольку она будет использоваться в последующих работах.
Контрольные вопросы
-
Что называется базой данных (БД)?
-
Что такое система управления базами данных (СУБД)?
-
Чем отличается Microsoft Excel от Microsoft Access?
-
Какие объекты базы данных Microsoft Access вы знаете?
-
Какой объект в базе данных является основным?
-
Что называется полями и записями в БД?
-
Какие типы данных вы знаете?
-
Как можно создать поле с раскрывающимся списком?
-
С каким расширением сохраняется файл БД Access?
-
С помощью каких средств можно создавать таблицы в Access?
-
Что такое ключевое поле?
-
Как установить несколько ключевых полей?
-
Как установить связи между таблицами?
-
Какие типы связей между таблицами вы знаете?
Отбор данных с помощью запросов. Создание отчётов
Цель работы: освоить на практике правила формирования запросов к базе данных, технологии создания запросов и отчётов.
Задание
1. Выполнить задания, помеченные в методических указаниях по выполнению работы символом √
Методические указания по выполнению работы
Вопросы, которые формируются средствами СУБД к одной или нескольким таблицам, называются Запросами.
Запросы являются основным средством просмотра, отбора, изменения и анализа информации, которая содержится в одной или нескольких таблицах базы данных.
Существует несколько типов запросов:
Запрос на выборку – позволяет отобрать информацию по заданным критериям отбора.
Запрос-создание новой таблицы. Предназначен для сохранения результатов запроса в виде новой таблицы.
Запрос-добавление записей в другую таблицу. Выборку можно добавить к другой таблице, однотипной по структуре или с изменением структуры выборки.
Запрос-удаление. С помощью запросов можно удалить часть или все записи из таблицы.
Запрос-обновление. С помощью запросов можно обновлять значения полей таблицы.
Для создания запросов можно использовать Мастер запросов или Конструктор.
Самым распространённым типом запросов является Запрос на выборку.
√ С помощью мастера запросов создайте запрос на выборку данных из таблицы Сотрудники с именем Телефоны сотрудников.
-
Откройте свою базу данных, созданную в предыдущей работе. Вызовите команду: Создание – Запросы - Мастер запросов - Простой запрос.
-
В появившемся окне (рис. 5.1) укажите таблицу Сотрудники и выберите требуемые поля. Щёлкните на кнопке Далее.
Рис. 5.1. Создание простого запроса
-
В следующем окне задайте имя запроса Телефоны сотрудников и щёлкните на кнопке Готово.
-
Появятся результаты выборки по запросу (рис. 5.2).
Рис. 5.2. Результаты выборки по запросу Телефоны сотрудников
√ С помощью Конструктора создать запрос с именем Адреса клиентов.
-
Для этого вызовите команду: Создание – Запросы - Конструктор запросов (рис. 5.3)
-
В диалоговом окне Добавление таблиц выберите таблицу Клиенты и щелкните на кнопке Добавить, а затем – на кнопке Закрыть.
-
Чтобы перенести нужные поля в бланк запроса, необходимо по ним дважды щелкнуть левой кнопкой мыши (рис. 5.3). Выбираем поля Название компании, Адрес клиента, Телефон клиента.
-
Чтобы отсортировать записи в поле Название компании в алфавитном порядке, необходимо в раскрывающемся списке строки Сортировка выбрать пункт по возрастанию.
-
Закройте окно конструктора запросов. При попытке закрыть окно конструктора запросов появится окно, в котором надо ввести имя запроса - Адреса клиентов.
Рис. 5.3. Создание запроса в режиме Конструктора
√ Самостоятельно создайте запрос «Дни рождения», в котором можно будет просмотреть дни рождения сотрудников.
Самое главное в запросе - критерии выборки, которые вводятся в строку Условие отбора. Можно выделить следующие типы запросов на основе критериев:
Выборка по строгому совпадению. В строку Условие отбора для определенного поля вводится одно из значений, существующих в таблице. Например, название конкретного товара, должности или название фирмы.
√ Создайте запрос Заказы менеджеров. Для этого вызовите Мастер запросов-Простой запрос, из таблицы Заказы выберите поля: Заказчик, Сотрудник, Дата заказа, Дата исполнения, Сумма, Отметка о выполнении, из таблицы Сотрудники выберите поле Должность, на следующих шагах выберите Подробный и дайте имя запросу - Заказы менеджеров и щёлкните кнопку Готово. Появятся результаты выполнения запроса. Далее надо перейти в окно конструктора запросов. Для этого выберите режим Конструктора на вкладке Главная или в контекстном меню,которое появляется, если щёлкнуть правой клавишей на ярлычке запроса (сверху). В строке Условие отбора для поля Должность наберите менеджер (рис. 5.4).
Рис. 5.4. Запрос на выборку по строгому совпадению
√ Самостоятельно создайте запрос Выполненные заказы, содержащий следующие сведения: ФИО сотрудника, Заказчик, с которым он работает, отметка о выполнении и сумма. Данные запроса возьмите из нескольких таблиц. Для условия отбора по полю Отметка о выполнении введите Да.
Параметризированные запросы. Это частный случай запросов по строгому совпадению. Для параметризации необходимо в строке Условие отбора вместо самого условия ввести текст приглашения на его ввод в квадратных скобках, например [Введите условие отбора].
При запуске параметризованного запроса появляется диалоговое окно (рис. 5.5), в котором пользователь должен ввести собственно условие отбора.
Рис. 5.5. Окно выполнения параметризированного запроса
Выборка по строгому несовпадению. В этом случае в выборку отбираются все записи таблицы, кроме записей, содержащих значение, указанное в строке Условие отбора. Для реализации данного запроса перед значением вводится префикс Not.
Выборка по неточному совпадению. Для выборки записей в условиях неполноты знаний о требуемых значениях используется оператор Like <условие>. Само <условие> образуется следующими подстановочными символами:
? - любой один символ;
* - любое количество символов (0 - );
# - любая одна цифра;
[список_символов] - любой символ из списка;
[!список_символов] - любой символ, не входящий в список;
В списке можно указывать сразу диапазон символов, например, [Г-Л] или [г-лГ-Л].
√ Измените запрос Телефоны сотрудников, предусмотрев для пользователя возможность ввода интересующей его фамилии без инициалов или только первых букв фамилии. Очевидно, что надо создать запрос с параметрами для поля ФИО, по неточному совпадению. Откройте запрос Телефоны сотрудников в Конструкторе. В строке Условие отбора для поля ФИО наберите Like [Введите фамилию] (рис. 5.6).
Рис. 5.6. Создание запроса
√ Для выполнения запроса вызовите команду Выполнить, находящуюся на линейке команд Конструктор. При выполнении команды появится окно (рис. 5.7). В нем введем: Иванов*. В результате выполнения запроса будут выведены телефоны всех сотрудников организации с фамилией Иванов.
Рис. 5.7. Выполнение запроса
Выборка по диапазону. Для формирования данных условий выбора используются операторы сравнения >, >=, <, <= и <>. Операции сравнения могут связываться логическими операциями And (И) и Or (ИЛИ).
Для этих же целей используется оператор диапазона Between <нижнее_значение> and <верхнее_значение>. Например, выбор книг стоимостью от 100 до 200 руб. может быть реализован через ввод в запросе условия в поле Стоимость в виде >=100 and <=200 или Between 100 and 200.
Перечень значений в условии выборки можно задать и оператором In (значение, значение, ...).
Для выбора записей с пустыми значениями в некотором поле надо в соответствующем поле бланка запроса указать оператор Is Null. Наоборот, записи с непустыми значениями в данном поле выбираются по оператору Is not Null.
Запрос с вычислениями. Такой запрос позволяет получить дополнительную информацию в процессе выборки, например, стоимость всей партии товара при хранимой в таблице информации о количестве товара и стоимости единицы его продукции. Для этого в строку Поле пустого столбца заносят выражение для вычисления по следующему формату:
Название_формируемого_поля :выражение.
В выражении можно использовать знаки арифметических операций, круглые скобки и имена полей в квадратных скобках. Например, стоимость партии можно вычислить по выражению:
Стоимость партии:[количество товара]*[стоимость единицы товара]
ВНИМАНИЕ: имена полей в выражении должны точно совпадать с наименованиями полей в таблицах!
Если используется поле другой таблицы, то в префиксе через ! указывается имя данной таблицы. Например:
Стоимость партии:[Товар]![количество товара]*[стоимость единицы товара].
Для задания вычислений в запросах можно использовать Построитель выражений. Для этого надо установить курсор в пустом столбце бланка запроса на ячейке Поле и вызвать команду Построитель.
√ Подготовьте запрос Выплата зарплаты, в котором предусмотрите поля ФИО, Заработная_плата. Далее в Конструкторе добавьте поля для вычисления налога и суммы к выдаче (рис. 5.8):
Налог:[Заработная_плата]*0,13
К выдаче:[Заработная_плата]-[Налог
Рис. 5.8. Создание запроса с вычислениями
Запрос
с групповыми операциями.
Эти
запросы анализируют отдельные записи
таблицы. При этом СУБД Access позволяет
находить интегральные показатели для
групп записей в таблице. Каждая такая
группа характеризуется одинаковым
значением по какому-то полю, например,
одинаковой должностью или отметкой о
выполнении заказа. Для перехода в данный
режим запросов необходимо на панели
инструментов нажать клавишу Итоги
,
что приведет к появлению в бланке запроса
новой второй строки с названием Групповая
операция.
В ячейках данной строки указывается
или режим группировки по некоторому
полю (опция Группировка),
или название групповой операции:
Sum - сумма значений;
Avg - среднее значение по данному полю для всей группы;
Count - число записей в данной группе;
Max - максимальное значение поля в каждой группе;
Min - манимальное значение поля в каждой группе;
First - первое значение данного поля в каждой группе;
Last - последнее значение данного поля в каждой группе и др.
Опции выбора вызываются нажатием кнопки раскрытия в требуемой ячейке.
При запуске такого запроса СУБД разбивает таблицу на группы, число которых равно числу существующих значений в группируемом поле (для него указана опция Группировка), и реализует для каждой группы заданную операцию (например, считает средний размер партии товара), т.е. число строк в выборке равно числу групп.
√ Подготовьте запрос Заказы клиентов на основе таблицы Заказы. В нём предусмотрите поля Заказчики и Сумма. Щёлкнув на кнопке Итоги, предусмотрите группировку по заказчику (рис. 5.9):
Рис. 5.9. Запрос с групповыми операциями
Результат выполнения запроса представлен на рис. 5.10.
Рис. 5.10. Результат выполнения запроса с групповыми операциями
Создание отчётов
Отчеты предназначены для вывода информации на печать. Часто данные в них располагаются в табличной форме. В отличие от распечаток таблиц или запросов отчет дает более широкие возможности сортировки и группировки данных, он предоставляет возможность добавлять итоговые значения, а также поясняющие надписи, колонтитулы, номера страниц, стили и различные графические элементы.
Создавать отчеты в базе данных Access можно с помощью Мастера отчетов или в режиме Конструктора отчётов.
Отчёты можно создавать на основе таблиц или на основе предварительно подготовленных запросов.
Наиболее универсальным средством создания отчетов является конструктор, в окне которого размещается Бланк отчёта.
Бланк отчета имеет структуру:
Заголовок отчёта
Верхний колонтитул
Область данных
Нижний колонтитул
Примечание отчёта
Содержимое заголовка и примечания отчета выводится (печатается) один раз, поэтому в них целесообразно включать разовую информацию: название отчета, название фирмы, её логотип, дату формирования отчета, итоговые показатели по всему отчету и другую служебную информацию.
В верхнем и нижнем колонтитулах указывается информация, отображаемая на каждой странице печатаемого отчета: номера страниц. Если отчёт имеет табличную структуру, то в верхнем колонтитуле размещают шапку таблицы.
Содержание области данных в отчете отображается для каждой записи источника информации (таблицы или запроса).
√ Создайте отчёт Ведомость на выдачу зарплаты. Для этого вызовите команду Создание-Мастер отчётов. В появившемся окне в качестве источника данных для отчёта задайте запрос Выплата зарплаты, выберите в нём все поля и щёлкните кнопку Далее. Уровни группировки в отчёте не предусмотрены, поэтому в следующем окне сразу щёлкните кнопку Далее. На следующем шаге надо задать сортировку строк отчёта – по ФИО, в следующем окне выбрать макет отчёта: табличный, книжная ориентация. На заключительном шаге задайте имя отчёта – Ведомость начисления заработной платы и щёлкните кнопку Готово. В результате получаем отчёт, представленный на рис. 5.11.