Работа с данными из связанных таблиц.

Цель работы: изучить программный продукт Access.

Оборудование: Компьютерный класс кафедры АТ, программное обеспечение Microsoft Access-2007, TeahPro Access-2000.

Запросы являются основным инструментом выборки, обновления и обработки данных в таблицах базы данных.

Access в соответствии с концепцией реляционных баз данных для выполнения запросов использует язык структурированных запросов SQL (StructuredQueryLanguage). С помощью инструкций языка SQL реализуется любой запрос в Access. В то же время Access позволяет создавать запросы, не прибегая к записи инструкций языка SQL. Простейшие запросы могут быть созданы с помощью мастера, практически любой запрос можно создать в режиме графического конструктора. Мы будем пользоваться мастером запросов в режиме графического конструктора.

Рисунок 23 – Конструктор запросов

 

Основные виды запросов:

1. на выборку

2. на изменение

Запросы на выборку

Основным видом запроса является запрос на выборку. Результатом выполнения этого запроса является новая таблица, которая существует до закрытия запроса. Структура такой таблицы определяется выбранными из одной или нескольких взаимосвязанных таблиц полями. Записи формируются путем объединения записей таблиц, на которых построен запрос. Способ объединения записей таблиц указывается при определении их связи в схеме данных или при создании запроса. Условия отбора, сформулированные в запросе, позволяют фильтровать записи, составляющие результат объединения таблиц.

В Access может быть создано несколько видов запроса:

·  запрос на выборку — выбирает данные из одной таблицы или запроса или нескольких взаимосвязанных таблиц и других запросов. Результатом является таблица, которая существует до закрытия запроса. Формирование записей таблицы результата производится в соответствии с заданными условиями отбора и при использовании нескольких таблиц путем объединения их записей;

· Запрос на групповые операции – выбирает из таблиц данные и производит с этими данными операции (например, подсчет количества ненулевых значений, сумму, скидку и др.)

· Параметрический запрос – выбирает данные из таблиц по нужным нам параметрам

· Перекрестный запрос – делает выборку в форме таблицы, где есть строка, столбец и поле значения (Например, подсчитать какой из мастеров какую работу выполнял и сколько раз)

·  запрос на создание таблицы — выбирает данные из взаимосвязанных таблиц и других запросов, но, в отличие от запроса на выборку, результат сохраняет в новой постоянной таблице;

Запросы на изменение

·  запросы на обновление, добавление, удаление — являются запросами действия, в результате выполнения которых изменяются данные в таблицах.

Для нашей базы данных составим следующие запросы:

1. Запрос на выборку

2. Запрос на групповые операции

3. Параметрический запрос

4. Перекрестный запрос

5. Запрос на создание таблицы

6. Запросы на обновление,

7. Запросы на добавление,

8. Запросы на удаление

 

Задание 1. Вывести таблицу с данными: Фамилия клиента, Фамилия мастера, Наименование работы, Удовлетворенность работой. Условие отбора – не удовлетворенность клиента.

Нажимаем в ленте вкладку создать → конструктор запросов

Появится окно Добавление таблицы. Щелкаем на нужные нам таблицы (в данном примере на все таблицы) двойным щелчкам.

Рисунок 24 – Добавление таблиц

 

Отобразится схема данных, используемых таблиц, и бланк запроса, в котором конструируется структура таблицы запроса и условия выборки записей (рис). Двойным щелчком выбираем нужные нам поля из таблиц. В строке Условие отбора в столбце Удовлетворенность клиента ставим условие НЕТ.

Рисунок 25 – Запрос на выборку

 

 

На ленте нажимаем кнопку Выполнить. Обратите внимание, если убрать галочку со строки Вывод на экран, то этот столбец отображаться не будет. Запрос готов. Сохраняем его при помощи сочетания клавиш CTRL+S «на выборку»

Задание 2. Запрос на групповые операции. Создать запрос, в котором подсчитывается количество ремонтов, которые выполняли мастера.

Нажимаем в ленте вкладку создать → конструктор запросов → выбираем Мастера, Наименование работы, Приемка → два раза щелкаем по полям Фамилия мастера и Код работы. Во вкладке Конструктор запросов выбираем функцию групповые операции (на ленте знак ∑-итоги) → в столбце Код работы выбираем групповую операцию count – эта операция подсчитывает количество ненулевых значений → для более красивого наименования столбца код книги, перед наименованием поля написать «Количество ремонтов:». Запрос готов. Сохраняем его под именем «запрос на групповые операции».

Задание 3. Параметрический запрос. Отобразить фамилию клиентов, его телефон, которые обслуживались до определенной даты.

Конструктор запросов →таблицы клиент, Приемка → фамилия клиента, телефон, дата ремонта → в поле Условие отбора в квадратных скобках пишем:>[введите дату] → запускаем → сохраняем. Готово.

Задание 4. Перекрестный запрос. Отобразить количество выполненных работ мастером по дням.

Конструктор запросов →таблицы Мастер, Наименование работ, Приемка → фамилия мастера, наименование работы, код приемки → на ленте выбираем пункт Перекрестный → Одно из значений обозначаем «заголовок строк», другое «заголовок столбцов», а третье – «поле значение». Выбираем в коде приемки функцию Count → запускаем → сохраняем.

Задание 5. Запрос на создание таблицы. Создать запрос Заработок Мастера 1. Зарплату будем считать по формуле сумма Стоимости услуг -15%

Выбираем таблицы Мастер, Приемка, Наименование работы → добавляем поля фамилия мастера, стоимость работы → Условие отбора Мастер 1 → Создание таблицы. В таблицы добавляем поле ИТОГО.

Задание 6. Запрос на изменение. Удалить клиента из таблицы по его коду.

Таблица Клиента → поля код клиента, Фамилия, Имя отчество клиента → на ленте нажимаем кнопку тип запроса – на удаление → условие отбора [введите код клиента].

Задание 7. Запрос на обновление. Сделать скидку 7 % на услуги клиента Иванова.

Таблица Клиента, Наименование работ, Приемка → Фамилия, Имя отчество клиента,, стоимость услуги → на ленте нажимаем кнопку тип запроса – на обновление → обновление [стоимость]-[стоимость]*0,07.

Задание 8. Запрос на добавление. Добавить нового клиентав базу данных.

Конструктор запросов → не выбираем ни какую таблицу → нажимаем кнопку Добавление→ в окне указываем в какую таблицу будем добавлять данные (клиенты) → где добавление выбираем нужные пункты (все кроме код клиента – он ставится автоматически) → в пункте Поле [введите фамилию клиента] и т.д для каждого поля.

 

Лабораторная работа № 6

Создание форм.

Цель работы: изучить программный продукт Access.

Оборудование: Компьютерный класс кафедры АТ, программное обеспечение Microsoft Access-2007, TeahPro Access-2000.

Access предоставляет возможность вводить данные как непосредственно в таблицу, так и с помощью форм. Форма в БД - это структурированное окно, которое можно представить так, чтобы оно повторяло форму бланка. Формы создаются из набора отдельных элементов управления.

Внешний вид формы выбирается в зависимости от того, с какой целью она создается. Формы Access позволяют выполнять задания, которые нельзя выполнить в режиме таблицы. Формы позволяют вычислять значения и выводить на экран результат. Источником данных для формы являются записи таблицы или запроса.

Форма предоставляет возможности для:

· ввода и просмотра информации базы данных;

· изменения данных;

· печати;

· создания сообщений.

Способы создания форм:

1. Конструктор форм (предназначен для создания формы любой сложности).

2. Мастер форм (позволяет создавать формы различные как по стилю, так и по содержанию).

3. Автоформа: в столбец (многостраничная – поля для записи выводятся в один столбец, в форме одновременно отображаются данные для одной записи).

4. Автоформа: ленточная (все поля записи выводятся в одну строку, в форме отображаются все записи).

5. Автоформа: табличная (отображение записей осуществляется в режиме таблица).

6. Автоформа: сводная таблица.

7. Автоформа: сводная диаграмма.

8. Диаграмма (создается форма с диаграммой, построенной Microsoft Graph).

9. Сводная таблица (создается форма Access, отображаемая в режиме сводной таблицы Excel).

Алгоритм создания форм следующий:

1. Открыть окно БД.

2. В окне БД выбрать вкладку Формы.

3. Щелкнуть на пиктограмме Создать, расположенной на панели инструментов окна БД.

4. В появившемся диалоговом окне «Новая форма» Выбрать способ создания формы и источник данных.

5. Щелкнуть на кнопке ОК.

 

Рисунок 26 – Создание новой формы