Создание запроса с вычисляемым полем

 

Запрос Стипендия из таблицы Учеба с вычисляемым полем Стипендия предназначен для вычисления стипендии по полученным оценкам. Стипендия вычисляется по выражению [Средняя оценка по предметам]*450. Средняя оценка по предметам в запросе формируется с использованием функции Avg по полю Оценка. Порядок создания запроса следующий.

1. Вызвать Конструктор запросов добавить таблицу Учеба.

2. На вкладке Работа с запросами нажать на кнопку групповых операций для появления в окне запроса строки Групповая операция.

3. В окно запроса установить имена полей Шифр, Фамилия и Оценка. В строке Групповая операция для полей Шифр и Фамилия оставить значение Группировка, а в поле Оценка в строке Группировка нажать стрелку и выбрать функцию Avg (рисунок 50)

Рисунок 50- Создание запроса с вычисляемым полем

 

4. Создать следующее вычисляемое поле Стипендия, для этого в строке Группировка выбрать пункт Выражение, а в заголовок ввести выражение: для расчета стипендии: Стипендия: [Avg-Оценка]*450.

Рисунок 51- Создание выражения для запроса с вычисляемым полем

 

5. [Avg-Оценка] указывает, что берется групповое значение поля Оценка, т.е. среднее значение по всем предметам очередного студента.

6. Установить форматы данных для полей Оценка и Стипендия. Средняя оценка будет иметь целую и дробную части. Для дробной части целесообразно задать две значащие цифры. Для установки такого формата необходимо вызвать контекстное меню поля Оценка, щелкнув правой клавишей мыши по заголовку поля и выбрать в пеню пункт . В появившемся окне свойств установить в строке Формат поля значение Фиксированный, а в строке Число десятичных знаков значение 2 (рисунок 52). Задание денежного формата поля стипендия выполняется аналогично, а в окне свойств в строке Формат поля установить Денежный (рисунок 53).

а б

Рисунок 52- Задание формата поля Оценка

а б

Рисунок 53- Задание формата поля Стипендия

7. Сохранить создаваемый запрос с именем Стипендия.

8. Проверить работу запроса. Окно запроса после установки всех параметров должно иметь следующий вид и результат запроса (рисунок 54):

Рисунок 54- Результат работы запроса Стипендия

Создание отчетов

 

Одноуровневый отчет Стипендия выводит информацию о начислении стипендии студентам. Отчет создается с помощью Мастера отчетов по таблице Студенты и запросу Стипендия и содержит на одном уровне поля Шифр, Фамилия, Имя, Отчество и Стипендия. Для создания отчета необходимо:

1. Для создания отчета с помощью Мастера отчетов необходимо на вкладке Создание в группе инструментов Отчеты выбрать инструмент .

2. Будет открыто диалоговое окно Создание отчетов (рисунок 53), в котором необходимо последовательно выбрать поля таблиц и запросов, по которым будет формироваться отчет.

Рисунок 55- Окно создания отчетов

3. В таблице Студенты выбираются поля Шифр, Фамилия, Имя, Отчество, (рисунок 54а) а из запроса Стипендия выбирается поле Стипендия (рисунок 54б), затем необходимо нажать кнопку .

 

 

а б

Рисунок 56- Выбор полей из таблицы и запроса

4. На следующем шаге мастера Создание отчетов он предложит формат отображения полей в отчете (рисунок 57а). Установите в левом окне Стипендия и нажмите .

 

а б

Рисунок 57- Выбор полей из таблицы и запроса

5. На следующих шагах работы Мастера создания отчетов необходимо задать уровни группировки, вид макета и имя отчета (рисунок 58).

 

а б

Рисунок 58- Задание параметров отчета Успеваемость

После нажатия кнопки сформируется отчет, вид которого представлен на рисунке 59.

Рисунок 59- Отчет Стипендия

 

Многоуровневый отчет Успеваемость выводит среднюю оценку студентов. В отчете необходимо сделать два уровня группировки – первый уровень по факультетам, а второй – по специальностям. Для формирования отчета с помощью Мастера отчетов в таблице Факультеты необходимо выбрать поле Им_фак, в таблице Группы выбрать

поле Им_группы, в таблице Студенты поля Шифр, Фамилия, Имя, Отчество, а в запросе Стипендия – вычисляемое поле Avg-Оценка. Порядок создания отчета следующий:

1. Вызвать с помощью инструмента Мастера отчетов и в окне Создание отчетов выбрать для отчета указанные поля из таблиц и запросов (рисунок 60а) и нажать .

а б

Рисунок 60- Выбор полей из таблиц и запроса

На следующем шаге поместить все поля в одну группу (рисунок 60б) и нажать .

2. Для задания уровней группировки необходимо последовательно выбрать поля Им_фак и Им_группы и нажимая на кнопку создать два уровня группировки (рисунок 61а) и нажать .

3. На следующих шагах задать сортировку записей и выбрать вид макета для отчета (рисунок 61б), задать имя отчета и нажать кнопку .

а б

Рисунок 61- Задание уровней группировки и вида запроса Успеваемость

 

Мастер отчетов сформирует отчет в соответствии с именами и длиной полей, заданных при создании таблиц и запросов (рисунок 62). При этом заголовки некоторых столбцов не соответствуют их назначению, а некоторые данные, например часть отчества студентов не помещаются в выделенные Мастером позиции. Для приведения отчета в удобный для использования вид, его необходимо под редактировать с помощью Конструктора отчетов (рисунок 63).

Рисунок 62- Отчет Успеваемость, сформированный мастером создания отчетов

Рисунок 63- Редактирование отчета с помощью Конструктора

 

В верхнем колонтитуле, формирующем заголовки столбцов, в окне Имя_фак необходимо набрать имя столбца Факультеты, в окне Имя_группы набрать Группы, а в окне Avg-Оценка – Средняя оценка. Кроме того необходимо сдвинуть и изменить размеры окон в соответствии с помещаемыми в столбцы данными (рисунок 64).

Рисунок 64- Установка параметров отчета а помощью Конструктора

 

Вид сформированного отчета Успеваемость представлен на рисунке 65.

Рисунок 65- Многоуровневый отчет Успеваемость

2.2 Варианты к лабораторным заданиям

Общее задание:

1. Создать базу данных в заданной предметной области (по вариантам).

2. Создать формы для заполнения каждой таблицы БД.

3. Создать запрос на выборку по варианту.

4. Создать отчет по варианту.

Вариант 1

 

Разработать БД «Абитуриент» для автоматизации работы приемной комиссии ВУЗа. БД должна содержать четыре таблицы: анкеты абитуриентов, данные о специальностях, данные о дисциплинах и результаты экзаменов.

Анкета включает следующие данные об абитуриенте:

· регистрационный номер (ключевое поле);

· фамилия, имя, отчество;

· дата рождения;

· оконченное среднее учебное заведение (название, номер, населенный пункт);

· дата окончания учебного заведения;

· наличие красного диплома или золотой/серебряной медали;

· адрес (город, улица, номер дома, телефон);

· шифр специальности.

Данные о специальностях содержат:

· шифр специальности (ключевое поле).

· название специальности;

Данные о дисциплинах содержат:

· шифр дисциплины (ключевое поле).

· название дисциплины;

Результаты экзаменов содержат:

· регистрационный номер абитуриента;

· шифр дисциплины;

· экзаменационная оценка.

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

Создать отчет по результатам экзамена по информатике.

 

Вариант 2

 

Разработать БД «Зарплата» для автоматизации начисления почасовой заработной платы в бухгалтерии. База данных состоит из трех таблиц, содержащих сведения о работниках, ставки почасовой оплаты и табель отработанных часов.

На каждого работника хранятся следующие данные:

· личный номер (ключевое поле);

· фамилия, имя, отчество;

· отдел;

· должность;

· разряд;

Тарифная сетка для почасовой оплаты:

· должность (ключевое поле вместе с разрядом);

· разряд (от 7 до 15);

· ставка (руб/час).

Табель содержит:

· личный номер;

· месяц;

· количество часов, отработанных за месяц.

Создать запрос на выборку всех кассиров с количеством отработанных часов большим 150, который содержит их ФИО, отдел, разряд и ставку.

Создать отчет, содержащий сведения о работниках, их разрядах и ставках.

Вариант 3

 

Разработать БД «Оптовая база». На оптовой базе хранятся товары, получаемые от поставщиков. Потребителями являются организации, предварительно направляющие на базу заявки на товар. Отдел снабжения базы ведет учет движения товаров.

БД должна состоять из четырех таблиц: «Склад», «Товары», «Заявки» и «Отпуск товаров». Таблицы имеют следующую структуру:

«Склад»:

· код товара;

· количество;

· дата поступления.

«Товары»:

· код товара (ключевое поле);

· название товара;

· единица измерения;

«Заявки»:

· код заявки (ключевое поле);

· название организации;

· код товара;

· требуемое количество;

«Отпуск товаров»:

· код заявки (ключевое поле);

· код товара;

· отпущенное количество;

· дата отпуска товара.

Создать запрос на выборку всех телевизоров, количество которых на складе больше 15, который содержит код товара, его наименование и дату поступления на склад.

Создать отчет, содержащий информацию об отпуске товаров: название, единица измерения, количество и дата отпуска.

Вариант 4

 

Разработать БД «Потребительская корзина» для анализа уровня жизни в семье. Уровень жизни зависит от соотношения доходов семьи и цен на потребляемые продукты.

БД содержит 3 таблицы: «Продукты», «Доходы» и «Потребление». Таблицы имеют следующую структуру:

«Продукты»:

· код продукта (ключевое поле);

· наименование;

· ед. измерения.

«Доходы»:

· год, месяц (ключевое поле);

· совокупный доход за месяц.

«Потребление»:

· год, месяц;

· код продукта;

· количество;

· цена.

Создать запрос на выборку продукта, с наибольшей ценой, который содержит название продукта, единицу измерения и цену.

Создать отчет, содержащий информацию о потреблении продуктов: наименование продукта, единица измерения, год и месяц, количество и цена.

 

Вариант 5

 

Разработать БД «Библиотека» для учета хранимой и выданной читателям литературы. БД состоит из трех таблиц со следующей структурой:

«Книги»:

· шифр книги (ключевое поле);

· автор;

· название;

· год издания;

· количество экземпляров.

«Читатели»:

· номер читательского билета (ключевое поле),

· фамилия и инициалы,

· отдел (адрес);

«Выдача»:

· шифр книги;

· читательский билет;

· количество экземпляров;

· дата выдачи;

· дата возвращения;

· дата фактического возвращения.

Создать запрос на выборку всех читателей задолжников, который содержит их ФИО, номер читательского билета, и количество «просроченных» книг.

Создать отчет, содержащий информацию о выдаче книг: автор и название книги, ФИО читателя, его адрес, дата выдачи и возврата.

Вариант 6

 

Разработать БД «ГАИ» для учета нарушений правил дорожного движения водителями. БД состоит из четырех таблиц: «Автомобили», «Водители», «Нарушения» и «Сведения о нарушителях» со следующей структурой:

«Автомобили»:

· марка автомобиля;

· серия и номер технического паспорта (ключевое поле);

· государственный номер;

· номер двигателя;

· номер кузова;

· владелец;

· адрес владельца.

«Водители»:

· фамилия, имя и отчество водителя;

· адрес;

· серия и номер водительского удостоверения (ключевое поле).

«Нарушения»:

· название нарушения;

· шифр нарушения (ключевое поле);

«Сведения о нарушителях»:

· серия и номер водительского удостоверения;

· гос. номер автомобиля;

· шифр нарушения;

· дата нарушения.

Создать запрос на выборку всех водителей владельцев Toyota Camry оштрафованных за превышение скорости, который содержит их ФИО, гос. номер и дату нарушения.

Создать отчет, содержащий информацию о нарушителях: ФИО, адрес, марка автомобиля, гос. номер, название и дата нарушения.

 

Вариант 7

 

Разработать БД «Старт» для подсчета результатов соревнований. База данных состоит из трех таблиц:

«Участники»:

· фамилия и инициалы;

· стартовый номер (ключевое поле);

· шифр группы (учитывающий пол и возраст);

· спортивная организация.

«Протокол старта»:

· стартовый номер;

· время старта;

· отметка о не выходе на старт.

«Протокол финиша»:

· стартовый номер;

· время финиша;

· отметка о сходе с дистанции.

Создать запрос на выборку всех участников из ИрГСХА пришедших на финиш, который содержит их ФИО, стартовый номер и время финиша.

Создать отчет, содержащий информацию о финишировании участников: ФИО, организация, время финиша и отметка о сходе с дистанции.

 

Вариант 8

 

Разработать БД «Перевозки», для транспортного предприятия. На предприятии имеется пакет заявок от других организаций на перевозку различных грузов. БД состоит из трех таблиц: «Транспорт», «Заявки», «Доставка», имеющих следующую структуру:

«Транспорт»:

· марка автомобиля;

· государственный номер (ключевое поле);

· расход топлива (литров на 100 км.).

«Заявки»:

· код заявки (ключевое поле);

· дата;

· пункт отправления;

· пункт назначения;

· название груза;

· единица измерения;

· количество груза.

«Доставка»:

· дата и время отправления,

· дата и время возвращения,

· гос. номер автомобиля,

· код заявки,

· ед. измерения,

· количество фактически перевезенного груза,

· пройденное расстояние.

Создать запрос на выборку всех автомобилей отправляющихся из Иркутска, который содержит их марку, гос. номер, код заявки и пункт назначения.

Создать отчет, содержащий информацию о перевозках: марка автомобиля, гос. номер, пункты отправления и назначения, даты отправления и возвращения.

Вариант 9

 

Разработать БД «Сессия» для анализа успеваемости на факультете по конкретной специальности. БД состоит из четырех таблиц: «Студенты», «Экзамены», «Зачеты» и «Дисциплины» со следующей структурой:

«Студенты»:

· шифр студента (ключевое поле);

· фамилия, имя, отчество;

· курс;

· группа.

«Экзамены»:

· шифр студента;

· дата;

· шифр дисциплины;

· оценка.

«Зачеты»:

· шифр студента;

· дата;

· шифр дисциплины;

· зачет.

«Дисциплины»:

· шифр дисциплины (ключевое поле);

· название дисциплины.

Создать запрос на выборку всех студентов сдавших зачет по информатике, который содержит их ФИО, курс, группу и дату зачета.

Создать отчет, содержащий информацию о результатах экзаменов: ФИО студента, курс, группа, название дисциплины, дата сдачи и оценка.

Вариант 10

 

Разработать БД «Учебная нагрузка» для учета нагрузки преподавателя ВУЗа и автоматизации отчета о выполнении нагрузки. БД состоит из 3 таблиц со следующей структурой.

Таблица «Дисциплины»:

· код дисциплины (ключевое поле);

· название дисциплины;

· специальность

· курс;

Таблица «Виды нагрузки» (лекции, лабораторные работы, семинары, индивидуальная работа, зачеты, экзамены, прочее):

· тип занятия (ключевое поле);

· название нагрузки.

Таблица «Выполнение нагрузки»:

· дата;

· номер пары;

· номер группы (подгруппы);

· код дисциплины;

· название темы;

· тип занятия (код);

· количество часов.

Создать запрос на выборку всех лекций по информатике, который содержит дату, номер группы и название темы.

Создать отчет, содержащий информацию о выполнении нагрузки: название дисциплины, специальность, номер группы, дата, название нагрузки и количество часов.

Вариант 11

Разработать БД «Программное обеспечение» для хранения и обработки сведений о файлах на CD-ROM. БД должна состоять из файлов «CD-ROM», «Владельцы», «Файлы» со следующей структурой:

«CD-ROM»:

· шифр диска (ключевое поле);

· название диска;

· дата выпуска;

· шифр владельца.

«Владельцы»:

· шифр владельца (ключевое поле);

· фамилия, имя, отчество;

· адрес;

· телефон.

«Файлы»:

· название файла (пакета);

· объем в Кбайтах;

· шифр диска;

· пояснения о назначении и свойствах.

Создать запрос на выборку всех владельцев больших файлов (>10 Мб), который содержит их ФИО, адрес и шифр диска.

Создать отчет, содержащий информацию о файлах: название файла, объем, название диска, ФИО владельца, его адрес и телефон.

 

Вариант 12

 

Разработать БД «Классный руководитель». БД содержит три таблицы: «Ученики», «Успеваемость» и «Дисциплины» со следующей структурой.

« Ученики »:

· фамилия, имя;

· шифр ученика (ключевое поле);

· домашний адрес;

· телефон;

· фамилия, имя, отчество родителей.

«Успеваемость»:

· шифр дисциплины;

· шифр ученика;

· оценка;

· пропуск занятия;

· дата.

«Дисциплины»:

· шифр дисциплины (ключевое поле);

· название дисциплины.

Создать запрос на выборку всех учеников, у которых есть пропуски по информатике, который содержит их ФИО, ФИО родителей и телефон.

Создать отчет, содержащий информацию об успеваемости: ФИО ученика, название дисциплины, оценка и дата.

Задание 13

 

Разработать БД «Администратор гостиницы». БД содержит четыре таблицы: «Гостиничные номера», «Проживание», «Заявки на бронь» и «Информация о местах».

«Гостиничные номера»:

· номер комнаты (ключевое поле);

· количество мест.

«Проживание»:

· фамилия, имя, отчество;

· серия и номер паспорта;

· дата начала проживания;

· дата окончания проживания;

· номер комнаты;

· номер места.

«Заявки на бронь»:

· номер заявки;

· организация или лицо, подавшее заявку;

· заявляемое начало проживания;

· заявляемое окончание проживания;

· количество заявляемых мест.

«Информация о местах»:

· номер комнаты (ключевое поле вместе с номером места);

· номер места;

· состояние (занято, свободно или забронировано);

· номер заявки (если забронировано).

Создать запрос на выборку всех постояльцев, проживающих в трехместных номерах, который содержит их ФИО, номер комнаты и дату окончания проживания.

Создать отчет, содержащий информацию о проживании: ФИО постояльца, дата начала и окончания проживания, номер комнаты, количество мест и состояние.

 

Вариант 14

 

Разработать БД «Товарооборот» для промтоварного магазина. БД состоит из четырех таблиц: «Товары», «Поступление товаров», «Продажа товаров» и «Поставщики» со следующей структурой.

«Товары»:

· код товара (ключевое поле);

· наименование товара;

· единица измерения количества товара.

«Поступление товаров»:

· код товара;

· дата поступления;

· цена приобретения товара за единицу измерения;

· код поставщика.

«Продажа товаров»:

· код товара;

· месяц продажи;

· проданное количество за месяц;

· цена продажи товара.

«Поставщики»:

· код поставщика (ключевое поле);

· название поставщика;

· адрес поставщика;

· телефон поставщика.

Создать запрос на выборку всех товаров, поступивших в этом месяце, который содержит код, наименование и цену.

Создать отчет, содержащий информацию о продаже товаров: месяц продажи, наименование товара, ед. измерения, дата поступления, цены приобретения и продажи, проданное количество за месяц.

Вариант 15

 

Разработать БД «Промышленность региона». БД состоит из трех таблиц: «Промышленные предприятия», «Уплата налогов», «Прибыль», «Виды налогов» со следующей структурой.

«Промышленные предприятия»:

· код предприятия (ключевое поле);

· название предприятия;

· адрес предприятия;

· форма собственности;

· выпускаемая продукция.

«Уплата налогов»:

· код предприятия;

· код налога;

· сумма налога;

· фактическая дата уплаты;

· планируемая дата уплаты.

«Прибыль»:

· код предприятия;

· сумма прибыли за год.

«Виды налогов» (предусмотреть 3 вида налогов):

· код налога (ключевое поле);

· название налога.

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

Создать отчет, содержащий информацию об уплате налогов: название предприятия, сумма прибыли за год, название налога, его сумма, дат планируемой и фактической уплаты.