Создание итогового запроса

Запросы, выполняющие вычисления по всем записям для какого-либо числового поля, называются итоговыми запросами.

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

Предположим, что малое предприятие собирает компьютеры трех классов: «Элитный», «Деловой», «Экономичный».

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

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

Порядок работы:

1. В окне ФамилияСборкаПК: база данных открыть панель Таблицы и выбрать таблицу Комплектующие.

2. Щелчком на значке Конструктор открыть структуру таблицы и создать дополнительное поле Класс, в котором будут храниться данные о том, для какого класса изделий предназначены соответствующие компоненты. Для этого выделить первое поле (Компонент) и нажать клавишу INSERT, а затем в начало структуры таблицы вставить имя нового поля Класс и определить его тип как Текстовый.

3. Закрыть окно Конструктора. При закрытии подтвердить необходимость изменения структуры таблицы.

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

; Жесткий диск

; Видеокарта

; Клавиатура

; Мышь

; Оперативная память

; Корпус

; CD-ROM

; Дисковод гибких дисков

; Видеоадаптер

; Звуковая карта

Цены и названия модели проставить произвольно. Поле Основной параметр можно не заполнять.

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

Таблица 1: Комплектующие

Класс	Компонент	Модель	Основной параметр	Цена
Элитный	Процессор	Pentium III 600
Экономичный	Процессор	Celeron 466
Деловой	Процессор	Celeron 500
Экономичный	Жесткий диск	Western Digital, Caviar 64AA	6,4
Элитный	Жесткий диск	Western Digital, Caviar 313000
Деловой	Жесткий диск	Western Digital, Caviar 102AA	10,2
Экономичный	Видеокарта	AGP, PowerColor
Деловой	Видеокарта	AGP, PowerColor M64
Элитный	Видеокарта	AGP, Matrox G400
Элитный	Клавиатура	А1
Деловой	Клавиатура	А2
Экономичный	Клавиатура	А3
…	…	…	…	…

5. Закрыть таблицу Комплектующие.

6. Открыть панель Запросы в окне ФамилияСборкаПК: база данных.

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

8. В бланк запроса по образцу ввести следующие поля таблицы Комплектующие: Класс, Компонент, Цена.

9. Для поля Класс включить сортировку по возрастанию.

10. На панели инструментов Microsoft Access щелкнуть на кнопке Групповые операции или воспользоваться меню:

Вид Þ Групповые операции

Эта команда необходима для создания в нижней части бланка строки Групповые операции. На ее базе и создаются итоговые вычисления. Все поля, отобранные для запроса, получают в этой строке значение Группировка.

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

Для поля Цена выбрать итоговую функцию SUM для определения стоимости изделия как суммы стоимостей комплектующих.

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

11. Закрыть бланк запроса по образцу и присвоить ему имя: Расчет стоимости изделия.

12. Запустить запрос и убедиться, что он работает правильно.

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

Класс	Компонент	Цена
Деловой
Элитный
Экономичный

В данной таблице в поле Компонент приведено количество компонентов, из которых состоит компьютер данного класса, а в поле Цена приведена стоимость компьютера данного класса.

Для вычисления количества компонентов использовалась функция COUNT, а для вычисления стоимости использовалась функция SUM.

3.3. Задания к лабораторным работам по теме MS Access

Задание 1

1. Создать базу данных, состоящую из трех таблиц, прилагаемых к данному заданию.

Присвоить базе данных имя ФамилияУниверситет.

Таблица1 с именем Студенты содержит данные о студентах университета с указанием номера студенческого билета (НомСБ), фамилии, имени, отчества, места жительства (МЖ), даты зачисления (ДатаЗачисл), оценок по математике (Мат) и физике (Физ), полученных на вступительных экзаменах. Ключевое поле – НомСБ.

Таблица2 с именем Факультеты содержит сведения о факультетах университета с указанием кода факультета (КодФак), сокращенного наименования факультета (СокрНаимФак), полного наименования факультета (ПолнНаимФак). Ключевое поле – КодФак.

Таблица3 с именем Города содержит сведения о городах с указанием кода города (КодГор) и названия города (Город). Ключевое поле – КодГор.

Таблица 1: Студенты

НомСБ	Фамилия	Имя	Отчество	Фак	МЖ	ДатаЗачисл	Мат	Физ
	Фомин	Артем	Викторович			25.07.02
	Иващенко	Надежда	Васильевна			16.06.02
	Петренко	Владимир	Иванович			15.06.02
	Дейнека	Валентин	Юрьевич			18.07.03
	Гоголь	Илья	Николаевич			12.07.03
	Косогов	Федор	Иванович			26.06.03
	Симонов	Олег	Олегович			28.07.03
	Литвинов	Николай	Степанович			07.07.03
	Подчинок	Артем	Викторович			01.08.03
	Байда	Дарья	Юрьевна			01.08.03
	Туник	Анна	Викторовна			01.08.03
	Крошкин	Виталий	Иванович			01.08.03
	Иванов	Иван	Иванович			14.07.04
	Волкова	Ольга	Алексеевна			30.07.04
	Кугай	Ирина	Сергеевна			29.06.04
	Сидоров	Алексей	Вадимович			09.06.04
	Петов	Сергей	Петрович			06.07.04
	Ковалева	Инна	Георгиевна			01.08.03
	Степовая	Диана	Юрьевна			02.06.04
	Филина	Анна	Семеновна			03.07.04

Таблица 2: Факультеты

КодФак	СокрНаимФак	ПолнНаимФак
	ФЭУ	Факультет экономики и управления
	СМФ	Судомеханический факультет
	ФТТС	Факультет транспортных технологий и систем
	ГТФ	Гидротехнический факультет
	ДПФ	Договорно-правовой факультет
	ФМП	Факультет механизации портов
	КСФ	Кораблестроительный факультет

Таблица 3: Города

КодГорода	Город
	Одесса
	Киев
	Запорожье
	Николаев
	Херсон
	Симферополь
	Измаил
	Севастополь
	Харьков

2. Создать Запрос1 с именем Дата зачисления, содержащий поля: Фамилия, Имя, СокрНаимФак (сокращенное наименование факультета), ДатаЗачисл (дата зачисления).

Выбрать студентов заданного факультета (например, ФЭУ), зачисленных ранее 01.07.04 и выполнить сортировку по алфавиту в поле Фамилия.

Для этого при создании структуры запроса после выбора заданных полей выполнить следующие действия:

- в строку Условие отбора поля СокрНаимФак ввести текст

ФЭУ

- в строку Условие отбора поля ДатаЗачисл ввести следующий набор символов

<01.07.04

- в строке Сортировка поля Фамилия выбрать

По возрастанию

3. Создать Запрос2 с именем Оценки, содержащий поля: НомСБ (номер студенческого билета), Фамилия, ПолнНаимФак (полное наименование факультета), Город, Мат (оценка по математике), Физ (оценка по физике), Город, МЖ (место жительства).

Выбрать студентов, проживающих в заданном городе (например, в Одессе).

Для этого при создании структуры запроса после выбора заданных полей выполнить следующие действия:

- в строку Условие отбора поля МЖ ввести код города(например, 1);

- удалить галочку в поле МЖ, чтобы код города не выводился на экран при выводе таблицы, полученной по данному запросу.

Примечание. Выбрать студентов, проживающих в заданном городе (например, в Одессе) можно и иначе.

Для этого в строку Условие отбора поля Город ввести название города (например, Одесса), а поле МЖ (место жительства) можно вообще в запрос не включать.

5. Создать Запрос3 с именем Средний балл, содержащий поля: Фамилия, Имя, СокрНаимФак (сокращенное наименование факультета).

Выбрать студентов заданного факультета (например, ФЭУ), имеющих средний балл, больший заданного числа (например, >7).

Для этого при создании структуры запроса после выбора заданных полей выполнить следующие действия:

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

Средний балл: ([Мат]+[Физ]) / 2

Примечание. Выражение ([Мат]+[Физ])/2 определяет формулу для вычисления среднего балла, как среднего арифметического двух оценок.

- в строку Условие отбора поля СокрНаимФак ввести текст:

ФЭУ

- в строку Условие отбора поля Средний балл ввести текст:

>7

5. Создать форму с именем Средний балл по Запросу3 (Средний балл).

Вставить кнопки: «следующая запись», «предыдущая запись», «закрыть форму».

Примечание. Эта форма может быть использована для вывода на экран (на печать) записей, выбираемых по Запросу3.

6. Создать отчет с именем Оценки по Запросу2 (Оценки).

7. Создать форму с именем Студенты по таблице1 (Студенты). Вставить кнопки: «добавить запись» и «удалить запись».

Примечание. Эта форма может быть использована для редактирования базы данных, т.е. для внесения изменений и ввода новых данных в базу данных.

Выполнить редактирование таблицы1 (Студенты):

- добавить в таблицу новые записи, т.е. добавить сведения о студентах, вновь поступивших в ВУЗ, и переведенных из других ВУЗов;

- удалить из таблицы ненужные записи, т.е. удалить из таблицы сведения о студентах, отчисленных из университета.

Данные для редактирования таблицы выбрать произвольно.

Задание 2

1. Создать базу данных, состоящую из трех таблиц, прилагаемых к данному заданию.

Присвоить базе данных имя ФамилияСклад.

Таблица1 с именем Учет товаров содержит следующие поля: НомНакл (номер накладной), Товар, Фирма, ДатаЗакупки, Колич (количество). Ключевое поле – НомНакл.

Таблица2 с именем Товары содержит следующие поля: КодТовара, Цена, НаимТовара (наименование товара). Ключевое поле – КодТовара.

Таблица3 с именем Фирмы содержит следующие поля: КодФирмы, НазвФирмы (название фирмы). Ключевое поле – КодФирмы.

2. Создать Запрос1, состоящий из следующих полей: НаимТовара, ДатаЗакупки, НазваниеФирмы, КодФирмы (без вывода на экран).

Выбрать относящиеся к заданной фирме записи, в которых приведены сведения о закупках, произведенных позже заданной даты (например, после 1 мая 2003 года) и выполнить сортировку в поле ДатаЗакупки по возрастанию дат. Пусть, например, заданной фирмой является фирма Элита, а ее код, как видно из Таблицы3 (Фирмы) равен 1.

Для этого при создании структуры запроса после выбора заданных полей выполнить следующие действия:

- в строку Условие отбора поля КодФирмы ввести код заданной фирмы

- в строку Условие отбора поля ДатаЗакупки ввести следующий набор символов

>01.05.03

- в строке Сортировка поля ДатаЗакупки выбрать

По возрастанию

- в поле КодФирмы удалить галочку, чтобы код фирмы не выводился на экран при выводе таблицы, полученной по данному запросу.

Таблица 1: Учет товаров

НомНакл	Товар	Фирма	ДатаЗакупки	Колич
			11.05.04
			02.03.04
			21.05.04
			15.11.03
			03.14.04
			06.11.03
			05.05.04
			12.06.04
			13.05.04
			17.04.03

Таблица 2: Товары

КодТовара	Цена	НаимТовара
	10,25	Пиломатериалы
	15,40	Краска
	28,00	Цемент
	34,12	Кирпич
	12,28	Обои

Таблица 3: Фирмы

КодФирмы	НазвФирмы
	Элита
	Герокс
	Оферта
	МММ
	Сокол

4. Создать Запрос2, состоящий из следующих полей: НаимТовара, НазваниеФирмы, Стоимость.

Выбрать только те записи, в которых стоимость товара меньше или равна заданной величины (например, меньше 200). В поле НаимТовара выполнить сортировку по алфавиту.

Для этого при создании структуры запроса после выбора заданных полей выполнить следующие действия:

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

Стоимость: [Цена]*[Колич]

Примечание. Выражение [Цена]*[Колич] определяет формулу для вычисления стоимости товара.

- в строку Условие отбора поля Стоимость ввести текст:

<=200

- в строке Сортировка поля НаимТовара выбрать

По возрастанию

Задание 3

1. Создать базу данных, состоящую из трех таблиц, прилагаемых к данному заданию.

Присвоить базе данных имя ФамилияАвтобаза.

Таблица1 с именем АрендаАвтомобилей содержит следующие поля: НомПЛ (номер путевого листа), Дата, КодАвто (код автомобиля), КодЗак (код заказчика), Пробег, СуммаАренды. Ключевое поле – НомПЛ.

Таблица2 с именем Автомобили содержит следующие поля: КодАвто, МодельАвто, ГП (грузоподъемность). Ключевое поле – КодАвто.

Таблица3 с именем Заказчики содержит следующие поля: КодЗак, НазвФирмы (название фирмы), Район. Ключевое поле – КодЗак.

2.Создать Запрос1, состоящий из следующих полей: НомПЛ, Дата, СуммаАренды, МодельАвто, НазвФирмы.

Выбрать только те записи, в которых содержится автомобиль модели МАЗ и дата заказа >01.08.04. Записи отсортировать по алфавиту названий фирм.

5. Создать Запрос2, состоящий из следующих полей: НомПЛ, СуммаАренды, МодельАвто, Район.

Вывести данные по Ильичевскому району с суммой аренды < 200.

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

4. Создать Отчет1 и Отчет2 по Запросу1 и Запросу2 соответственно.

Таблица 1: АрендаАвтомобилей

НомПЛ	Дата	КодАвто	КодЗак	Пробег	СуммаАренды
	02.09.04			200,5	220,50
	12.08.04			150,6	170,25
	11.07.04			300,4	210,45
	13.08.04			230,2	255,30
	10.09.04			150,5	110,52
	11.10.04			170,2	190,80
	11.07.04			210,6	196,60
	13.08.04			85,3	67,45
	02.07.04			130,2	160,25
	21.08.04			97,3	88,00

Таблица 2:Автомобили

КодАвто	МодельАвто	ГП
	МАЗ
	КАМАЗ
	УРАЛ
	ТАТРА
	ГАЗ

Таблица 3:Заказчики

КодЗак	НазвФирмы	Район
	Ирен	Центральный
	Вера	Ильичевский
	Импульс	Суворовский
	Интекс	Ленинский
	Вектор	Приморский

Задание 4

1. Создать базу данных, состоящую из трех таблиц, прилагаемых к данному заданию.

Присвоить базе данных имя ФамилияПоставкаТовара.

Таблица1 с именем Поставки содержит следующие поля: КодТовара, Колич, Цена (цена товара), КодПоставщика, ДатаПоставки. Ключевое поле – не задано.

Таблица2 с именем Товары содержит следующие поля: КодТовара, Категория, НазваниеТовара. Ключевое поле – КодТовара.

Таблица3 с именем Поставщики содержит следующие поля: КодПоставщика, НазваниеПоставщика. Ключевое поле – КодПоставщика.

2. Создать Запрос1: ПереченьПоставок, содержащий следующие поля:Категория, Название товара, КодТовара (без вывода на экран), Колич, Цена, ДатаПоставки.

Выбрать информацию обо всех поставках товаров данного наименования (например, джинсов) и выполнить сортировку по датам поставки товаров.

3. Создать Запрос2 с именем ПоставкиПоКатегории, содержащийследующие поля: НазваниеТовара, Категория, НазваниеПоставщика, Колич, Цена.

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

4. Создать Запрос3 с именем ПоставкиПоДате, содержащийследующие поля: Категория, НазваниеТовара, НазваниеПоставщика, Колич, ДатаПоставки.

Выбрать информацию обо всех поставках товаров не ранее определенной даты и выполнить сортировку по категории и названию товара (по возрастанию).

Предусмотреть, чтобы при запуске запроса на экране появлялось диалоговое окно с приглашением

Дата добавления: 2015-11-14