Использование в запросах вычисляемых полей

Структура таблиц.

Структурирование данных – это введение соглашений о способах представления данных.

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

Имя поля[1] вводится взамен наименования столбца. Каждому полю определяется тип.

Типы полей

Поля могут иметь следующие типы:

текстовый,
числовой,
денежный,
счетчик,
дата\время,
логический,
поле МЕМО,
мастер подстановок.

Каждый из типов данных наделён собственными свойствами: размер поля, формат поля, число десятичных знаков, индексированное и др.

Текстовые поля по умолчанию имеют размер – 50 знаков, но могут иметь от 1 до 255.

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

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

Таблица 1

Значение	Описание	Дробная часть
Байт	Числа от 0 до 255	Нет
Целое	Числа от –32768 до 32768	Нет
Длинное целое	Числа от –2147483648 до 2147483647	Нет
Одинарное с плавающей точкой	Числа от –3.40282310³⁸до3.40282310³⁸
Двойное с плавающей точкой	Числа от - 1.7976931348623210³⁰⁸до 1.7976931348623210³⁰⁸

Для указания количества десятичных знаков используется свойство поля ЧИСЛО ДЕСЯТИЧНЫХ ЗНАКОВ (от 0 до 15). Атрибут АВТО служит для автоматической установки количества десятичных знаков после запятой.

Денежное поле аналогично числовому. Число десятичных знаков после запятой равно 2.

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

Поля Дата/Время используют разные форматы от 1 января 100 года до 31 декабря 9999 года.

Поля МЕМО тестовые произвольной длины до 64 000 символов.

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

Задание 1

Изучите содержание столбцов в таблицах 2 – 6 (приложение 1), составьте список полей и присвойте каждому собственное имя. Затем по значениям, которые встречаются в таблицах, определите типы полей. Размер определяется по максимальному значению реквизита либо часто встречающемуся. Для числовых полей следует учесть максимальную длину целой, дробной части и разделитель «,». В таблице 2 приведен пример определения типа и свойств полей. Определите свойства всех полей в таблице 2.

Таблица 2

Свойства полей БД «Агроном»

№ п/п	Имя поля	Размер	Тип	Десят. Зн.	Значение[2]
	Культура		Текстовый
	Площадь, га		Числовой		Одинарное с плав. точкой
	И т. д.

Задание 2

Задайте структуру таблиц, используя Субд ACCESS.

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

· Загрузите программу Microsoft ACCESS. (Пуск / Программы / Microsoft Access).

· В окне установите переключатель для «новой базы», щелкните ОК.

· Объявите имя новой БД - АГРОНОМ, щелкните по кнопке Создать.

· В левой его части окна базы данных выделите объект «Таблицы», в правой части предлагаются разные приемы создания таблиц. Выберите «Создание таблицы в режиме конструктора» и щелкните на кнопку панели .

· В следующем окне «Новая таблица» ещё раз выберите режим «Конструктор», ОК. Окно конструктора используется для ввода структуры таблицы. Для таблицы РАЙОНЫ показан образец заполнения. В верхней части окна вводится имя поля, выбирается тип (через выбор из раскрывающегося списка), а в нижней части окна уточняются свойства поля.

Свойства полей: «Обязательное поле», «Пустое поле», «Индексированное поле» могут иметь значения - ДА, НЕТ. Настройка этих полей используется для контроля при вводе данных. Значения следует определять, анализируя информацию таблицы.

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

· Заполните структуры всех таблиц.

Задание 3

Заполните все таблицы исходными данными.

В окне базы данных выделите имя таблицы, щелкните по кнопке на панели инструментов и заполните данные из приложения1. При вводе данных наблюдайте заполнение поля Счетчик порядковыми номерами. Эти ячейки пропускайте клавишей TAB, они заполняются автоматически:1, 2, 3… Закрытие окна сохраняет введенные записи.

Задание 4

Установите связи между таблицами и типы отношений.

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

· Выполните пункты меню Сервис – Схема данных.

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

· Закрыть окно Добавление таблиц.

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

· Указателем мыши «ухватить» поле Хозяйство таблицы Районы и «отбуксировать» его к одноименному полю таблицы Хозяйства. На экране появитсяокно Изменение связей, где будет указано, по каким полям устанавливается связь. Если строке Тип отношения тип не соответствует, его можно уточнить через кнопку Объединение. Для установки связи нажать кнопку Создать.

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

Удаление связи. Выделите линию связи (линия становится более толстой), а затем нажмите клавишу Delete.

Изменение существующих связей. Закройте все открытые таблицы. Изменять связи между открытыми таблицами нельзя. Указатель мыши установите на линию связи (или дважды щелкните по ней) и правой кнопкой мыши вызовите контекстное меню. В окне Изменение связей выполнитередактирование, а через кнопку Объединение выберите требуемый тип объединения.

· Установите связи между остальными таблицами.

· Закройт е окно Схема данных.

Запросы

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

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

Бланк запроса состоит из двух областей. В верхней области отображается структура таблиц, к которым запрос адресован. А нижняя область разбита на столбцы – по одному столбцу на каждое поле будущей результирующей таблицы.

Идея формирования запроса проста. С помощью контекстного меню в верхней половине бланка открывают те таблицы (Запрос \ Добавить таблицу или кнопка на панели инструментов Добавить таблицу), к которым обращен запрос. Затем выполняют двойные щелчки на названия тех полей, которые должны войти в результирующую таблицу. При этом заполняются столбцы нижней части бланка. Сформировав структуру запроса, его закрывают, присваивают имя и в дальнейшем открывают для просмотра.

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

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

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

· Выделяют столбец щелчком на его заголовке (кнопку мыши отпускают);

· Еще раз щелкают на заголовке уже выделенного столбца (но кнопку не отпускают);

· Перетаскивают столбец в другое место.

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

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

Задание 5

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

Результат:

Использование в запросах вычисляемых полей

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

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

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

Вычисляемое поле будет иметь выражение

потребность в семенах, ц: [хозяйства]![площадь, га]*[нормы высева]![норма высева, ц\га]

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

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

Чтобы добавить знак умножения, можно раскрыть папку «Операторы», затем выбрать из следующего списка группу арифметических операторов и, наконец, из последнего списка – знак умножения. Но это слишком сложно и долго. Гораздо проще нажать кнопку с изображением знака умножения, которая расположена под областью ввода выражения.

После завершения формирования выражения нажмите кнопку ОК, выражение будет перенесено в строку Поле бланка запроса. Access автоматически задаст имя вычисляемого поля (например, Выражение1), которое отделяется от выражения двоеточием. Отредактируйте предложенное имя на более подходящее.

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

Задание 6

Рассчитать потребность в семенах. В окне Конструктора задать структуру запроса по образцу:

Результат запроса:

Итоговые запросы

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

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

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

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

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

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

Задание 7

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

Результирующая таблица:

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

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

Задание 8

Составить перекрестный запрос для подсчета площадей по культурам и хозяйствам. В названии строк ввести наименование хозяйства, а в названии столбцов – наименование культуры. На пересечении строк и столбцов вывести данные о посевных площадях. В таблице следует показать итоговые данные. Порядок выполнения задания с использованием Мастера:

· В окне БД на вкладке Запросы выбрать режим «Создание запроса с помощью мастера» и щелкнуть на кнопку Создать, выбрать Перекрестный запрос, ОК.

· В следующем окне выбрать таблицу «Хозяйства», щелкнуть Далее

· В следующем окне выбрать поле для наименования строк

· Затем выберите поле для использования его значений в заголовках граф

· В следующем окне выбирается поле, по которому следует накапливать числа

Результат запроса

· Для форматирования чисел с точностью до 2-х десятичных знаков запрос следует открыть в режиме Конструктора,

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

· Те же действия повторить для поля Площадь.

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

Формы

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

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

Первая запись Предыдущая запись Следующая запись Последняя запись Новая запись

Задание 9

Создать форму для ввода и просмотра записей в таблице «Хозяйства».

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

· В окне БД выбрать вкладку Формы, нажать кнопку Создать.

· В диалоговом окне выбрать вариант построения Конструктор.

· Указать в качестве источника данных имя таблицы Хозяйства, ОК. На экране появится окно формы.

· Форму дополним названием «Хозяйства», используя кнопку Надпись на панели элементов . Если панель на экране отсутствует, настройте её через команду главного меню Вид.

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

· Закрыть окно конструктора форм и сохранить форму именем Хозяйства.

Задание 10

Составить прейскурант цен на удобрения по таблице Цены на удобрения, добавив новое поле - Цена с НДС. НДС (налог на добавленную стоимость) = 18%. Для создания вычисляемого поля Цена с НДС следует нажать кнопку Поле на панели элементов. Рядом с новым полем автоматически появится надпись «Поле …». Щелчком левой кнопки мыши измените текст на «Цена с НДС». Для ввода формулы указатель поставить внутрь поля, нажать кнопку мыши и ввести формулу: =[цена, руб/ц]*1.18

Закрыть форму и сохранить с именем Прейскурант на удобрения.

Отчеты

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

Для создания отчета Microsoft Access позволяет использовать различные элементы макета: текст, рисунки, рамки, диаграммы.

Задание 11

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

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

Окне БДвыберите Отчет и нажмите кнопку Создать.

· Выберите таблицу Хозяйства в качестве источника данных.

Укажите способ создания Мастер отчета, нажмите кнопку ОК.

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

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

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

· Для подведения итогов необходимо использовать кнопку Итоги. В строке Площадь, га укажите функцию SUM, нажмите ОК и кнопкой Далее перейти к следующему шагу построения отчета.

· Выберите макет отчета и ориентацию бумаги.

· По своему желанию подберите стиль отчета.

· Введите имя отчета, предложенное в задании, нажмите кнопку Готово. На экране в режиме просмотра появится отчет, который можно вывести на печать через команду Файл / Печать.

В отчете следует выполнить форматирование итоговых числе с точностью, например, до 2-х десятичных знаков.

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

· Наблюдайте изменение десятичной значности чисел.

Работа в окне базы данных

В окне БД перечислены составляющие её объекты: таблицы, запросы, формы, отчеты, макросы и модули. Окно БД предназначено для создания новых и изменения существующих объектов.

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

Чтобы скопировать объект:

· В окне базы данных выделите объект, который следует скопировать;

· Выполните команды меню Правка / Копировать (или нажать CTRL+C);

· Если объект следует скопировать в другую базу данных, откройте БД – адресат;

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

· Введите имя нового объекта. Затем нажмите кнопку «ОК».

Чтобы переименовать объект:

· В окне базы данных выделите объект, который следует переименовать;

· Выполните команды меню Файл / Переименовать;

· Введите новое имя объекта, затем нажмите Enter.

Чтобы удалить объект:

· В окне базы данных выделите объект, который следует удалить;

· Выполните команды меню Правка / Удалить или воспользуйтесь клавишей Delete.