Запросы с вычисляемыми полями.

Вывести наименования заказчиков:

а) располагающихся в Северодвинске (рекомендуемая функция InStr)

б) оформивших заказ в январе-апреле 1999 года (BETWEEN)

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

 

Вывести номера заказов, дату оформления, дату готовности и:

г) количество лет и месяцев, потребовавшихся на выполнение заказа

 

Вывести номера заказов, дату оформления, дату готовности для заказов:

д) оформленных в конкретном году (год оформления указать в качестве параметра)

е) оформленных определенным заказчиком (код заказчика указать в качестве параметра)

ж) которые будут готовы в текущем месяце.

 

Порядок выполнения работы:

Создание запроса, содержащего вычисляемые поля:

В окне конструктора запроса перейдите к первому чистому столбцу запроса.

Введите имя столбца с двоеточием после него, а затем выражение: например, Год:Year([Дата оформления]). Если не ввести имя поля и двоеточие, Access по умолчанию предоставит вычисляемое поле с именем Выражение1. Для использования “Построителя выражений” при вводе выражения нажмите кнопку “Построить” на панели инструментов.

При необходимости введите критерий отбора в строке “Условие отбора” для вычисляемого столбца.

 

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

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

Введите строку-подсказку, заключив ее текст в квадратные скобки, например: [Введите код заказчика:].

Нажмите кнопку “Запуск”.

 

Общие сведения:

 

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

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

Пункт меню? ® Вызов справки ® Вкладка "Содержание" ® Работа с запросами ® Проведение вычислений.

Если запрос требуется выполнять многократно, постоянно изменяя условие отбора, то можно преобразовать его в запрос с параметрами. Запросы с параметрами позволяют задать критерий отбора с помощью диалогового окна “Введите значение параметра”.

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

Пункт меню? ® Вызов справки ® Вкладка "Содержание" ® Работа с запросами ® Создание перекрестных запросов и запросов с параметрами ® Запросы с параметрами.

 

Контрольные вопросы:

1. Как создать вычисляемое поле?

2. Как использовать построитель выражений?

3. Можно ли использовать вычисляемые поля в условии отбора?

4. Как создать запрос с параметром?

 

Составление отчетов:

Студенту необходимо продемонстрировать знание работы с Access.

Показать выполненное задание преподавателю.

Ответить на контрольные вопросы.

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

Цель работы: Количество часов: 2

 

Научиться составлять запросы, используя групповые операции.

 

Рабочее задание:

 

Создайте запросы с использованием групповых операций:

 

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

б) количество комплектующих для каждого изделия собственного производства по категориям деталь и материал

в) общее количество комплектующих, необходимых для выполнения каждого заказа

г) количество заказов, оформленных по годам (отсортировать по годам)

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

е) вывести номер заказа, время на изготовление которого оказалось минимальным

ж) количество заказов, оформленных каждым заказчиком

з) самое раскупаемое изделие собственного производства

и) среднее количество заказов в год

 

 

Порядок выполнения работы:

 

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

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

3. Для поля или полей, по которым выполняется группировка, оставьте значение Группировка в ячейке Групповая операция.

4. Для каждого поля, которое следует сделать вычисляемым, выберите его ячейку в строке Групповая операция, а затем выберите одну из следующих статистических функций: Sum, Avg, Min, Max, Count.

5. При необходимости введите условие отбора.

6. При необходимости укажите порядок сортировки.

7. Нажмите кнопку Вид на панели инструментов.

Общие сведения:

 

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

Avg() – вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле диапазона;

Count() – вычисляет количество непустых записей, возвращаемых запросом;

Sum() – возвращает сумму набора значений, содержащихся в заданном поле;

Max() – вычисляется максимальное значение данного поля для всех записей, отобранных запросом;

Min() – вычисляется минимальное значение данного поля для всех записей, отобранных запросом и т.д.

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

 

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

Пункт меню? ® Вызов справки ® Вкладка "Содержание" ® Работа с запросами ® Проведение вычислений ® Выполнение вычислений в запросе ® Дополнительные сведения о каждом типе итоговых вычислений

 

Контрольные вопросы:

1. Какие виды групповых операций вы знаете?

2. Как перейти в режим групповых операций?

3. Какая агрегатная функция подсчитывает арифметическое среднее?

 

Составление отчетов:

 

Студенту необходимо продемонстрировать знание работы с Access.

Показать выполненное задание преподавателю.

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

 

Работа с отчетами.

Цель работы:Количество часов: 2

 

изучение способов создания отчетов.

 

Рабочее задание:

 

Замечание: перед созданием отчетов необходимо установить принтер (Пуск®Настройка®Принтеры®Установка принтера)

Составить отчет «Спецификация» (см. рис.) на основе запроса с параметром. В качестве параметра вводится код изделия. В форме «Изделие» добавить кнопку «Просмотр спецификации» на основе созданного отчета. Вместо параметра Код изделия – создать фильтр, открывающий отчет только на то изделие, что отражается в форме на данный момент.

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

Создать отчет «Квитанция к заказу» в произвольном виде, расположить на форме «Заказ». В отчете должны отражаться сведения только по одному заказу, который отражается в данный момент на форме.

Далее сделать отчеты на основе двух-трех существующих запросов.

Добавить отчеты, не связанные с формами в Главную кнопочную форму и в пункт меню «Формирование документов».

Спецификация на изделие:

КЛЮЗ

Наименование количество

деталь

гайка 6

плита 4

стяжка 3

материал

Герметик 51-УТ-3 0,3

 

 

Порядок выполнения работы:

Создание отчета при помощи “Мастера отчетов”

1. Раскройте вкладку “Отчеты” окна базы данных, нажмите кнопку “Создать”. Появится диалоговое окно “Новый отчет”.

2. В списке диалогового окна выделите элемент “Мастер отчетов”.

3. В раскрывающемся списке, находящемся в нижней части диалогового окна “Новый отчет”, содержатся имена всех таблиц и запросов базы данных, которые могут быть использованы в качестве источника данных для отчета. Щелкните левой кнопкой мыши по кнопке со стрелкой для раскрытия списка и выделите соответствующий элемент. Нажмите кнопку “OK” и Access отобразит начальное диалоговое окно “Мастера отчетов”.

4. Определите какие поля будут образовывать строки отчета. Если вы хотите изменить порядок полей, то воспользуйтесь кнопкой “<” для переноса поля обратно в список “Доступные поля”.

5. В следующем диалоговом окне “Мастер отчетов” спросит, хотите ли вы добавить уровни группировки к отчету. Выделите в списке поле, по которому будете группировать записи и нажмите кнопку “>”. При нажатиии кнопки “Группировка” появляется диалоговое окно “Интервалы группировки”. Изменяя интервал группировки, можно влиять на то, как Access группирует данные в отчете. Для числовых полей можно задать группировку по десяткам, сотням и т.д. Для текстовых полей возможна группировка по первой букве, первым трем буквам и т.д. Если для отчета не требуется специальный интервал группировки, то выберите из списка вариант “обычный ”.

6. В пределах группы можно сортировать записи по значению произвольного поля, при этом сортировка производится не более чем по четырем полям. Порядок сортировки выбирается в следующем диалоговом окне. Для вывода диалогового окна “Итоги” нажмите на кнопку “Итоги”. Если вы хотите указать для столбца отчета итоговую информацию, то можно задать параметры для выбранного столбца с помощью данного диалогового окна. “Мастер отчетов” перечислит все числовые поля отчета, не являющиеся счетчиками, и предложит установить для указанного столбца флажки Sum, Min и т.д. Группа “Показать” позволяет выбрать, следует ли в отчете указывать только итоги или необходимо выдавать полный отчет.

7. Далее, в следующем окне “Мастер” предложит вам выбрать для отчета один из шести стилей макета. По умолчанию здесь же установлен флажок “Настроить ширину полей для размещения на одной странице?” для экономии бумаги и удобства чтения. В группе “Ориентация” выбирается ориентация отчета на принтере. Она бывает книжной или альбомной.

8. В следующем диалоговом окне выбирается стиль отчета (чаще всего компактный).

9. В последнем диалоговом окне выбирается заголовок отчета.

Изменение отчета:

1. Откройте отчет в режиме конструктора.

2. Для удаления элемента из отчета, выделите его и нажмите клавишу <Delete>.

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

4. Для выравнивания и форматирования элементов сначала выделите их и выберите нужную команду меню Формат.

5. Чтобы добавить в форму надпись, нажмите кнопку “Надпись” на панели элементов и добавьте в отчет текстовое поле.

6. Чтобы добавить в форму вычисляемое поле, нажмите кнопку “Поле” на панели элементов и добавьте в отчет текстовое поле. Введите в качестве значения текстового поля выражение, начинающееся со знака “=”.

 

Общие сведения:

 

Создание отчетов — это метод, с помощью которого данные могут быть представлены в выразительном и легком для понимания виде. Отчеты могут быть представлены, напечатаны или экспортированы в другой формат. Создание отчетов в Access не представляет трудностей. Проект может быть впоследствии изменен для наилучшего предоставления данных пользователю.

Отчет похож на форму. Однако отчеты только отображают данные; они не могут быть использованы для ввода данных. Отчеты могут содержать множество одинаковых элементов и компоноваться как формы, за исключением элементов управления, которые требуют взаимодействия с пользователем (например, спис­ки, надписи и др.).

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

Ниже приведены доступные для применения методы создания нового отчета:

• Конструктор: Новый отчет создается вручную.

• Мастер отчетов: Мастер Access сопровождает процесс создания отчета.

• Автоотчет: в столбец: Создается отчет, который отображает поля из таблицы в одном столбце.

•Автоотчет: ленточный: Создается отчет, который отображает данные в таблич­ном формате, аналогичном электронной таблице.

• Диаграмма: Мастер сопровождает процесс вставки диаграммы в отчет.

• Почтовые наклейки: Мастер сопровождает процесс создания отчета, форматирован­ного для печати почтовых наклеек.

 

 

Конструктор отчета

 

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

 

Мастер отчетов

 

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

 

Контрольные вопросы:

 

1. Какие методы создания отчетов вы знаете?

2. Что такое группировка?

3. На основе каких объектов базы данных можно создать отчет?

4. Как изменить структуру отчета?

5. Какие режимы работы с отчетом существуют в Access?

 

 

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

«Создание таблиц, загрузка и корректировка данных с помощью программы Quest SoftWare TOAD»

Цель работы: Количество часов: 2

Знакомство с программой Quest SoftWare TOAD. Освоение создания структур таблиц. Определение внешних и первичных ключей в таблицах. Загрузка данных в таблицы, просмотр данных в таблицах и их корректировка.

 

Рабочее задание:

 

1. Создать пять таблиц с именами PERSONS, GRUPPY, OBUCHENIE, FORMY_OB, SPECIAL. Имя каждой из таблиц должно оканчиваться на ваш порядковый номер по журналу (можете уточнить свой номер у преподавателя). При создании таблиц – определить первичный или внешний ключ, для каждого поля - тип данных, число символов, обязательность или необязательность. Данные для каждого поля взять из рисунка 3.1.

 

 

Рисунок 11.1. Физическая модель данных

 

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

3. Сдать работу преподавателю.

 

Общие сведения:

 

Создание таблицы.

Таблицы определяются с помощью команды CREATE TABLE.

Синтаксис команды:

 

CREATE TABLE <имя таблицы>

(<имя столбца><тип данных>(<размер>),

<имя столбца><тип данных>(<размер>), …);

 

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

CHAR, VARCHAR2 (CHARACTER) – текстовый, для этого типа аргумент размера – целое неотрицательное число, задающее максимальную длину строки. Значения этого типа заключаются в кавычки;

NUMBER, INTEGER – число без явно представленной десятичной точки. Аргумент размера можно не использовать. В этом случае он назначается автоматически в зависимости от конкретного способа реализации (в большинстве случаях – 8 символов).

DEC (DECIMAL) – десятичное число, т.е. число, которое может иметь в своем представлении десятичную точку. Соответственно, аргумент размера имеет две части: m - точность и n – масштаб, т.е. decimal (m,n). Масштаб не может превышать точность. Точность показывает количество значащих десятичных разрядов.

DATE – дата/время. Аргумент размера не задается. Форматом даты является краткий вид: дд.мм.гг.

Примечание! В SQL пробелы используются для разделения отдельных частей команд, поэтому их нельзя использовать как часть имени таблицы (либо как часть какого-либо другого объекта, например индекса). Символ подчеркивания наиболее часто используется для разделения слов в именах таблиц.

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

Первичные и внешние ключи.

Когда все значения одного из полей таблицы должны быть представлены в поле другой таблицы, это означает, что поле ссылается на (refers to) или является ссылкой (references) на таблицу. Это свидетельствует о прямой связи между значениями двух полей.

Когда поле таблицы ссылается на другое поле (в другой таблице), оно называется внешним ключом (foreign key), поле, на которое он ссылается, называется его родительским ключом (primary key). Имена внешнего и родительского ключей могут быть неодинаковыми; одинаковые имена - это соглашение, принятое с целью сделать связи более понятными.

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

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

Ограничение FOREIGN KEY используется в команде CREATE TABLE (или в команде ALTER TABLE), содержащей поле, которое желательно объявить внешним ключом. Указывается имя родительского ключа, на который есть ссыл­ка в ограничении внешнего ключа. Это ограничение, как и любое другое рас­смотренное ранее, размещается в команде.

Синтаксис ограничения на таблицу FOREIGN KEY:

 

FOREIGN KEY <список столбцов>

REFERENCES <имя таблицы> [<список столбцов>]

 

Первый список столбцов представляет собой заключенный в круглые скобки список из одного или более столбцов таблицы, перечисленных через запятую, создаваемых или изменяемых этой командой. Предложение REFERENCES за­дает имя таблицы, содержащей родительский ключ. Это может быть та же самая таблица, которая создается или изменяется в текущей команде. Второй список столбцов представляет собой заключенный в круглые скобки список столбцов, которые формируют родительский ключ (имена столбцов указываются через запятую). Два списка столбце должны быть сравнимы, т.е.:

1. Они должны иметь одинаковое количество столбцов.

2. Первый, второй, третий и т.д. элемент списка столбцов внешнего ключа должны иметь тот же тип и размер, что и соответствующие элементы -первый, второй, третий и т.д. — списка родительского ключа.

 

Используя ограничение на таблицу или на столбец FOREIGN KEY, можно опустить список столбцов родительского ключа, если он имеет ограничение PRIMARY KEY.

Родительский ключ должен быть структурирован так, чтобы была уверенность, что каждое значение внешнего ключа соответствует отдельной строке. Это означает, что оно должно быть уникальным и не должно содержать NULL -значений. Недостаточно, чтобы родительский ключ полностью удовлетворял этим требованиям только в тот момент, когда объявляется внешний ключ. SQL должен гарантировать, что повторяющиеся или NULL -значения не будут введены в родительский ключ. Следовательно, необходимо иметь уверенность, что все поля, которые используются как родительские ключи, имеют либо ограничение PRIMARY KEY, либо ограничение UNIQUE, а также ограничение NOT NULL.

Синтаксис команды CREATE TABLE с определением родительского ключа:

CREATE TABLE <имя таблицы>

(<имя столбца><тип данных>(размер) primary key,

<имя столбца><тип данных>(размер) not null, …);

 

Использование ограничений для исключения NULL-значения.

NULL – специальный символ, обозначающий что поле пусто. Если при создании таблицы не было указано никаких ограничений NULL-значений, то столбцы таблицы автоматически формируются так, что могут содержать пустые поля. Но это полезно не всегда. Первичные ключи никогда не содержат NULL-значений, поскольку необходимо, чтобы поля содержали определенные значения. Для этого используется ключевое поле NOT NULL. Если при создании таблицы NOT NULL размещается непосредственно после типа данных (включая размер) столбца, то любые попытки ввести NULL-значения в данное поле будут отвергнуты.

 

Пример:

CREATE TABLE <имя таблицы>

(<имя столбца><тип данных>(<размер>) NOT NULL primary key,

<имя столбца><тип данных>(<размер>), NOT NULL …,

foreign key (имя столбца)

references (имя таблицы (список полей)));